使用Spreadsheet Service [1]的方法并将onEdit触发器安装到每个客户端Spreadsheets [2],我可以通过以下代码实现所需的过程:
function myFunction(e) {
//Get edited client row and Sheet
var editedCell = e.range;
var editedRow = editedCell.getRow();
var clientSheet = editedCell.getSheet();
//Number of headers on task sheet
var noColumnHeaders = clientSheet.getRange("A4").getDataRegion(SpreadsheetApp.Dimension.COLUMNS).getValues()[0].length;
//Entire row based on number of headers
var completeRow = clientSheet.getRange(editedRow,1,noColumnHeaders).getValues();
//Check if the row is completely filled
var isFilled = completeRow[0].indexOf("");
if(isFilled == -1) {
//Get master Spreadsheet
var masterSpreadsheet = SpreadsheetApp.openById("[MASTER-SPREADSHEET-ID]");
//Get master task sheet and set the values from client
var masterSheet = masterSpreadsheet.getSheetByName(clientSheet.getSheetName() + " master");
masterSheet.getRange(masterSheet.getLastRow()+1,noColumnHeaders).setValues(completeRow);
}
}
function createSpreadsheetEditTrigger() {
var clientSpreadsheet = SpreadsheetApp.openById("[CLIENT-SPREADSHEET-ID]");
ScriptApp.newTrigger('myFunction')
.forSpreadsheet(clientSpreadsheet)
.onEdit()
.create();
}
每次要添加客户端电子表格(首先替换给定电子表格ID)时,都需要运行createSpreadsheetEditTrigger函数。另外,替换myFunction上的主电子表格ID。
[1] https://developers.google.com/apps-script/reference/spreadsheet
[2] https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually
本文链接:https://www.f2er.com/3157123.html