CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsconcurrencylocking
Emile
Emile

Reputation: 53

How can I facilitate concurrent users on a Google Apps Script Webapp?

I'm currently creating a webapp that reads and writes to a Google Sheet. It is essentially a form app which takes inputs from a HTML form, writes it into a sheet using appendRow() (and subsequent use of getRange() and setValue() to append further data onto that row). Each row represents an instance of the form being filled. See example code of the write process.

/*
Function that is called in Index.html whenever the 'Next' button pressed. This 
gets all inputs from HTML form and stores them in the spreadsheet.

Parameters:
inputArray: a 1x3 array which contains the owner, reference and type of scale.
*/

function addProjectInputs(inputArray) {
  var sheetName = "Inputs";
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);
  Logger.log(sheet);
  var d = new Date();
  var tz = spreadsheet.getSpreadsheetTimeZone();
  var d = [Utilities.formatDate(d,tz,  "dd/MM/yyyy")];
  var data = d.concat(inputArray);
  Logger.log(data);
  sheet.appendRow(data);
}

/* Function to append further data onto the row created from 
addProjectInputs() */

function addFurtherInputs(idlerArray) {
  var sheetName = "Inputs";
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);

  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn()-42;

  var arrayEmpty = idlerArray.every(function(i) { return i === ''; });
  Logger.log(arrayEmpty);

  if (arrayEmpty !== true) {
    for (i = 0; i < idlerArray.length; i++) {
      var range3 = sheet.getRange(lastRow,lastColumn+1+i,1,1);
      range3.setValue(idlerArray[i]);
    }
  }
}

The summary page of the app (page 5, appears after all writing steps) then reads this data (using .getRange().getValues()) and then displays them on the page.

This works fine when one user is on it - however this app will get used by up to 20 people, potentially concurrently. This is an issue. For example, if a second user were to start using the app, their data would then start overwriting the first user's by adding a new row, thus 'archiving' the first user's data. I've come across the following potential solutions:

  1. Making use of the LockService to force only one person to use the app at a time (not ideal since the app would be used for quoting customers and don't really want to keep users waiting).
  2. Multithreading
  3. Stacks

I'm not sure how 2 or 3 would work, or even possible in GAS. Could anyone please shed some light on if it is possible to facilitate multiple users, and how?

Thanks in advance

Upvotes: 5

Views: 2193

Answers (2)

Cameron Roberts
Cameron Roberts

Reputation: 7377

Rather than locking, I would approach this by generating a unique ID for each user session, which you can store in the sheet and use to match up the values later.

On your entry point [probably doGet(), maybe doPost()], generate a UUID with var session_id = Utilities.getUuid();

Then pass that sessiond ID to your template, and include it in all future requests back to the Apps Script.

Write the UUID to a column in the sheet in your addProjectInputs() function, then modify your addFurtherInputs() function to locate the correct row based on the UUID, (rather than always taking the last one) and write your updates to that row.

This approach guarantees you can have many users working without worrying about overwriting each other, and with no delays due to locks.

See https://developers.google.com/apps-script/reference/utilities/utilities#getUuid()

Upvotes: 3

oshliaer
oshliaer

Reputation: 4979

I tried all of those solutions.

  1. LockService works well even for large sheets and more users. You need to track the frequency of actions of your users. If you don’t use a simulated web socket behavior you’re usually afraid of nothing.
  2. Multithreading is not applicable here.
  3. Stacks. If I understand you correctly, the stacks are organized based on sheets and formulas. Usually I divide my Sheets Apps by access level and by type of actions. About access everything is clear. Types of action: push, calculates, pull. Each sheet has only one action. I use appendRow unary entries for push. And also I use classes to calculate what will be in the push. This allows me to save one call and not take pull.getDataRange().getValues() every time after I write to pull. I use formulas so that pull is always relevant. You must be familiar with the concepts of "slice of the past", "balances for the period" in order to realize this.

It can be a little foggy because I speak English poorly. My apologize.

Upvotes: 0

Related Questions