Google Sheets Script populating data very slowly

Question

I am currently trying to pull the following into google sheets via a very simple script.

The view is about 16k rows and A-W in width. This code executes and in MySQL in about 1 second. When trying to run the code as a script it is timing out at more than 1800 seconds.

Does anyone know how to get the data to populate faster into sheets?

Code

    var query = ""; 
query += ' SELECT '
+' * '
+' FROM rep_fin_sml_dash_es'



var result  = statement.executeQuery(query);
var num_col = result.getMetaData().getColumnCount();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Supply Data");
sheet.getRange('A2:W').clearContent();


var header = ['Accommodation Provider'];
for(var h = 1; h < header.length; h++ ){
sheet.getRange(1, h+1).setValue(header[h]);
}

var cell = sheet.getRange('A2');

var row = 0;
while(result.next()) {
for(var i=0; i < num_col; i++) {
   cell.offset(row, i).setValue(result.getString(i+1));
 }
 row++;
}

//tidy up
result.close();
statement.close();
connection.close(); 

}

Possible duplicate of [Google app script timeout ~ 5 minutes?](https://stackoverflow.com/questions/14450819/google-app-script-timeout-5-minutes) — TheMaster, Oct 24 '18 at 16:38
If you can, try to avoid repeated calls to the server. getValue()/setValue() is a call to the server. Try to use getValues()/setValues() instead. — TheWizEd, Oct 24 '18 at 17:18

score 0 · Accepted Answer · answered Oct 29 '18 at 03:45

0

The reason that your script is taking so long is that you are writing every value to the sheet as you retrieve it from the database. There two setValue commends inside the loop. At, say, 0.2 seconds each x 16,000 rows - that's a lot of time. A more efficient approach is to push the results into a 2d array and then after the loop paste the array to the sheet with a single command. Your execution should drop to around a few seconds.

Keep the separate loops for the header and data rows, but change them like this. (Untested)

    // declare some arrays
    masterValue = [];
    value = [];

    // get the header row
    for (var h = 1; h < header.length; h++) {
      value.push(header[h]); // header row to an array
    }
    masterValue.push(value); //push the header row into a 2d array.

    // Loop through the data
    while (result.next()) {
      value = []; // reset the value array
      for (var i = 0; i < num_col; i++) {
        value.push(result.getString(i + 1)); // get the data from each column
      }
      row++;
      masterValue.push(value);
    }

<<insert code closing connection >>

  // update the target sheet with the contents of the array
  sheet.getRange(1, 1, masterValue.length, value.length);
  sheet.setValues(masterValue);

answered Oct 29 '18 at 03:45

Tedinoz

5,911
3
25
35

Thanks @tedinoz The issue is now that it is placing 870 lines in at once and then it is failing with this error: Service timed out: Spreadsheets (line 56, file "Offers") It seems like the size of the data is too much? If I run the code on SQL and just do the copy and paste it can handle it so I am not sure why it is now failing – Jonathan Child Oct 30 '18 at 10:42
@JonathanChild Would you look at the Execution transcript and see how long it takes to complete the import of the results. And then, generally, where is the script spending its time? Is it possible to get access to your spreadsheet? And/or post all your code. I'm interested in what is happening on line 56. – Tedinoz Oct 30 '18 at 13:38
Hi @tedinoz can you request access to this: (https://script.google.com/macros/d/MBE8Aituye8T_bXWTpBjjQ5MRS7M0hLX_/edit?uiv=2&mid=ACjPJvFkmz62Ky2PIdh8VLk0APNmPn8TxjDZ83EoT-wKnzO46MQbYU12D173YkKOcYwweZPRXtWEa1kDkbLiLA1mfD4a-DAGbOAk4hjrNBubOs9ueJ6JgCiLTbCe2Wxj2wvjN6fFsdsOwbI) If you look at the script - offers this is the error on executions: Logging output too large. Truncating output. – Jonathan Child Oct 31 '18 at 15:12
Thank you for giving me access to your sheet. I've looked at the spreadsheet and the code changes that I proposed. The code is definitely much more efficient, completing the mySQL import/write in @180 seconds compared to the initial time of 1800 seconds. The reason for the slow load time that you are experiencing is unrelated to the mySQL import and has more to do with the content contained in other sheets of the spreadsheet which are causing prolonged recalculation times. – Tedinoz Nov 02 '18 at 03:41
Thanks, @tedinoz - that was super helpful. I think we will just have to be smarter with the number of calculations we are doing and split out the display into additional documents – Jonathan Child Nov 05 '18 at 09:57

Google Sheets Script populating data very slowly

1 Answers1