英文:
Add item to a new sheet using Ui menu and trigger onChange() is not working in google apps script
问题
Case:
我有一个情况,使用 Ui 菜单(onOpen())在用户点击它时向新工作表添加数据。之后,我想将数据从新工作表移动到另一个电子表格,我正在使用 onChange() 触发器来移动数据。触发器将在新工作表中插入新数据时起作用,但不会读取发生在新工作表上的任何更改。
The Code:
我正在使用 onChange(e) 来将新插入的数据从新工作表移动到另一个电子表格。我尝试检查并获取仅活动工作表和活动列,但 onChange(e) 触发器不起作用,无法检测到通过 Ui 菜单添加到新工作表的新数据。
function onOpen() {
var ui = SpreadsheetApp.getUi();
var menu = ui.createMenu("Move")
.addItem("Move to new sheet", "MovingData")
.addToUi();
}
function onChange(e) {
var ActiveSheet = e.source.getActiveSheet().getName();
var ActiveColumn = e.source.getActiveCell().getColumn();
console.log(ActiveSheet);
console.log(ActiveColumn);
if (ActiveSheet == "New Sheet" && ActiveColumn == 1) {
//Moving data to another spreadsheet
}
}
function MovingData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Source');
var data = sheet.getDataRange().getValues();
var sheet_new = ss.getSheetByName("New Sheet");
var arr = [];
for (var i = 0; i < data.length; i++) {
arr.push(data[i]);
sheet.getRange(i + 1, 5).setValue("Done");
}
if (arr != "") {
var newsheet_lr = sheet_new.getLastRow();
sheet_new.getRange(newsheet_lr + 1, 1, arr.length, arr[0].length).setValues(arr);
}
}
The flow of the process:
流程的步骤如下图所示:
英文:
Case:
I have a case using Ui Menu (onOpen()) to add data to a new sheet when the user clicks it. After that, I want to move the data from the new sheet to another spreadsheet and I'm using onChange() trigger to move the data. The trigger will work when new data is inserted on the New Sheet, but it's not working by not reading any changes that happen on the new sheet.
The Code:
I'm using the onChange(e) to move the newly inserted data on the New Sheet into another spreadsheet. I try to check and get only the active sheet and active column but the onChange(e) trigger is not working to detect when new data is added by Ui Menu to the New Sheet.
function onOpen() {
var ui = SpreadsheetApp.getUi();
var menu = ui.createMenu("Move")
.addItem("Move to new sheet", "MovingData")
.addToUi();
}
function onChange(e) {
var ActiveSheet = e.source.getActiveSheet().getName();
var ActiveColumn = e.source.getActiveCell().getColumn();
console.log(ActiveSheet);
console.log(ActiveColumn);
if (ActiveSheet == "New Sheet" && ActiveColumn == 1) {
//Moving data to another spreadsheet
}
}
function MovingData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Source');
var data = sheet.getDataRange().getValues();
var sheet_new = ss.getSheetByName("New Sheet");
var arr = [];
for (var i = 0; i < data.length; i++) {
arr.push(data[i]);
sheet.getRange(i + 1, 5).setValue("Done")
}
if (arr != "") {
var newsheet_lr = sheet_new.getLastRow();
sheet_new.getRange(newsheet_lr + 1, 1, arr.length, arr[0].length).setValues(arr);
}
}
答案1
得分: 3
模式 1:
在这个模式下,将 onChange
函数的脚本从 MovingData
中调用。
function MovingData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Source');
var data = sheet.getDataRange().getValues();
var sheet_new = ss.getSheetByName("New Sheet");
var arr = [];
for (var i = 0; i < data.length; i++) {
arr.push(data[i]);
sheet.getRange(i + 1, 5).setValue("Done")
}
if (arr != "") {
var newsheet_lr = sheet_new.getLastRow();
sheet_new.getRange(newsheet_lr + 1, 1, arr.length, arr[0].length).setValues(arr);
}
// 在这里,复制并粘贴“Moving data to another spreadsheet”的脚本到“onChange”函数中。
}
模式 2:
在这个模式下,在 MovingData
函数中将 Spreadsheet 服务更改为 Sheets API。当 Sheets API 编辑单元格时,将触发 OnChange 触发器。因此,使用此模式时,请启用 Sheets API 和高级 Google 服务。
function MovingData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Source');
var data = sheet.getDataRange().getValues();
var sheet_new = ss.getSheetByName("New Sheet");
if (data.length > 0) {
data.forEach(r => r[4] = "Done");
Sheets.Spreadsheets.Values.append({ values: data }, ss.getId(), "New Sheet", { valueInputOption: "USER_ENTERED" });
}
}
英文:
From but the onChange(e) trigger is not working to detect when new data is added by Ui Menu to the New Sheet.
, if you want to execute onChange
when MovingData()
is run, how about the following modification?
Pattern 1:
In this pattern, the script of onChange
is called from MovingData
.
function MovingData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Source');
var data = sheet.getDataRange().getValues();
var sheet_new = ss.getSheetByName("New Sheet");
var arr = [];
for (var i = 0; i < data.length; i++) {
arr.push(data[i]);
sheet.getRange(i + 1, 5).setValue("Done")
}
if (arr != "") {
var newsheet_lr = sheet_new.getLastRow();
sheet_new.getRange(newsheet_lr + 1, 1, arr.length, arr[0].length).setValues(arr);
}
// Here, copy and paste the script of "//Moving data to another spreadsheet" in "onChange" function.
}
Pattern 2:
In this pattern, the Spreadsheet service is changed to Sheets API in "MovingData" function. When the cell is edited by Sheets API, the OnChange trigger can be fired. So, when you use this, please enable Sheets API ad Advanced Google services.
function MovingData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Source');
var data = sheet.getDataRange().getValues();
var sheet_new = ss.getSheetByName("New Sheet");
if (data.length > 0) {
data.forEach(r => r[4] = "Done");
Sheets.Spreadsheets.Values.append({ values: data }, ss.getId(), "New Sheet", { valueInputOption: "USER_ENTERED" });
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论