I have 4 different scripts linked to 4 different accounts, which do their functions and in the end send a value from a specific cell to a final spreadsheet and in that final spreadsheet , to the same column of data:
Script Spreadsheet 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());
}
Script Spreadsheet 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());
}
Script Spreadsheet 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());
}
Script Spreadsheet 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 would work if it was the same script with multiple users trying to use it at the same time.
But in my case, there are four scripts with auto trigger (every 5 minutes) running and sending to the same column of the same spreadsheet.
Is there any way to be able to avoid having the risk of them meeting and putting values on the same lines?
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.
CodePudding user response：
From your updated question, in your situation, how about using Web Apps with LockService? From your question, I confirmed that 4 accesses are run simultaneously. In this case, in my benchmark for writing a Spreadsheet using Web Apps, it has already been found that 4 workers can be used. Ref From this result, I proposed to use Web Apps as a workaround for achieving your goal.
Usage:
1. Prepare script for Web Apps.
As a sample, please copy and paste the following script to a new Google Apps Script project. This script is used as Web Apps. In this case, you can also put this script in one of 4 scripts. But as a sample, I separated 4 clients and a server of Web Apps.
Please set your destination Spreadsheet 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. Deploy Web Apps.
The detailed information can be seen at the official document.
- On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
- Please click "Select type" -> "Web App".
- Please input the information about the Web App in the fields under "Deployment configuration".
- Please select "Me" for "Execute as".
- 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 accountand 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
Spreadsheet1toSpreadsheet4cannot 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".