Google App Script Timeout due to slowness

Question

This would be my first time posting, so I am sorry for the things I may or may not write.

I have a working Google Script which gets a file from an URL, parses the JSON then feed the data to a google Spreadsheet, one row at a time.

The problem that I have is that some of these files are large, 7000 rows large, which the script is unable to feed in the given time of 6 minutes. The speed of which it processes each row is quite slow, it is about 1 row every 1-3 seconds.

I believe that the problem is with the for clause, but I cannot seem to make it work. I am not that experienced with this kind of scripting, and this was made from "inspirations"

Is there a way to speed up the for process in order to fill out the sheet faster, so it wont timeout?


 SpreadsheetApp.getUi() 
     .createMenu('Update')
      .addItem('Refresh Report', 'getData')
     .addToUi();
}

function getData() {
 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 //var sheets = ss.getSheets();
 var datasheet = ss.getSheetByName('Data');

 var hasCreatedHeaders = false;
 
 //Url
var url = 'URL HERE';

 //Create header
 var headers = {
   'Content-Type': 'application/json',
   'Authorization': 'xxx'
 };

 //Options
 var options = {
   'method': 'get',
   'headers': headers
 };

 var response = UrlFetchApp.fetch(url, options);
 var json = response.getContentText();
 var data = JSON.parse(json);
 var table = data.Table1;
 
 datasheet.clear();
 
 //foreach row
 table.forEach(function(row) {
   var cols = [];
   var headers = [];

   for (var prop in row.Columns) {
     if (!hasCreatedHeaders)
       headers.push(prop);
     
     cols.push(row.Columns[prop]);
   }
 
   if (!hasCreatedHeaders) {
    datasheet.appendRow(headers);
    hasCreatedHeaders = true;
   }
      
  datasheet.appendRow(cols);
 
 });

}

Yuri Khristich · Accepted Answer · 2022-03-11 14:59:25Z

Try to change the loop table.forEach() this way:

//foreach row

var table_new = []; // <--- create a new empty array

table.forEach(function (row) {
    var cols = [];
    var headers = [];

    for (var prop in row.Columns) {
        if (!hasCreatedHeaders)
            headers.push(prop);

        cols.push(row.Columns[prop]);
    }

    if (!hasCreatedHeaders) {
        // datasheet.appendRow(headers);
        table_new.push(headers); // <--- add row to the array
        hasCreatedHeaders = true;
    }

    // datasheet.appendRow(cols);
    table_new.push(cols); // <--- add row to the array

});

// put the array on the sheet all at once
datasheet.getRange(1,1,table_new.length,table_new[0].length).setValues(table_new);

The problem is appendRow() method is quite time consuming. If you create the table as a 2D array you can put it on the sheet in one step with setValues() method. It's much faster.

References

Best practices

Collectives™ on Stack Overflow

Google App Script Timeout due to slowness

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related