Apps Script execution of .setValues() takes 20 seconds

Question

I am having trouble to understand why this takes so long to execute and whether there is a solution to this. It requires 20 seconds to execute the .setValues() line. The grid is about 2000 (row) by 100 (col) cells.

Here is a link to the test speed sheet for testing material, that is like my data-set.

function test() {

    var active = SpreadsheetApp.getActiveSpreadsheet();

    var logs_sheet = active.getSheetByName("Logs");
    logs_sheet.appendRow([new Date(), "", "", "STARTING EXECUTION"]);

    var test_sheet = active.getSheetByName("Test");
    var test_data = test_sheet.getRange(1, 1, test_sheet.getLastRow(), test_sheet.getLastColumn()).getValues();

    var combine = test_data.slice();

    combine[1].splice(3, 1, new Date());

    combine.splice(2017, 1);

    combine.splice(0, 0, combine[1]);

    logs_sheet.appendRow([new Date(), "", "", "finished combine"]);

    test_sheet.getRange(1, 1, combine.length, combine[0].length).setValues(combine);

    logs_sheet.appendRow([new Date(), "", "", "FINISHED EXECUTION"]);
}

What's more is that setValues() appears to update the sheet quite fast, but then it continues running something invisible before moving on.

For Google Spreadheets, this is a humongous sized grid. The default number of rows for any new spreadsheet is just 100. If you go to View - > Execution Transcript, you will see that even methods as simple as appendRow() take almost a second to execute while getValues() is pushing 4 secs. In Excel, you can have the formula fill the range of 40k rows almost in a second. Google Sheets is a cloud service that still has lots of performance limitations. I could be wrong about this particular example - would love to see others weigh in on this. — Anton Dementiev
Thanks Anton, I was wondering whether the grid is large or not. Interesting is that getting the Values is much faster then setting them. Why would that be? — philipeachille
The existing values are probably pre-cached, so calling getValues() simply pulls them out of the cache instead of iterating over each row and column at runtime. — Anton Dementiev

philipeachille philipeachille · Accepted Answer · 2018-02-06T18:47:53

Here is a possible solution / workaround I came up with.

Writing the array to a Google Doc for temporary storage only takes 0.06 seconds, rather than 20 seconds storing it in Sheets!

I assume that this can be quite a large array then, but I have not tested the limits of this workaround. I did however try to open the Google Doc and that is not really possible. But there is no need to open it.

var doc_database = DocumentApp.openById(" some doc ID here ").getBody();
var old_array = JSON.parse(doc_database.getText());

doc_database.setText('');

// run old_array calcs and updates here

doc_database.setText(JSON.stringify( ... new array here .... ));

hope this hack helps others.

Best PAV

Apps Script execution of .setValues() takes 20 seconds

1 Answers