CODEWITHSUNDEEP
firebasegoogle-apps-scriptgoogle-sheetsspreadsheetdrive
John Gorenfeld
John Gorenfeld

Reputation: 2455

Trouble grabbing the latest changes to the Google Sheet

I'm confused about how to make sure my App Script grabs the most recent changes to a Google Sheet. My Sheet has an image button on it that calls a script called StartSync (below.) The idea is that it grabs data from the active sheet, then sends it off to Firebase.

But if the user changes a cell and then runs the script, and it seems like the data that is sent is a few minutes old. In other words, if I inspect what data went out, it doesn't seem to reflect recent changes. In fact the outgoing data seem suspiciously like the state of things when I first opened the window and the words "Working..." appeared.

I'm not sure if this is because the script was evaluated a while earlier? Or is there some App Script command I could use to ensure that the latest changes are found when the API is found? Any help much appreciated.

function syncMasterSheet(excelData) {
/*
We make a PUT (update) request,
and send a JSON payload
More info on the REST API here : https://firebase.google.com/docs/database/rest/start
*/
var options = {
    method: 'put',
    contentType: 'application/json',
    payload: JSON.stringify(excelData)
};
var fireBaseUrl = getFirebaseUrl('language')

/*
We use the UrlFetchApp google scripts module
More info on this here : https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app
*/
try {
    var response = UrlFetchApp.fetch(fireBaseUrl, options);
    var userResponse = ui.alert('Done syncing. Do you need to take a closer look at what data went out?', ui.ButtonSet.YES_NO);
    if (userResponse == ui.Button.YES) {
        ui.alert(response);
    }
}
catch(e) {
    SpreadsheetApp.getActiveSpreadsheet().toast(e);
}
}

function startSync() {

//Get the currently active sheet
var sheet = SpreadsheetApp.getActiveSheet();
SpreadsheetApp.getActiveSpreadsheet().toast('Synced.');

//Get the number of rows and columns which contain some content
var [rows, columns] = [sheet.getLastRow(), sheet.getLastColumn()];
//Get the data contained in those rows and columns as a 2 dimensional array
var data = sheet.getRange(1, 1, rows, columns).getValues();

//Use the syncMasterSheet function defined before to push this data to the "masterSheet" key in the firebase database
syncMasterSheet(data);
}

Upvotes: 0

Views: 149

Answers (1)

Metric Rat
Metric Rat

Reputation: 316

SpreadsheetApp.flush()

Should update the spreadsheet. Put this at the start of things

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#flush()

Upvotes: 1

Related Questions