CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Josee
Josee

Reputation: 170

How to speed up writing onto google spreadsheet

I am trying to write an add on for google spread sheet that takes a csv and write onto a new sheet. The problem is that the csv can potentially be very large, reaching up to around a million rows. I managed to fix the cell limit problem by deleting unnecessary columns, but my script keeps timing out even at 500,000 rows. So I was wondering if there is a way to increase the write speed?

Here is my code where I create a new sheet and populate it with the csv data:

function writeDataToSheet(data,sheetName) {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.insertSheet(sheetName);
  const neededColumns = data[0].length;
  const totalColumns = sheet.getMaxColumns();
  const columnsToDelete = totalColumns - neededColumns;
  if(columnsToDelete > 0) sheet.deleteColumns(totalColumns , columnsToDelete);
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
  return sheet.getName();
}

To be clear, data is supposed to be a 2 dimensional array, converted from csv data using Utilities.parseCsv().

Upvotes: 0

Views: 1133

Answers (1)

Tanaike
Tanaike

Reputation: 201358

In my measured benchmark, when the data is large, when Sheets API is used instead of the Spreadsheet service (SpreadsheetApp), the process cost can be reduced. Ref This situation is almost the same with and without V8 runtime. This has already been mentioned in TheMaster's comment. In your script, when Sheets API is used, it becomes as follows.

Before you use this modified script, please enable Sheets API at Advanced Google services.

Modified script 1:

In this modification, your data is used. Please modify your script as follows.

From:

sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

To:

Sheets.Spreadsheets.Values.update({values: data}, ss.getId(), sheet.getSheetName(), {valueInputOption: "USER_ENTERED"});

Modified script 2:

In this modification, from To be clear, data is supposed to be a 2 dimensional array, converted from csv data using Utilities.parseCsv()., the CSV data before data is directly used. Please modify your script as follows. So, please directly put csvData to the following request.

From:

sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

To:

function writeDataToSheet(csvData, sheetName) {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.insertSheet(sheetName);
  const requests = { requests: [{ pasteData: { data: csvData, coordinate: { sheetId: sheet.getSheetId() } } }] };
  Sheets.Spreadsheets.batchUpdate(requests, ss.getId());
}

References:

Upvotes: 3

Related Questions