Benchmark: Concurrent Writing to Google Spreadsheet using Form

report / GasTips / spreadsheet / Benchmark
Google Apps Script / spreadsheet / concurrent-processes / form

Gists

  • Published: September 15, 2021

  • Updated: September 17, 2021

    • From the discussions, added data by changing the wait time of LockService for Web Apps.

Kanshi Tanaike

Introduction

When the users try to write to a Spreadsheet using a form, the developers have to consider the concurrent submission from the form. For example, when multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet. So it is considered that it is important to know the information about the concurrent writing to Google Spreadsheet using a form. In this report, such a situation was investigated.

As the result, when the success rate for writing concurrently to Google Spreadsheet is investigated, it was found that the concurrent writing with Web Apps created by Google Apps Script was suitable rather than Google Form. The threshold number of users for succeeding to write all data to Spreadsheet was 60 for Web Apps and 35 for Google Form, respectively. And, when Web Apps is used, it was also found that Lock Service and the long wait time was definitely required to be used for the multiple submission.

Experimental procedure

In order to investigate the success rate for writing concurrently to Google Spreadsheet with a form, in this report, Google Form and Web Apps were used. As the sample situation, it supposes that text data is submitted to Google Spreadsheet with a form. Figure 1 shows the Google Form used in this report. There is only one question for inputting text data.

Fig. 1: Google Form used in this report

For Web Apps, 2 patterns with and without the LockService were used. The sample script can be seen at Appendix. Ref

In this report, the data was submitted for each form using Google Apps Script. In order to achieve the concurrent submission, the method of fetchAll in the Class UrlFetchApp was used, because it has already been reported that the method of fetchAll works with the asynchronous process. Ref

The success rate for writing concurrently to Google Spreadsheet was measured as following sample flow.

  1. Submitting the text data to each form with 50 requests. The number of requests is changed for each condition.
  2. Counting the submitted data rows on Google Spreadsheet.
  3. When the number of submissions is the same as the number of submitted data rows, no error occurs. When the number of submissions is NOT the same as the number of submitted data rows, the error occurs.
  4. The cycle from 1 to 3 was run 100 times and the success rate was obtained.

I worry that each detailed data point in my environment might be different from that of other user’s environments. But I think that the trend of this result can be used.

In this report, the inputted text data is written to Google Spreadsheet by submitting the form. So when the data is submitted with Google Form, when the Google Apps Script is run, this data might not be able to be used. And, when the script for Web Apps is changed, I’m not sure whether the same result can be obtained. So please be careful about this.

Results and discussions

Figure 2 shows the max concurrent submissions vs. the success rate for writing concurrently to Google Spreadsheet with Google Form. From Fig. 2, it is found that when the number of concurrent submissions is below 35, the success rate is 100 %. This indicates that the threshold value of this situation is 35 and when the number of concurrent submissions is more than 35, all submitted data cannot be saved to the Google Spreadsheet.

Fig. 2: Max concurrent submissions vs. Success rate for writing concurrently to Google Spreadsheet with Google Form.

Figure 3 shows the max concurrent submissions vs. the success rate for writing concurrently to Google Spreadsheet with Web Apps created by Google Apps Script. From Fig. 3, it is found that when the LockService is not used, even the number of concurrent submissions is 2, all data cannot be saved. This indicates that using the LockService is very important for submitting from multiple users to Web Apps. It is also found that when the LockService is used, the threshold value is 26 for the wait time of 20 seconds and 60 for the wait time of 350 seconds. From this result, it was found that the wait time of LockService is also important. It is considered that this result indicates that for example, when the process cost of Google Apps Script for creating Web Apps is high, the wait time of 350 seconds might not be enough.

Fig. 3: Max concurrent submissions vs. Success rate for writing concurrently to Google Spreadsheet with Web Apps. Blue, red and yellow dots mean without LockService, with LockService by the wait time of 20 seconds and with LockService by the wait time of 350 seconds in Web Apps created by Google Apps Script, respectively.

The threshold value of 60 for Web Apps using LockService with a long wait time is larger than that of Google Form. From this result, it is considered that when the multiple users are concurrently submitted to Spreadsheet using a form, Web Apps using LockService with a long wait time is suitable.

And, when the slopes of the 3 lines shown in Fig. 3 are seen, I thought that when the slope is large, the remaining capacity for processing the concurrent submissions might show smaller.

Summary

In this report, the information about the concurrent writing to Google Spreadsheet using a form was investigated. From this experiment, the following results were obtained.

  1. When the multiple users are concurrently submitted to Spreadsheet using a form, Web Apps created by Google Apps Script is suitable rather than Google Form.

  2. For Google Form, the threshold value of concurrent submissions is 35.

  3. For Web Apps, the threshold value of concurrent submissions is 60.

  4. When Web Apps is used for concurrent submissions, use LockService with a long wait time is required.

For concurrently submitting by more than 60 users

  • About the threshold value of 60 for Web Apps, for example, I think that when the possibility for concurrently submitting by 60 users is low in your users, Web Apps can be used for the situation even when the total number of users is more than 60 .

  • When you worry about the error that the users more than 60 are concurrently submitted, I think that the retry for submitting can be achieved using the error process. In this case, I think that it is required to check the saved row on Spreadsheet.

Appendix

Sample scripts for Web Apps

Without LockService

const doGet = ({ parameter: { sample } }) => {
  try {
    SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].appendRow([sample]);
  } catch (e) {
    return ContentService.createTextOutput("error");
  }
  return ContentService.createTextOutput("done");
};

With LockService

const doGet = ({ parameter: { sample } }) => {
  const lock = LockService.getDocumentLock();

  // This is an important point.
  if (lock.tryLock(350000)) {
    try {
      SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].appendRow([sample]);
    } catch (e) {
      return ContentService.createTextOutput("error");
    } finally {
      lock.releaseLock();
      return ContentService.createTextOutput("done");
    }
  } else {
    return ContentService.createTextOutput("timeout");
  }
};

Sample scripts for testing the concurrent submissions

For Google Form

const url =
  "https://docs.google.com/forms/d/e/###/formResponse?submit=Submit&entry.###=";
const submissions = 10;
const requests = Array(submissions)
  .fill("")
  .map((_, i) => ({ url: url + i, muteHttpExceptions: true }));
const res = UrlFetchApp.fetchAll(requests);

For Web Apps

const url = "https://script.google.com/macros/s/###/exec?sample=";
const submissions = 10;
const requests = Array(submissions)
  .fill("")
  .map((_, i) => ({ url: url + i, muteHttpExceptions: true }));
const res = UrlFetchApp.fetchAll(requests);

 Share!