Hi I am trying to fetch data from a source and write it into a Google Sheet. Before writing the data, I am erasing the existing data in the target sheet. However every time I do this, when the script executes, first the data is cleared from the target sheet, then the new data is written, but after writing the data is cleared again!
I have tried changing the code to clear the sheet contents in multiple different ways and placing them at multiple different stages, all BEFORE the data write, it always end up clearing the contents a second time after the data write is complete.
The function looks something like this:
function getDataFromSomewhere() {
//Fetching data
var url = 'www.xyzsource.com';
var csv = UrlFetchApp.fetch(url);
var data = Utilities.parseCsv(csv);
const ss = SpreadsheetApp.openById('spreadsheet_id');
const sheet = ss.getSheetByName('sheet_name');
//Tried all of the following options to clear existing data (one at a time):
sheet.getDataRange().clearContent();
sheet.clearContents();
sheet.getRange(1,1).clearContent();
//Writing Data
Sheets.Spreadsheets.Values.update({values: data}, ss.getId(), sheet.getSheetName(),{valueInputOption: "USER_ENTERED"});
}
Also note that the function was working fine before I tried to clear the sheet contents prior to writing as previously the new data always had more rows than the old data. But due to some changes, currently the new data may or may not have more rows than the old data.
This is because Sheets API(Sheets advanced Google service) is asynchronous and faster than Google apps script. You can avoid Sheets API(use setValues) or use SpreadsheetApp.flush before calling Sheets service.
sheet.clearContents();
//flush clearing the sheet
SpreadsheetApp.flush();
//wait if it still clears after setting values
//Utilities.sleep(5*1000)
//Writing Data
Sheets.Spreadsheets.Values.update({values: data}, ss.getId(), sheet.getSheetName(),{valueInputOption: "USER_ENTERED"});
//or use `setValues` instead of `Sheets` Advanced Google services
//sheet.getRange(1, 1, data.length, data[0].length).setValues(data)