Google Apps Script Spreadsheet Range Processing (G

Question

Once again I'm back to SO for GAS problems because I'm not too familiar with Javascript/GAS yet. I'm having a bit of trouble with how slowly a script is running based on the method of handling function calls in an efficient manner.

I've read in several places (ah, it was here), that doing a "read-all" then "write-all" for getting-parsing-setting values (in Spreadsheets at least) is faster than doing a "get-one, write-one" method (for obvious reasons, this makes sense).

I know how to get all the values in a Range, but I'm not really sure how to go through the (multidimensional) array, process the data, and set a new Range accordingly.

The problem: The function takes about 2 seconds to start up, and takes about 5 seconds to run for 50 rows. The problem is, I will likely have thousands of rows in this Spreadsheet, and to have this function run to ensure that data is post-processed correctly so that Boomerang Calendar picks up the time data correctly is a tad ridiculous in my opinion. The script takes twice as long for every column of data needed to process, which is horrendous. I fear that during the next school semester I will often be going over my "GAS processing time limit."

Here is the starting code (bad, I admit):

function fixApostrophes() {
  // Get the active spreadsheet to run the script on
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Get the active sheet within the document to run the script on
  var sheet = spreadsheet.getActiveSheet();

  // Get max number of rows needed to process
  var maxRows = sheet.getLastRow();

  // Get the range for the startTime column and endTime column
  var apostropheRange = sheet.getRange(1, 10, maxRows, 2);

  // Get the value in each cell, remove apostrophes from the start,
  // and replace the value in that cell
  for (var i = 1; i < apostropheRange.getNumRows(); ++i) {

    // Get the cells for startTime and endTime to speed things up a bit
    var startCell = apostropheRange.getCell(i, 1);
    var endCell = apostropheRange.getCell(i, 2);

    // Get the values for startTime and endTime
    var startTime = startCell.getValue();
    var endTime = endCell.getValue();

    // Remove apostrophes from start of startTime
    while(startTime.charAt(0) == "'") {
      startTime = startTime.substring(1);
    }

    // Remove apostrophes from start of startTime
    while(endTime.charAt(0) == "'") {
      endTime = endTime.substring(1);
    }

    // Set the values for startTime and endTime
    startCell.setValue(startTime);
    endCell.setValue(endTime);
  }
}

This is highly related to my previous question about fixing a time-format apostrophe issue that was breaking Boomerang Calendar functionality for scheduling events.

The solution: Pull the values from a Range into a 2D array using the Range.getValues() function. Process each value in the 2D array (row-by-row is probably the most logical method), then update the index of the edited value with the new value (see Srik-answer comments in code). After that, put the elements in the 2D array back into the original Range by using Range.setValues(2Darray). I hope this helps you if you came across it! This is also much faster than calling the API multiple times as seen in my original code.

function myNewLibraryFunction(startCol, numColumns) {

  // Get the active spreadsheet to run the script on
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Get the active sheet within the document to run the script on
  var sheet = spreadsheet.getActiveSheet();

  // Get max number of rows needed to process
  var maxRows = sheet.getLastRow();

  // Get the range for the startTime column and endTime column
  var dataRange = sheet.getRange(1, startCol, maxRows, numColumns);
  var values = dataRange.getValues();

  // Get the value in each cell, remove apostrophes from the start,
  // and replace the value in that cell
  for (var i = 1; i < maxRows; ++i) {

// Get the values for startTime and endTime
var startTime = values[i][0];
var endTime = values[i][1];

    // Remove apostrophes from start of startTime
    while(startTime.charAt(0) == "'") {
      startTime = startTime.substring(1);
    }

    // Remove apostrophes from start of startTime
    while(endTime.charAt(0) == "'") {
      endTime = endTime.substring(1);
    }

    values[i][0] = startTime; // New stuff from Srik's answer
    values[i][1] = endTime; // New stuff from Srik's answer
  }
  dataRange.setValues(values);
  SpreadsheetApp.flush(); // New stuff from Srik's answer
}

Answer 1

Basically, most of the function calls that you make to the APIs listed in GAS will require more time than regular JavaScript. In your case, reduce the number of calls to Range and Sheet classes

function fixApostrophes() {
  // Get the active spreadsheet to run the script on
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Get the active sheet within the document to run the script on
  var sheet = spreadsheet.getActiveSheet();

  // 1. Replace two calls and many other calls later with one. 
  var dataRange = sheet.getDataRange(); 
  var values = dataRange.getValues();// a 2D array

  // Get the value in each cell, remove apostrophes from the start,
  // and replace the value in that cell
  for (var i = 1; i < values .length ; ++i) {

    // Get the values for startTime and endTime
    var startTime = values [i][0];
    var endTime = values [i][1];

    // Remove apostrophes from start of startTime
    // These are okay. Regular Javascript - not time consuming
    while(startTime.charAt(0) == "'") {
      startTime = startTime.substring(1);
    }

    // Remove apostrophes from start of startTime
    while(endTime.charAt(0) == "'") {
      endTime = endTime.substring(1);
    }

  }

  dataRange.setValues(values);
}

TIP: You can see how much time each call took in the Execution transcript