CODEWITHSUNDEEP
google-apps-script
titituono
titituono

Reputation: 1

Google App Script Web app conflict when recording data on spreadsheet

I am building a user interface thanks to a GAS web app. This script is connected to a spreadsheet to read and record data. I am able to record data from the user entry to the spreadsheet thanks to my current code but my concern is about the reliability of this app when multiple users will be connected to this app and will try to record data. In order to test the tool, I have created a 10 iterations loop on client side which send pre formatted data to the server side for recording into the spreadsheet. When I launch the function on one computer it works (10 lines are properly recorded) but when a second user activate the same function on its session the total number of lines recorded is random (15 or 17 instead of 20). When having a look to the spreadsheet when scriptS are running, I see that sometimes values on a line is overwritten by an other value (just like if the new row was not recorded at the proper place). The Web app is share as execute as me (so the server side code is executed with my account) In order to control what s going on, a lockservice has been implemented on the server side script (the part which records the data on the spreadsheet) and a new promise / async function on the client side.

function recordDataOnSheet(data){ // server side function to record data in the spreadsheet

  var lock='';   
  lock = LockService.getScriptLock();   
  var success = lock.tryLock(10000);//Throws exception if fail wait 10s  max to get a lock
  
  if (success){  
    var dat =dataSheet.getDataRange().getValues();
    lsRow++;
    data[0].length);  
    dat.push(data);
    dataSheet.getRange(1, 1, dat.length, dat[0].length).setValues(dat);
    Logger.log(dat.length);
  
  } else {Logger.log("Lock failed")};
lock.releaseLock();
} ```

and client side piece of script:


 function runGoogleSript(serverFunc,dat){ // function to mange the asynchronous call of a server sdide function

    return new Promise((resolve, reject) => {
    google.script.run.withSuccessHandler(data => {
    resolve(data)
    }).withFailureHandler(er => {
    reject(er)
    })[serverFunc](dat)
    });
  }

async function test (){  

  for (i=0;i<10;i++){    
  let d = new Date(); 
  setTimeout(() => { console.log(i) }, 2000);

  try {
    const data = await runGoogleSript("recordDataOnSheet",["06323","XX:",user.id,"2022-02-07", d.getMinutes() , d.getSeconds() ] );
  }
  catch (er) {alert(er);}       
  }       
  }

Upvotes: 0

Views: 81

Answers (1)

titituono
titituono

Reputation: 1

After dozen of test and tests, I have discovered that it was only an issue with the spreadsheet call. As I am calling the same file/sheet in a loop sometimes the previous activity (to update the data in the sheet) were not over. This was at the origin of the issue. I have simply added a SpreadsheetApp.flush(); at the end of my function recordDataOnSheet and it works and it is reliable

Upvotes: 0

Related Questions