如果LockService適用于多個用戶的通用腳本，那么在處理將資料發送到同一列的多個腳本時是否有選項？-有解無憂

我有 4 個不同的腳本鏈接到 4 個不同的帳戶，它們執行它們的功能，最后將值從特定單元格發送到最終電子表格，并在最終電子表格中發送到同一列資料：

腳本電子表格 1：

function Spreadsheet1() {

  HIDDEN CODE LINES → THEY ARE COLLECT DATA FROM AN API

  var first_sheet_id = SpreadsheetApp.openById('SPREADSHEET 1');
  var first_sheet_page = first_sheet.getSheetByName('Sheet86');
  
  var second_sheet_id = SpreadsheetApp.openById('SAME DESTINATION AS FOUR SCRIPTS');
  var second_sheet_page = second_sheet.getSheetByName('Sheet86');
  var r=1;
  while(second_sheet_page.getRange(r,1).getValue()) {
    r  ;
  }
  second_sheet_page.getRange(r,1).setValue(first_sheet_page.getRange(1,1).getValue());
}

腳本電子表格 2：

function Spreadsheet2() {

  HIDDEN CODE LINES → THEY ARE COLLECT DATA FROM AN API

  var first_sheet_id = SpreadsheetApp.openById('SPREADSHEET 2');
  var first_sheet_page = first_sheet.getSheetByName('Sheet86');
  
  var second_sheet_id = SpreadsheetApp.openById('SAME DESTINATION AS FOUR SCRIPTS');
  var second_sheet_page = second_sheet.getSheetByName('Sheet86');
  var r=1;
  while(second_sheet_page.getRange(r,1).getValue()) {
    r  ;
  }
  second_sheet_page.getRange(r,1).setValue(first_sheet_page.getRange(1,1).getValue());
}

腳本電子表格 3：

function Spreadsheet3() {

  HIDDEN CODE LINES → THEY ARE COLLECT DATA FROM AN API

  var first_sheet_id = SpreadsheetApp.openById('SPREADSHEET 3');
  var first_sheet_page = first_sheet.getSheetByName('Sheet86');
  
  var second_sheet_id = SpreadsheetApp.openById('SAME DESTINATION AS FOUR SCRIPTS');
  var second_sheet_page = second_sheet.getSheetByName('Sheet86');
  var r=1;
  while(second_sheet_page.getRange(r,1).getValue()) {
    r  ;
  }
  second_sheet_page.getRange(r,1).setValue(first_sheet_page.getRange(1,1).getValue());
}

腳本電子表格 4：

function Spreadsheet4() {

  HIDDEN CODE LINES → THEY ARE COLLECT DATA FROM AN API

  var first_sheet_id = SpreadsheetApp.openById('SPREADSHEET 4');
  var first_sheet_page = first_sheet.getSheetByName('Sheet86');
  
  var second_sheet_id = SpreadsheetApp.openById('SAME DESTINATION AS FOUR SCRIPTS');
  var second_sheet_page = second_sheet.getSheetByName('Sheet86');
  var r=1;
  while(second_sheet_page.getRange(r,1).getValue()) {
    r  ;
  }
  second_sheet_page.getRange(r,1).setValue(first_sheet_page.getRange(1,1).getValue());
}

如果LockService是多個用戶同時嘗試使用它的同一個腳本，它將起作用。

但就我而言，有四個帶有自動觸發功能的腳本（每 5 分鐘一次）運行并發送到同一個電子表格的同一列。

有什么辦法可以避免他們相遇并將價值觀放在同一條線上的風險？

If there is any way, please create a visual example of how to use it so that I understand the method as I believe it is not as simple as my knowledge limit.

uj5u.com熱心網友回復：

根據您更新的問題，在您的情況下，如何將 Web 應用程式與 LockService 一起使用？根據您的問題，我確認同時運行 4 個訪問。在這種情況下，在我使用 Web 應用程式撰寫電子表格的基準測驗中，已經發現可以使用 4 個作業人員。參考根據這個結果，我建議使用 Web 應用程式作為實作目標的解決方法。

用法：

1. 為 Web 應用準備腳本。

作為示例，請將以下腳本復制并粘貼到新的 Google Apps 腳本專案中。此腳本用作 Web 應用程式。在這種情況下，您也可以將此腳本放在 4 個腳本之一中。但作為示例，我分離了 4 個客戶端和一個 Web 應用服務器。

請設定您的目標電子表格 ID。

function doGet(e) {
  const lock = LockService.getDocumentLock();
  if (lock.tryLock(350000)) {
    try {
      var ssId = e.parameter.spreadsheetId;
      if (!ssId) return ContentService.createTextOutput("No spreadsheet ID.");
      var first_sheet = SpreadsheetApp.openById(ssId);
      var first_sheet_page = first_sheet.getSheetByName('Sheet86');
      var second_sheet = SpreadsheetApp.openById('SAME DESTINATION AS FOUR SCRIPTS'); // <--- Please set your destination Spreadsheet ID.
      var second_sheet_page = second_sheet.getSheetByName('Sheet86');
      var r = 1;
      while (second_sheet_page.getRange(r, 1).getValue()) {
        r  ;
      }
      second_sheet_page.getRange(r, 1).setValue(first_sheet_page.getRange(1, 1).getValue());
    } catch (e) {
      return ContentService.createTextOutput(e.message);
    } finally {
      lock.releaseLock();
      return ContentService.createTextOutput("Done");
    }
  } else {
    return ContentService.createTextOutput("Timeout");
  }
}

2. 部署 Web 應用程式。

詳細資訊可以看官方檔案。

在腳本編輯器上，在腳本編輯器的右上角，請點擊“點擊部署”->“新建部署”。
請點擊“選擇型別”->“Web App”。
請在“部署配置”下的欄位中輸入有關 Web App 的資訊。
請為“執行為”選擇“我” 。
- This is the importance of this workaround.
Please select "Anyone" for "Who has access".
- In your situation, I thought that this setting might be suitable.
- Of course, you can use the access token. If you want to use the access token, please set it as Anyone with Google account and use the access token at the client side.
Please click "Deploy" button.
Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec.
- When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
- You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

3. Prepare script of 4 clients.

`Spreadsheet1`

Please copy and paste the following script to the script editor of Spreadsheet 1. And, please reinstall the trigger. Because the scope is authorized.

function Spreadsheet1() {
  const srcSpreadsheetId = 'SPREADSHEET 1'; // Please set spreadsheet ID.
  const webAppsUrl = "https://script.google.com/macros/s/###/exec"; // Please set your Web Apps URL.

  const res = UrlFetchApp.fetch(webAppsUrl   "?spreadsheetId="   srcSpreadsheetId);
  console.log(res.getContentText());
}

`Spreadsheet2`

Please copy and paste the following script to the script editor of Spreadsheet 2. And, please reinstall the trigger. Because the scope is authorized.

function Spreadsheet2() {
  const srcSpreadsheetId = 'SPREADSHEET 2'; // Please set spreadsheet ID.
  const webAppsUrl = "https://script.google.com/macros/s/###/exec"; // Please set your Web Apps URL.

  const res = UrlFetchApp.fetch(webAppsUrl   "?spreadsheetId="   srcSpreadsheetId);
  console.log(res.getContentText());
}

`Spreadsheet3`

Please copy and paste the following script to the script editor of Spreadsheet 3. And, please reinstall the trigger. Because the scope is authorized.

function Spreadsheet3() {
  const srcSpreadsheetId = 'SPREADSHEET 3'; // Please set spreadsheet ID.
  const webAppsUrl = "https://script.google.com/macros/s/###/exec"; // Please set your Web Apps URL.

  const res = UrlFetchApp.fetch(webAppsUrl   "?spreadsheetId="   srcSpreadsheetId);
  console.log(res.getContentText());
}

`Spreadsheet4`

Please copy and paste the following script to the script editor of Spreadsheet 4. And, please reinstall the trigger. Because the scope is authorized.

function Spreadsheet4() {
  const srcSpreadsheetId = 'SPREADSHEET 4'; // Please set spreadsheet ID.
  const webAppsUrl = "https://script.google.com/macros/s/###/exec"; // Please set your Web Apps URL.

  const res = UrlFetchApp.fetch(webAppsUrl   "?spreadsheetId="   srcSpreadsheetId);
  console.log(res.getContentText());
}

4. Testing.

After the script of Web Apps and the scripts of 4 clients were prepared, please run those functions of clients. By this, the script of Web Apps can be run with LockService. In this case, your 4 clients can be run simultaneously.

Note:

In this case, the order of the functions Spreadsheet1 to Spreadsheet4 cannot be controlled. Please be careful about this.
When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

References:

Benchmark: Concurrent Writing to Google Spreadsheet using Form
Web Apps
Taking advantage of Web Apps with Google Apps Script

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/441619.html

標籤：google-apps-script

上一篇：我在Apps腳本中的代碼執行時間過長

下一篇：將公式從源表復制到目標表（資料表到用戶表單以進行編輯）