Optimizing Spreesheet data

Question

I am doing a script that extracts data from a google spreadsheet and generates google docs from that data. The script seems to work but five minutes is not enough to get through all my files, can anybody guide me in the right direction?

function createDocument() {
  var sheetsURL="";

  var title = Sheets.Spreadsheets.Values.get(sheetsURL, 'B1:B191');
  var ksvalue = Sheets.Spreadsheets.Values.get(sheetsURL, 'A1:A191');
  var q1 = Sheets.Spreadsheets.Values.get(sheetsURL, 'I1:I191');
  var q2 = Sheets.Spreadsheets.Values.get(sheetsURL, 'J1:J191');
  var q3 = Sheets.Spreadsheets.Values.get(sheetsURL, 'K1:K191');
  var q4 = Sheets.Spreadsheets.Values.get(sheetsURL, 'L1:L191');
  var templateId = '';
  
 for(var i = 0; i < title.values.length; i++){
    
    //declare vars to call later
    var titles = title.values[i][0];
    var ksv= ksvalue.values[i][0];
    var q1s = q1.values[i][0];
    var q2s = q2.values[i][0];
    var q3s = q3.values[i][0];
    var q4s = q4.values[i][0];

    
    //Make a copy of the template file
    var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
    
    //Rename the copied file
    DriveApp.getFileById(documentId).setName('' + titles + ' '+ ksv +'');
    
    //Get the document body as a variable
    var body = DocumentApp.openById(documentId).getBody();
    
    
    //replaces tags from the doc with the cells value
    body.replaceText('##TITLE##', titles)
    body.replaceText('##QUESTION1##', q1s)
    body.replaceText('##QUESTION2##', q2s)
    body.replaceText('##QUESTION3##', q3s)
    body.replaceText('##QUESTION4##', q4s)
    
  }

}

score 0 · Answer 1 · answered May 11 '21 at 15:23

My usual approach in such cases is adding some other column, in which I set the state as processed, and in case of timeout I skip these lines on next run.

For example, assuming in your case we add column #12 (M) which would contain "Done" if the template for that file is created.

In this case, if the script times out, on next run it resumes from the point it stopped on.

Here is approximate example for your case

function createDocument() {
 var start = Date.now();

 function isLate() {
    // Script default timeout is 6 min, so after 5 we shutdown gracefully
    return (Date.now() - start) > 5 * 60 * 1000 
 }

 var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1") 
 var rows = sheet.getDataRange();

 rows.map(function(row, index) {
    // ---> Here we skip the files already created
    if(row[12] !== 'Done' && ! isLate()) {
        // Make a copy of the template file
        var doc = DriveApp.getFileById(templateId).makeCopy();
        
        // Rename the copied file
        doc.setName('' + titles + ' '+ row[0] +'');
        
        // Get the document body as a variable
        var body = doc.getBody();        
        
        //replaces tags from the doc with the cells value
        body.replaceText('##TITLE##', row[1])
        body.replaceText('##QUESTION1##', row[8])
        body.replaceText('##QUESTION2##', row[9])
        body.replaceText('##QUESTION3##', row[10])
        body.replaceText('##QUESTION4##', row[11])

        doc.saveAndClose();

        // --> Here we mark this file as created, to skip on next run
        sheet.getRange(index + 1, 12).setValue('Done');
    }
            
  });

}

You can remove in fact isLate check, but the script will finish with error in this case. IsLate() check makes it for more graceful shutdown in case of timeout

Haven't run it, so not sure it'll work 100% out-of-box on your side but you can get the general idea.

score 0 · Answer 2 · answered May 11 '21 at 19:53

This might not be enough but you could same some execution time by assigning the name to the Google document at the time that you script makes the copy. In other words, instead of

//Make a copy of the template file
var documentId = DriveApp.getFileById(templateId).makeCopy().getId();

//Rename the copied file
DriveApp.getFileById(documentId).setName('' + titles + ' '+ ksv +'');

use

//Make a copy of the template file and set its name 
var documentId = DriveApp.getFileById(templateId).makeCopy(titles + ' '+ ksv +'').getId();

Also you might save time by adding the Done to several cells at once instead of doing this one by one, just be sure to add this values before the execution time is exceeded.

Optimizing Spreesheet data

2 Answers2