Google sheets script Exceeded maximum execution time

Question

I wrote a script to import stock data from a csv file stored in Google Drive to an existing google sheet.

In one function I'm doing this for multiple csv files. Unfortunately I get "Exceeded maximum execution time" sometimes, but not all the time.

Do you have an idea how I can boost performance on this:

   //++++++++++++++ SPY +++++++++++++++++++

var file = DriveApp.getFilesByName("SPY.csv").next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());


//Create new temporary sheet
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var yourNewSheet = activeSpreadsheet.getSheetByName("SPY-Import");

if (yourNewSheet != null) {
    activeSpreadsheet.deleteSheet(yourNewSheet);
}

yourNewSheet = activeSpreadsheet.insertSheet();
yourNewSheet.setName("SPY-Import");

//Import
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

//Copy from temporary sheet to destination
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A:B').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('SPY'), true);
spreadsheet.getRange('A2').activate();
spreadsheet.getRange('\'SPY-Import\'!A:B').copyTo(spreadsheet.getActiveRange(), 
SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);


//Delete temporary sheet

// Get Spreadsheet Object
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Get target sheet object
var sheet = spreadsheet.getSheetByName("SPY-Import");
// Delete
 spreadsheet.deleteSheet(sheet);

Thanks in advance!

Answer 1

I believe your situation and goal as follows.

• You have several CSV files like SPY.csv.
• Your Spreadsheet has the several sheet corresponding to each CSV file like SPY.
• You want to put the values from the CSV data to the Spreadsheet.
  • You want to put the values of the column "A" and "B" of the CSV data.
• In your current situation, you copied the script in your question several times and run them by changing the csv filename and sheet name.
• You want to reduce the process cost of your script. I understood your goal like this.

Modification points:

• SpreadsheetApp.getActiveSpreadsheet() is used several times. And, activate() is used several times.
  • I think that in your case, SpreadsheetApp.getActiveSpreadsheet() can be declared one time, and activate() is not required to be used.
• In order to copy the CSV data to Spreadsheet, the CSV data is put to a temporal sheet and the required values are copied to the destination sheet.
  • In this case, I think that the CSV data is directly put to the destination sheet by processing on the array.

I think that above points lead to the reduction of process cost. When above points are reflected to your script, it becomes as follows.

Modified script:

Please copy and paste the following script and prepare the variable of obj. When you run the script, the CSV data is retrieved and processed, and then, the values are put to the Spreadsheet.

function myFunction() {
  var obj = [
    {filename: "SPY.csv", sheetname: "SPY"},
    {filename: "###.csv", sheetname: "###"},
    ,
    ,
    ,
  ];
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  obj.forEach(({filename, sheetname}) => {
    var file = DriveApp.getFilesByName(filename);
    if (file.hasNext()) {
      var sheet = ss.getSheetByName(sheetname);
      if (sheet) {
//        sheet.clearContents(); // Is this requierd in your situation?
        var csv = DriveApp.getFileById(file.next().getId()).getBlob().getDataAsString();
        var values = Utilities.parseCsv(csv).map(([a, b]) => [a, b]);
        sheet.getRange(2, 1, values.length, 2).setValues(values);
      }
    }
  });
}

Note:

• Please use this script with enabling V8
• I'm not sure about your CSV data. So when Utilities.parseCsv(csv) cannot be used, please use the delimiter.
• In this modification, Spreadsheet service is used. If above modified script occurs the same error of Exceeded maximum execution time, please tell me. At that time, I would like to propose the sample script using Sheets API.

References:

• Spreadsheet Service
• parseCsv(csv)