0

I am using a chrome extension to update sheets through Apps Script.

There are 14 rows to update and I am sending the data for each row one by one. But the problem is that if I run the program once, it would update, let's say, 5 rows. If I run the program again, it will update some other number of rows. And another interesting thing is, every time I run the program, it will send different rows. The rows are never same.

Code.gs

function doGet(e){
  var sheetID=e.parameter.sheetID; 

  var firstName = e.parameter.firstName;
  var lastName = e.parameter.lastName;

  var count = e.parameter.count

  var ss = SpreadsheetApp.openById(spreadsheetId);
  SpreadsheetApp.setActiveSpreadsheet(ss);

    ss.appendRow([
                firstName,
                lastName,
                count
              ]);
}

Here count is the variable I send to check which rows are getting saved to sheets. And for every execution, this value differs. Sometimes it's [0, 12, 1, 3, 5] (when only 5 out of 14 rows gets saved) and other times it's [1, 5, 14, 13] (when only 4 out of 14 rows gets saved)

This is of course without making any changes in the code.

Following is the GET request that is sent from backgroun.js

background.js

params = 'firstName=' + firstName + '&' + 'lastName=' + lastName + '&' + 'count=' + count
url = url + encodeURI(params)
console.log(url)
fetch(url, {
    method: 'GET',
    mode: 'no-cors',
    })
    .then((response) => response)
    .then((data) => {
    console.log(data);
    })
    .catch((error) => {
    console.error('Error:', error);
});

What am I possibly doing wrong? Am I missing something?

Amit Sharma
  • 690
  • 8
  • 22
  • Did you try to use `SpreadsheetApp.flush()`? https://stackoverflow.com/questions/41175326/why-do-we-use-spreadsheetapp-flush – Yuri Khristich Jun 15 '21 at 21:31
  • @YuriKhristich Yes, I did. Didn't work. I think `flush()` is useful if you already have all the data in the Apps Script and then you want to dump data one by one. The case with my application is that App Script would receive only one data at a time but it's receiving and processing new data before the sheet has finished processing the previous data. – Amit Sharma Jun 15 '21 at 21:57
  • Can you provide some sample inputs and a sample expected output? – Ron M Jun 15 '21 at 22:19
  • maybe this? https://stackoverflow.com/questions/46938420/prevent-google-app-script-from-executing-in-parallel or this? https://developers.google.com/apps-script/reference/lock/ – Yuri Khristich Jun 15 '21 at 22:41
  • @RonM Sample inputs are given in the form of `url = url + encodeURI(params)`. This represents a single input. I have 14 of those to send which apparently happen instantaneously and hence overwrite data in the sheet because the previous write operation isn't complete when the new input is received by the app script – Amit Sharma Jun 16 '21 at 00:37
  • @YuriKhristich Locking doesn't work either, supposedly for the same reasons by `flush()` doesn't work either – Amit Sharma Jun 16 '21 at 00:38
  • Can you provide a sample logs for both your client(background.js) and server(Code.gs) side? You can redact sensitive information in the logs – Ron M Jun 16 '21 at 16:25

1 Answers1

0

I eventually had to use getActiveSheet() by modifying my client-side code, which was first sending the data one by one to the apps script, to object of arrays -

{ 1: [a,b,c], 
  2: [x,y,z],
  3: [p,q,r],
  ...
}

My final Apps Script code was as follows:

Apps Script (Code.gs)

function doPost(e){
    try 
    {
      var arr = [];
      var data = JSON.parse(e.postData.contents);

      for (var val in data){
        arr.push(data[val])
      }

      var ss = SpreadsheetApp.openById(spreadsheetId);
      SpreadsheetApp.setActiveSpreadsheet(ss);

      var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
      SpreadsheetApp.getActiveSheet().getRange(lastRow + 1,1,arr.length, arr[0].length).setValues(arr);

    } 
    catch (error) 
    {
      return Logger.log(error.toString());
    }
}
Amit Sharma
  • 690
  • 8
  • 22