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)

### Quotas

>The maximum execution time for a single script is 6 mins / execution  
- https://developers.google.com/apps-script/guides/services/quotas

But there are other limitations to familiarize yourself with.  For example, you're only allowed a total trigger runtime of 1 hour / day, so you can't just break up a long function into 12 different 5 minute blocks.

### Optimization

That said, there are very few reasons why you'd really need to take six minutes to execute.  JavaScript should have no problem sorting thousands of rows of data in a couple seconds.  What's likely hurting your performance are service calls to Google Apps itself.

> You can write scripts to take maximum advantage of the built-in caching, by minimizing the number of reads and writes. Alternating read and write commands is slow. To speed up a script, read all data into an array with one command, perform any operations on the data in the array, and write the data out with one command.  
- https://developers.google.com/apps-script/best_practices

### Batching

The best thing you can possibly do is reduce the number of service calls.  Google enables this by allowing batch versions of most of their API calls.  



As a trivial example, **Instead of this**:



    for (var i = 1; i <= 100; i++) {
      SpreadsheetApp.getActiveSheet().deleteRow(i);
    }

**Do this**:



    SpreadsheetApp.getActiveSheet().deleteRows(i, 100);

In the first loop, not only did you need 100 calls to deleteRow on the sheet, but you also needed to get the active sheet 100 times as well.  The second variation should perform several orders of magnitude better than the first.



### Interweaving Reads and Writes

Additionally, you should also be very careful to not go back and forth frequently between reading and writing.  Not only will you lose potential gains in batch operations, but Google won't be able to use its built-in caching.

> Every time you do a read, we must first empty (commit) the write cache to ensure that you're reading the latest data (you can force a write of the cache by calling `SpreadsheetApp.flush()`). Likewise, every time you do a write, we have to throw away the read cache because it's no longer valid. Therefore if you can avoid interleaving reads and writes, you'll get full benefit of the cache.  
- http://googleappsscript.blogspot.com/2010/06/optimizing-spreadsheet-operations.html

**For example, instead of this**:



    sheet.getRange("A1").setValue(1);
    sheet.getRange("B1").setValue(2);
    sheet.getRange("C1").setValue(3);
    sheet.getRange("D1").setValue(4);

**Do this**:



    sheet.getRange("A1:D1").setValues([[1,2,3,4]]);

### Chaining Function Calls

As a last resort, if your function really can't finish in under six minutes, you can chain together calls or break up your function to work on a smaller segment of data.  

You can store data in the [Cache Service][1] (temporary) or [Properties Service][2] (permanent) buckets for retrieval across executions (since Google Apps Scripts has a stateless execution).

If you want to kick off another event, you can create your own trigger with the [Trigger Builder Class][3] or setup a recurring trigger on a tight time table.


[1]: https://developers.google.com/apps-script/reference/cache/
[2]: https://developers.google.com/apps-script/reference/properties/
[3]: https://developers.google.com/apps-script/reference/script/trigger-builder

My google app script is iterating through the user's google drive files and copying and sometimes moving files to other folders. The script is always stopped after 5 minutes with no error message in the log. 

I am sorting tens or sometimes thousands files in one run.

Are there any settings or workarounds?

Google app script timeout ~ 5 minutes?

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

This is a general problem when hardcoding strings or numbers in code.

In general the javascript parser can't tell which strings might be used on a sheet function call. Its sometimes not trivial to solve. 

Two approaches are: 

1. If  the columns/cells/ranges are known beforehand, use named ranges:
 [Define a named range](https://support.google.com/docs/answer/63175) and use [NamedRange in code](https://developers.google.com/apps-script/reference/spreadsheet/named-range). Use the range to directly write to it or query its row/column position.
2. Another for column based ranges like yours is that your code does this naming manually by using the column header as the column names. Code uses those names and reads the header to build the mapping.

We're migrating a lot of our business logic to scripts behind the scenes, but I'm worried that they'll be much more fragile when columns move.

### On Sheet Updates Automagically

For example, If I have a formula on a spreadsheet like this:

    =If(A1=5,"Yes","No")

And then I Insert 1 Column Left of A, the formula will be automatically updated like this:

    =If(B1=5,"Yes","No")

### Apps scripts doesn't update

For example, if I have the formula in the script section:



    function myFunction() {
       var value = SpreadsheetApp.getActiveSheet().getRange("A1").getValue();
       var output = (value == 5) ? 'Yes' : 'No';
       Logger.log(output);
    }

It will not update when the sheet changes.

**Q**: How can I get stable references in the code behind for columns that could potentially move?

Update script cell references when columns are moved

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

Apparently you can use the function [`Utilities.sleep()`][1] like this:



    function onSubmit() {
      // we've been called, remove trigger, set timeout, re-enable, and then run function
      destroySubmitHandler();
      Utilities.sleep(5 * 1000)
      createSubmitHandler();
      myFunction()
    }

[1]: https://developers.google.com/apps-script/reference/utilities/utilities#sleep(Integer)

Is it possible to call [setTimeout][1] or an equivalent function in Google Apps Scripts?

When I try to run the following code:



    function onSubmit() {
      // we've been called, remove trigger, set timeout, re-enable, and then run function
      destroySubmitHandler();
      setTimeout(function() {
        createSubmitHandler();
        myFunction()
      }, 5 * 1000)
    }

I get the following error:

![screenshot][screenshot]

[1]: https://developer.mozilla.org/en-US/docs/Web/API/WindowTimers/setTimeout
[screenshot]: http://i.imgur.com/SVy1CCx.png

Set Timeout in Google Apps Scripts

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)