CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Kim Miller
Kim Miller

Reputation: 886

Google Apps Script Batch Executes with Unexpected Timing

The following code results in a blank sheet, assumably due to .clear() executing last, even though it's listed first?

        const ss = SpreadsheetApp.getActiveSpreadsheet();
        var contractSheet = ss.getSheetByName(sheetName);
        contractSheet.clear();

        const body = {requests: [{pasteData: {html: true, data: table[0], coordinate: {sheetId: ss.getSheetByName(sheetName).getSheetId()}}}]};
        Sheets.Spreadsheets.batchUpdate(body, ss.getId());

If I remove the .clear() the script runs and populates the sheet fine. But I need to clear the sheet before loading the new data from the final batch request.

Any ideas would be much appreciated. :-)

Upvotes: 1

Views: 104

Answers (1)

Tanaike
Tanaike

Reputation: 201713

In this case, how about using SpreadsheetApp.flush() in your script as follows? I thought that after clear() was run, it is required to reflect it and request the batchUpdate method of Sheets API.

Modified script:

const ss = SpreadsheetApp.getActiveSpreadsheet();
var contractSheet = ss.getSheetByName(sheetName);
contractSheet.clear();

SpreadsheetApp.flush(); // Added

const body = {requests: [{pasteData: {html: true, data: table[0], coordinate: {sheetId: ss.getSheetByName(sheetName).getSheetId()}}}]};
Sheets.Spreadsheets.batchUpdate(body, ss.getId());

Reference:

Upvotes: 4

Related Questions