Reading and writing data from spreadsheets – best practices and performances

You probably noticed when playing with the preceding script example that it takes some time to execute. You must remember how these spreadsheets and scripts actually work.

The sheet we see in our browser does not exist as a file in our computer; all its data is stored on a Google server somewhere and it is rendered as HTML content that can be understood by any web browser.

Each time we make a change manually or using a script, the information is sent to the server, interpreted, processed, and sent back to us using our Internet connection.

This can be a time-consuming journey for all those bytes; some parameters are predictable (our average connection speed, for example) and some are not, such as the distance from our computer to the Google server and its load at that moment. Remember that you are not alone in using this drive app! There can be thousands of users sending requests simultaneously.

To make things better, or at least the best we can achieve with the parameters we have control over, we should take care to use as few separate requests as possible to any distant service.

For example, if we want to fill 100 cells in a sheet column, it would be a bad idea to fill each cell one by one. We should try to use the batch method to set the values in all 100 cells in one single step.

Example

The following are two scripts that do what we suggested in the preceding section: filling a column of 100 cells with some text. Try both versions successively and compare the execution times of both versions, which will be shown in a browser pop up. The following code snippet fills a column of 100 cells with some text:

function fill100Cells(){
  var start = new Date().getTime();
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  for(var n=1 ; n<=100 ;n++){
    sh.getRange(n,1).setValue('This cell is filled');
  }
  Browser.msgBox('Execution time : '+(new Date().getTime()-start)+' milliseconds');
}

function fill100CellsatOnce(){
  var start = new Date().getTime();
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sh.getRange(1,1,100,1).setValue('This cell is filled');
  Browser.msgBox('Execution time : '+(new Date().getTime()-start)+' milliseconds');
}

The following screenshot displays the execution time for filling a column of 100 cells with some text:

I'll let you guess which one is the most efficient.

This simple example illustrates the very few annoying aspects of cloud computing and Google Drive applications, in particular, the execution speed of code.

Knowing that, we should always be very careful when writing scripts, do it as efficiently as possible, and using as few service calls as possible. These recommendations and a few others that we shall examine later on are clearly explained in Google Drive's documentation. I suggest you read it twice rather than once and keep it in mind when writing your future applications (https://developers.google.com/apps-script/best_practices).

There have been a couple of very interesting posts on that subject on the Stack Overflow Help forum and I suggest that you read this as well, as it goes quite deeper in the speed testing and optimization process (among others: http://stackoverflow.com/questions/15145918/what-is-faster-scriptdb-or-spreadsheetapp/15149959#15149959).