Both [`ScriptProperties`][1] and [`ScriptDB`][2] are deprecated.

Instead, you should be using the new class [**`PropertiesService`**][3] which is split into three sections of narrowing scope:

* **Document** - Gets a property store that all users can access within the current document, if the script is published as an add-on.
* **Script** - Gets a property store that all users can access, but only within this script.
* **User** - Gets a property store that only the current user can access, and only within this script.

Here's an example persisting a user property across calls:



 var properties = PropertiesService.getScriptProperties();

 function saveValue(lastDate) {
 properties.setProperty('lastCalled', lastDate);
 }

 function getValue() {
 return properties.getProperty('lastCalled');
 }

The script execution environment is stateless, so you cannot access local variables from previous runs, but you can store `getScriptProperties()` in a local variable because it will be re-run for each return trip to the server so it can be called in either method.

----

If you need to store something on a more temporary basis, you can use the [`CacheService`][4] API

[1]: https://developers.google.com/apps-script/reference/properties/script-properties
[2]: https://developers.google.com/apps-script/reference/script-db/
[3]: https://developers.google.com/apps-script/reference/properties/properties-service
[4]: https://developers.google.com/apps-script/reference/cache/

In a Google spreadsheet using the Script Editor, I do function calls, but I am not quite sure if the best way to store persistant data (data that I will continue to use) is to use global variables (using objects, arrays, strings), or there is a better way to store data.

I don't want to use cells which could be another way.

Another question, is it possible to create (pseudo) classes in this environment? Best way?

Google apps script: how to persist data in spreadsheet between different function calls?

According to the [Optimization section][1] on the Apps Script Function Guide:

> Each time a custom function is used in a spreadsheet, Google Sheets makes a separate call to the Apps Script server. If your spreadsheet contains dozens (or hundreds, or thousands!) of custom function calls, this process can be quite slow. 
>
> Consequently, if you plan to use a custom function multiple times on a large range of data, consider modifying the function so that it accepts a range as input in the form of a two-dimensional array, then returns a two-dimensional array that can overflow into the appropriate cells.


To do this, pass in an input that represents the size of the array you'd like to return. When you start executing your function check if the input parameter is an array with `input.map`. If it is, you can call the the function on each item and return that entire collection.

So in your case like this:



 function foo(){
 return "bar";
 };

You can update the function like this:



 function foo(input){
 if (input.map) { // Test whether input is an array.
 return input.map(foo); // Recurse over array if so.
 } else {
 // do actual function work here
 return "bar";
 }
 };

And then call it like this:

![screenshot](http://i.imgur.com/IseIy5x.png)


[1]: https://developers.google.com/apps-script/guides/sheets/functions#optimization

I want to use Google Apps Script to make custom functions for a spreadsheet. I've made an extremely simple function:

    function foo(){
        return "bar";
    };

The problem is that I need this function in a couple hundred cells. When I paste the function `=foo()` into all of these cells, the function works in a few of the cells, but in most I get this error: **"Service invoked too many times: spreadsheet. Try `Utilities.sleep(1000)` between calls."**

[[Screenshot here]][1]

I guess I don't understand why this function, simple as it is, is considered an invocation of the Spreadsheet Services. I'm not even requesting any data (except for the function itself). Is that the problem? And if so, is there a workaround? Custom functions could make Google Spreadsheets infinitely more powerful, but this problem hamstrings the possibility of using a custom function in multiple cells. Suggestions?

(P.S. -- Using the `Utilities.sleep()` function as suggested by the error message doesn't help at all when all of the cells call their functions simultaneously; it only slows the rate at which individual cells repeatedly call the function.)


  [1]: http://i.imgur.com/k4KGW.png

Service Invoked Too Many Times (Google Apps Script)

# Here's what I believe is the best solution so far:

 =CountIf(ArrayFormula(range<>""),TRUE)

## Here's why in 3 easy steps

### Step 1: Simple As Pie - Add Extra Column

The [answer by eniacAvenger][1] will yield the correct solution without worrying about edge cases as `=A1<>""` seems to arrive at the correct truthy/falsy value based on how we intuitively think of blank cells, either virgin blanks or created blanks. 

So imagine we have this data and we want the Count of non-blanks in `B2:B6`:

<pre><code>| | A | B | C |
|---|-------------|-------|---------|
| 1 | Description | Value | B1&lt;&gt;"" |
| 2 | Text | H | TRUE |
| 3 | Number | 1 | TRUE |
| 4 | IF -> "" | | FALSE |
| 5 | IF -> Text | h | TRUE |
| 6 | Blank | | FALSE |
</code></pre>

If we relied on *Column C*, we could get the count of values in B like this:

 =COUNTIF(C2:C6,True)

### Step 2: Use `FormulaArray` to dynamically create Extra Column

However, [consideRatio's comment][2] is a valid one - if you need an extra column, you can often accomplish the same goal with an `ArrayFormula` which can create a column in memory without eating up sheet space.

So if we want to create C dynamically, we can use an array formula like this:

 =ArrayFormula(B2:B6<>"")

If we simply put it in C2, it would create the vertical array with a single stroke of the pen:

<pre><code>| | A | B | C |
|---|-------------|-------|--------------------------|
| 1 | Description | Value | =ArrayFormula(B2:B6&lt;&gt;"") |
| 2 | Text | H | TRUE |
| 3 | Number | 1 | TRUE |
| 4 | IF -> "" | | FALSE |
| 5 | IF -> Text | h | TRUE |
| 6 | Blank | | FALSE |
</code></pre>

### Step 3: Count Values in Dynamic Column

But with that solved, we no longer need the column to merely display the values.

`ArrayFormula` will resolve to the following range: `{True,True,False,True,False}`. 
`CountIf` just takes in any range and in this case can count the number of True values.

So we can wrap `CountIf` around the values produced by `ArrayFormula` like this:

 =CountIf(ArrayFormula(B2:B6<>""),TRUE)


### Further Reading

The other solutions in this thread are either overly complex, or fail in particular edge cases that I've enumerated in this test sheet:

### [Google Spreadsheet - CountA Test - Demo][3]

For why `CountA` works the wonky way it does, see [my answer here][4]

[1]: https://stackoverflow.com/a/25983799/1366033
[2]: https://stackoverflow.com/questions/24081707/google-spreadsheet-count-rows-with-not-empty-value#comment44144031_25983799
[3]: https://docs.google.com/spreadsheets/d/1wGzbVbM4y6iPu0xbgLHKC-Xkaj94ArGHqI6SOlv6yrQ/edit?usp=sharing
[4]: https://stackoverflow.com/a/26790893/1366033

In a Google Spreadsheet: How can I count the rows of a given area that have a value? All hints about this I found up to now lead to formulas that do count the rows which have a not empty content (including formula), but a cell with  

    =IF(1=2;"";"")  // Shows an empty cell

is counted as well.

What is the solution to this simple task?


Count rows with not empty value

## See Also: [Count rows with not empty value][0]

---

Unfortunately, this is *functions as designed* from Google. 
Although I'm not sure why it's divergent from the way that [Excel calculates COUNTA][MS-COUNTA].

According to the [Google Spreadsheet Documentation on `COUNTA`][Google - COUNTA]:

> COUNTA counts all values in a dataset, including those which appear more than once and text values (**including zero-length strings** and whitespace).

Meaning that the only way to "make [the cells] completely empty" is to delete the entire contents, formula and all. But fear not...

### Some workarounds:

1. *Hypothetically*, you should be able to do this with [`=COUNTIF(A3:A8,"<>"&"")`][Not Equal Blank], but Google spreadsheets doesn't support the ***not equal to*** operator in the [COUNTIF function][Google - COUNTIF] according to these forums: [1], [2], [3]

2. A workaround is to create a truthy or falsy array based on the condition and use SUMPRODUCT to count the truthy values like this:

 =SUMPRODUCT((A3:A8<>"")*1)

3. Another option you could pursue would be to [write a custom function][custom] and add it to Drive. 
 It's actually pretty easy to do so. Go to Tools > Script Editor and add the following script:

 

 /**
 * Works like COUNTA except does not include null strings
 *
 * @param {cellRange} myArray The value or range to consider when counting.
 * @return Returns the a count of the number of values in a dataset.
 * @customfunction
 */
 function CountNotBlank(myArray) {
 var output = 0;
 for (i = 0; i < myArray.length; i++) { 
 if (myArray[i] != "") {
 output += 1
 }
 }
 return output;
 }
 
 Then use like this:

 =CountNotBlank(I31:I)


[MS-COUNTA]: http://office.microsoft.com/en-us/excel-help/use-counta-to-count-nonblank-cells-HP010342340.aspx
[Google - COUNTA]: https://support.google.com/docs/answer/3093991?hl=en
[Google - COUNTIF]: https://support.google.com/docs/answer/3093480?hl=en
[Not Equal Blank]: https://www.ablebits.com/office-addins-blog/2014/07/02/excel-countif-examples/

[0]: https://stackoverflow.com/a/43358591/1366033
[1]: https://productforums.google.com/forum/#!topic/docs/zAT50FK9DDE
[2]: https://productforums.google.com/forum/#!topic/docs/FIOttjtaPYY
[3]: https://productforums.google.com/forum/#!topic/docs/u_XdYZGCcrE

[custom]: https://developers.google.com/apps-script/guides/sheets/functions

I'm using the command `=COUNTA(I31:I)` to count all non-empty cells in the column. The cells in the column contain this line of code:

    =IF(ISNUMBER(D31), "X", "")

The result is that the cells are either an empty string (the line with add nothing `""`) or contain an `X`.

The problem now is that `COUNTA` counts all the cells after this procedure, empty or not. If I delete the long command line the cell is not counted and it works fine. So this long command line is adding something to the cell, so it appears *not-empty* even though it looks empty.

Any clues to what that is? My guess is that I should use some kind of `Null` operator instead of the `""` to add nothing to the cell.

**Note**: For some reason, this seems to work in a previous version of Google Spreadsheet I had.

As a *potential workaround*, I could replace `=COUNTA(I31:I)` by checking specifically for `x` with `=COUNTIF(I31:I;"X")`. But I'm still curious as to the problem with COUNTA. 

If it turns out that my cells are not empty 'enough' for this command, how can I then make them completely empty?

COUNTA not working as I expect in new Google Sheets

In addition to options that would be available in Excel (`LEFT` + `FIND`) pointed out by pnuts, you can use a variety of regex tools available in Google Sheets for text searching / manipulation

For example, `RegexReplace`:



 =REGEXREPLACE(A1,"(.*)USD.*","$1")

* `(.*)` <- capture group `()` with zero or more `*` of any character `.`
* `USD.*` <- exact match on `USD` followed by zero or more `*` of any character `.`
* `$1` <- replace with match in first capture group


[![enter image description here][1]][1]


 [1]: https://i.stack.imgur.com/zlCRv.png

I did some searching and in openoffice and excel it looks like you can simply add an * at the beginning or end of a character to delete everything before and after it, but in Google spreadsheet this isn't working. Does it support this feature? So if I have:

<pre>keyword USD 0078945jg .12 N N 5748 8</pre>

And I want to remove USD and everything after it what do I use? I have tried:

<pre>USD* and (USD*) with regular expressions checked</pre>

But it doesn't work. Any ideas?

replace everything after a character in Google spreadsheet

Your update has you about 90% of the way there. The trick is to explicitly check the size of the destination sheet before you copy data into it. For example, if I did something like this:



 var cromulentDocument = SpreadsheetApp.getActiveSpreadsheet();
 var masterSheet = cromulentDocument.getSheetByName('master');
 var logSheet = cromulentDocument.getSheetByName('log');
 var hugeData = masterSheet.getDataRange().getValues();
 var rowsInHugeData = hugeData.length;
 var colsInHugeData = hugeData[0].length;

 /* cross fingers */
 logSheet.getRange(1, 1, rowsInHugeData, colsInHugeData).setValues(hugeData);

...then my success would totally depend upon whether `logSheet` was at least as big as `masterSheet`. That's obvious, but what's less so is that if `logSheet` is **bigger** then there will be some old junk left over around the edges. Ungood.

Let's try something else. As before, we'll grab the master data, but we'll also resize `logSheet`. If we don't care about `logSheet` being too big we could probably just `clear()` the data in it, but let's be tidy.



 var cromulentDocument = SpreadsheetApp.getActiveSpreadsheet();
 var masterSheet = cromulentDocument.getSheetByName('master');
 var logSheet = cromulentDocument.getSheetByName('log');
 var hugeData = masterSheet.getDataRange().getValues();
 var rowsInHugeData = hugeData.length;
 var colsInHugeData = hugeData[0].length;

 /* no finger crossing necessary */
 var rowsInLogSheet = logSheet.getMaxRows();
 var colsInLogSheet = logSheet.getMaxColumns();

 /* adjust logSheet length, but only if we need to... */
 if (rowsInLogSheet < rowsInHugeData) {
 logSheet.insertRowsAfter(rowsInLogSheet, rowsInHugeData - rowsInLogSheet);
 } else if (rowsInLogSheet > rowsInHugeData) {
 logSheet.deleteRows(rowsInHugeData, rowsInLogSheet - rowsInHugeData);
 }

 /* likewise, adjust width */
 if (colsInLogSheet < colsInHugeData) {
 logSheet.insertColumnsAfter(colsInLogSheet, colsInHugeData - colsInLogSheet);
 } else if (colsInLogSheet > colsInHugeData) {
 logSheet.deleteColumns(colsInHugeData, colsInLogSheet - colsInHugeData);
 }

 /* barring typos, insert data with confidence */
 logSheet.getRange(1, 1, rowsInHugeData, colsInHugeData).setValues(hugeData);

What's going on here is pretty straightforward. We figure out how big the log needs to be, and then adjust the destination sheet's size to match that data.

What I'd like to do is warehouse information from a particular sheet within a spreadsheet and copy it to a second spreadsheet at the end of every day. The second spreadsheet will run complex pivots and reports against the copied information that don't need to run throughout the day.

I can set up a [time-driven trigger][4] which will run the job every day within an hour block.

I'm working on the following script which uses [`SpreadsheetApp.getActiveSpreadsheet`][5] to get the current Spreadsheet. Then gets the individual sheet to backup with [`spreadsheet.getSheetByName`][7]. And then uses the [`sheet.copyTo`][8] method to add the current sheet to a new spreadsheet. I'm getting the new spreadsheet by looking up the id with [`SpreadsheetApp.openById`][9] all like this:



 function startBackupJob() {
 
 var currentSpreadSheet = SpreadsheetApp.getActiveSpreadsheet()
 var masterSheet = currentSpreadSheet.getSheetByName("Sheet1")
 
 var backupSpreadSheetId = "#######################################";
 var backupSpreadSheet = SpreadsheetApp.openById(backupSpreadSheetId);
 
 // var backupSheet = backupSpreadSheet.getSheetByName("Sheet1");
 // backupSpreadSheet.deleteSheet(backupSheet);

 masterSheet.copyTo(backupSpreadSheet).setName("Sheet1");
 
 }


The issue I'm having is that `copyTo` will create a new worksheet rather than overwrite the existing spreadsheet. The point of moving to the new workbook is to run pivot tables off the data and not re-wire them to point to a new sheet.

I can delete the previous sheet to make room for the new one, but this kills the references on the PivotTable as well, so it doesn't help much.

**Is there an easy way to transfer the entire contents of one worksheet to another?**

---

This is similar to (but different from) the following questions:

* [How do I script making a backup copy of a spreadsheet to an archive folder?][0] - However, I don't want to move the whole file, but a specific sheet within the spreadsheet.
* [How can copy specifics sheet to another spreadsheet using google script][1] & [copy one spreadsheet to another spreadsheet with formatting][2] - However copying produces a new sheet, whereas I need to replace the contents of an existing sheet
* [Scripts, copy cell from one sheet to another sheet EVERYDAY at a specific time][3] - However, I do want to replace the entire sheet, rather than just specific cells within the sheet.

[0]: https://stackoverflow.com/q/11839952/1366033
[1]: https://stackoverflow.com/q/17099497/1366033
[2]: https://stackoverflow.com/q/19791132/1366033
[3]: https://stackoverflow.com/q/24500761/1366033

[4]: https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers
[5]: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#getActiveSpreadsheet()
[7]: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getSheetByName(String)
[8]: https://developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet)
[9]: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openById(String)

---

**Update**

I might be able to do this by calling [`getRange`][10] on each sheet and then using [`getValues`][11] and [`setValues`][12] like this:



 var currentValues = masterSheet.getRange(1, 1, 50, 50).getValues()
 backupSheet.getRange(1, 1, 50, 50).setValues(currentValues)

But I'm worried about edge cases where the master sheet has a different available range than the backup sheet. I also don't want to hardcode in the range, but for it to encompass the entire sheet. If I call `.getRange("A:E")` then the two worksheets have to have the exact same number of rows which is not likely.


[10]: https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer,Integer)
[11]: https://developers.google.com/apps-script/reference/spreadsheet/range#getValues()
[12]: https://developers.google.com/apps-script/reference/spreadsheet/range#setValues(Object)

Replace entire sheet with another in Google Apps Scripts

You could use the class [`FormTriggerBuilder`][3] to dynamically create and destroy triggers. You can use the [`PropertiesService`][4] to persist the id for the created trigger and then [programmatically delete it later][5] like this:



 var formID = 'XXXXXXX';
 var properties = PropertiesService.getScriptProperties()
 
 function onInstall() {
 createSubmitHandler()
 }
 
 function createSubmitHandler() {
 var form = FormApp.openById(formID);
 var trigger = ScriptApp.newTrigger('onSubmit')
 .forForm(form)
 .onFormSubmit()
 .create();
 
 properties.setProperty('triggerID', trigger.getUniqueId())
 }
 
 function destroySubmitHandler() {
 var triggerID = properties.getProperty('triggerID')
 var form = FormApp.openById(formID);
 var allTriggers = ScriptApp.getUserTriggers(form)
 for (var i = 0; i < allTriggers.length; i++) {
 if (allTriggers[i].getUniqueId() == triggerID) {
 ScriptApp.deleteTrigger(allTriggers[i]);
 break;
 }
 }
 }
 
 function onSubmit() {
 // we've been called, remove trigger, set timeout, re-enable, and then run function
 destroySubmitHandler();
 Utilities.sleep(5 * 1000)
 createSubmitHandler();
 myFunction()
 }
 
 function myFunction() {
 // do stuff here
 }

---

Alternatively, as ScampMichael pointed out, you could use the [Lock Service][1]. There's a great write up of it in this article on [Concurrency and Google Apps Script][2]. However, I'm not sure that's what I want here. The lock service will queue code waiting to be executed. Which solves the problem of concurrent executions, but not the problem of hitting daily quotas of only 20 triggers per script, depending on how the quotas are measures.

Here's an example of how a lock script could be used to prevent concurrent invocation:



 function onSubmit() {
 // we've been called, get a public lock, wait for other job to finish, run the function, and release the cracken
 var lock = LockService.getScriptLock();
 lock.tryLock(30 * 1000);
 myFunction();
 lock.releaseLock();
 }
 
 function myFunction() {
 // do stuff here
 }

[1]: https://developers.google.com/apps-script/reference/lock/
[2]: http://googleappsdeveloper.blogspot.com/2011/10/concurrency-and-google-apps-script.html
[3]: https://developers.google.com/apps-script/reference/script/form-trigger-builder
[4]: https://developers.google.com/apps-script/reference/properties/properties-service
[5]: https://stackoverflow.com/q/15368152/1366033

Currently we have a Google Form that is automatically uploaded and batched in installments of ~50 forms.  These come into a spreadsheet and need to be acted upon by a trigger that subscribes to the [onFormSubmit event][1].

The problem is that since these are all uploaded simultaneously, the event fires rapidly back to back and come up with the either of the following warnings:

> Too many simultaneous invocations: Spreadsheets (line 16, file "Code")  
> We're sorry, a server error occurred. Please wait a bit and try again. (line 12, file "Code")

According to the [Daily Quotas Limit][2], there's a max of 20 scripted triggers per day.

That's a perfectly reasonable number, so long as I can find a way to run a single trigger for all 50 back to back form submissions at the very end.

It would be great if I could set a timeout for the execution to wait and see if any other events were fired in the next couple seconds, but I would need to be inside of a trigger handler by the time I'm executing the trigger.

**Q**: Is there anyway to batch Form Submission Triggers?



[1]: https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
[2]: https://developers.google.com/apps-script/guides/services/quotas



Prevent "Too many simultaneous invocations" on formSubmit

There must be some criteria for archiving the row. Can you use the same criteria to sort, then delete all at once (also copy for that matter), and then un-sort?

I'm working on a script to archive (copy and delete) rows from a spreadsheet.

To do this I go through every row in the worksheet to see if it's completed.

I can batch the first copy process by gathering up all the rows and writing the range of values all at the end.

However, when it comes time to delete the rows, I still have to loop through and call [`sheet.deleteRow()`][1] once for each row. I can't use [`sheet.deleteRows()`][2] because the rows are not consecutive.

Here's an example of the way I'm trying to use the script:



 function archiveRows_(archiveSheet, sheetID) {
 
 // get req values
 var reqSpreadSheet = SpreadsheetApp.openById(sheetID);
 var reqSheet = reqSpreadSheet.getSheetByName("Mstr_Wrkbk");
 var currentValues = reqSheet.getDataRange().getValues();
 
 // container for staged data
 var rowsToArchive = [];
 var rowsToDelete = [];
 
 // check for archivable content
 var currentRows = currentValues.length;
 for (i = 0; i < currentRows - 1; i++) {
 var row = currentValues[i]
 
 // check if we meet the criteria for archival
 if (shouldArchive_(row)) {
 // add row to archive list
 rowsToArchive.push(row);
 rowsToDelete.push(i + 1);
 }
 }
 
 // archive rows if we found some
 var numRows = rowsToArchive.length; if (!numRows) return 0;
 var numCols = rowsToArchive[0].length;

 // add rows to archive sheet
 var nextFreeRow = archiveSheet.getLastRow();
 archiveSheet.insertRows(nextFreeRow + 1, numRows);
 
 // write archive rows
 archiveSheet.getRange(nextFreeRow + 1,1, numRows, numCols).setValues(rowsToArchive);
 
 // Committ Changes
 SpreadsheetApp.flush();
 
 // delete original rows (we have to loop backwards so delete works)
 for (i = rowsToDelete.length - 1; i >= 0; i--) {
 // delete row from master sheet
 var rowNum = rowsToDelete[i];
 reqSheet.deleteRow(rowNum);
 }

 // Committ Changes
 SpreadsheetApp.flush();
 
 return numRows; 
 }


It's this part that runs really slowly:



 // delete original rows (we have to loop backwards so delete works)
 for (i = rowsToDelete.length - 1; i >= 0; i--) {
 // delete row from master sheet
 var rowNum = rowsToDelete[i];
 reqSheet.deleteRow(rowNum);
 }

Is there any way to optimize / batch this operation?

[1]: https://developers.google.com/apps-script/reference/spreadsheet/sheet#deleteRow(Integer)
[2]: https://developers.google.com/apps-script/reference/spreadsheet/sheet#deleteRows(Integer,Integer)