0

I've not found SO posts on this issue. I'm not clear what role Lock Service has when writing to spreadsheet with UrlFetchApp.

I am asking about preventing a problem--not about a problem that currently is occurring (and which I don't know how to adequately simulate). Should Lock Service be used with UrlFetchApp "put" method for writing to a Google spreadsheet to prevent overwriting by near-concurrent users of the same script? And, if so, is the basic code grammar correct as outlined below?

Secondary question: I see no option with UrlFetch for a "SpreadsheetApp.flush()"-type command to apply all pending spreadsheet changes before releasing the lock. Does UrlFetchApp ensure all sheet changes are completed before returning?

Again, there is not now a problem writing to the spreadsheet using UrlFetchApp--the question is about preventing loss or overwriting of data with near-concurrent execution of the code.

 var lock = LockService.getScriptLock();
       try {
         lock.waitLock(15000); // wait 15 seconds for others' use of the code section and 
           lock to stop and then proceed
       } catch (e) {
           Logger.log('Could not obtain lock after 15 seconds.');
           return "Error: Server busy try again later."
       }
  var url= `https://sheets.googleapis.com/v4/spreadsheets/${ssID}/values/${postRange}?valueInputOption=RAW`;
    var options = {
        muteHttpExceptions: true,
        contentType: 'application/json',
        method:   'put',    
        payload: JSON.stringify(data), data
        headers: { Authorization: 'Bearer ' + clientToken }
        };
   var response= UrlFetchApp.fetch(url,options); 
 lock.releaseLock();
// END - end lock here
DougMS
  • 45
  • 5
  • I have to apologize for my poor Engish skill. Can I ask you about the detail of your current issue of your script? – Tanaike Mar 21 '22 at 00:23
  • The question is unclear and the code is uncomplete. Please add a [mcve] and clarify what are you asking. – Rubén Mar 21 '22 at 02:18
  • @Tanaike thank you for reviewing this question. I have revised the question and hope it now is clearer. – DougMS Mar 21 '22 at 03:45
  • @Rubén thank you for reviewing this question. I have revised the question and hope it is now properly framed. – DougMS Mar 21 '22 at 03:46
  • 1
    @DougMS You are welcome. By the way, the question still doesn't include a [mcve]. Also please bear in mind that each post should include only one question. – Rubén Mar 21 '22 at 03:49
  • 1
    By the way 2: It looks that using UrlFetchApp is over complicating your script as what it's apparently doing could be done by using either SpreadsheetApp or the Advanced Sheets Service. – Rubén Mar 21 '22 at 03:52
  • Thank you for replying. I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. I would be grateful if you can forgive my poor English skill. – Tanaike Mar 21 '22 at 05:12

1 Answers1

1
  • The use of LockService itself seems to be fine for your code. You could also consider using tryLock instead of waitLock, as you prefer.
  • If you don't have a specific reason to do otherwise, I'd suggest using the Spreadsheet Service or the Advanced Sheets Service instead of using UrlFetchApp. It would simplify your code.
  • When UrlFetchApp returns, the API request has been completed. All sheet changes should have been made by then. flush only makes sense in the context of the Apps Script service if you want to use updated data in the same script that made the update, since a script might not apply spreadsheet changes until the end of it.
Iamblichus
  • 18,540
  • 2
  • 11
  • 27
  • Thank you! This eases concern about overlapping fetch operations. Using UrlFetchApp because: a) need user authenticated Gmail address (so must execute webapp as user because Google federated authentication no longer available for webapps--also reduces issues with quota limits), b) am working with project spreadsheets (not user spreadsheets) and cannot share the spreadsheets with users, so employ UrlFetch with service account authentication to access spreadsheets on behalf of the users. – DougMS Mar 21 '22 at 19:48