Add item to a new sheet using Ui menu and trigger onChange() is not working in google apps script

huangapple go评论77阅读模式
英文:

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:
流程的步骤如下图所示:
Add item to a new sheet using Ui menu and trigger onChange() is not working in google apps script

英文:

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(&quot;Move&quot;)
    .addItem(&quot;Move to new sheet&quot;, &quot;MovingData&quot;)
    .addToUi();
}

function onChange(e) {
  var ActiveSheet = e.source.getActiveSheet().getName();
  var ActiveColumn = e.source.getActiveCell().getColumn();
  console.log(ActiveSheet);
  console.log(ActiveColumn);
  if (ActiveSheet == &quot;New Sheet&quot; &amp;&amp; ActiveColumn == 1) {
    //Moving data to another spreadsheet
  }
}  

function MovingData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(&#39;Source&#39;);
  var data = sheet.getDataRange().getValues();

  var sheet_new = ss.getSheetByName(&quot;New Sheet&quot;);

  var arr = [];
  for (var i = 0; i &lt; data.length; i++) {
      arr.push(data[i]);
      sheet.getRange(i + 1, 5).setValue(&quot;Done&quot;)
    
  }
  if (arr != &quot;&quot;) {
    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:
Add item to a new sheet using Ui menu and trigger onChange() is not working in google apps script

答案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(&#39;Source&#39;);
  var data = sheet.getDataRange().getValues();

  var sheet_new = ss.getSheetByName(&quot;New Sheet&quot;);

  var arr = [];
  for (var i = 0; i &lt; data.length; i++) {
      arr.push(data[i]);
      sheet.getRange(i + 1, 5).setValue(&quot;Done&quot;)
    
  }
  if (arr != &quot;&quot;) {
    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 &quot;//Moving data to another spreadsheet&quot; in &quot;onChange&quot; 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(&#39;Source&#39;);
  var data = sheet.getDataRange().getValues();
  var sheet_new = ss.getSheetByName(&quot;New Sheet&quot;);
  if (data.length &gt; 0) {
	data.forEach(r =&gt; r[4] = &quot;Done&quot;);
    Sheets.Spreadsheets.Values.append({ values: data }, ss.getId(), &quot;New Sheet&quot;, { valueInputOption: &quot;USER_ENTERED&quot; });
  }
}

huangapple
  • 本文由 发表于 2023年7月13日 11:51:32
  • 转载请务必保留本文链接:https://go.coder-hub.com/76675787.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定