我有一個谷歌表在這里。它有三個子表。機密：MIS、機密：MSA 和收集表。我需要一個選單??按鈕，它應該在兩個電子表格的第 4 行之后讀取所有資料。并將“收集表”作為單個串列粘貼到 E5 中的當前日期到最后填充的列。下面的代碼做得很好，但我認為這不是一個聰明的方法。有時如果行數很少，它會給出“行超出范圍”的訊息，有沒有專家給/建議我一些改進它的建議？
代碼如下。
function create_submit_sheet(){
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var target_sheet = SpreadsheetApp.setActiveSheet(sheet.getSheetByName('Collection Sheet'))
target_sheet.getRange('C1').setValue('');
var ss = SpreadsheetApp.getActiveSpreadsheet();
var source_sheet = ss.getSheetByName("CONFIDENTIAL : MIS");
var Avals = source_sheet.getRange("B4:B").getValues();
var Alast = Avals.filter(String).length;
//Logger.log(Alast);
var target_sheet = ss.getSheetByName("Collection Sheet");
//var roww = target_sheet.getDataRange.length;
//var Avals = ss.getRange("A1:A").getValues();
//var Alast = Avals.filter(String).length;
//Logger.log(Alast);
var source_range = source_sheet.getRange("B4:F" Alast 4);
//var target_range = target_sheet.getRange("A6:H");
var last_row = target_sheet.getLastRow();
//target_sheet.insertRowAfter(last_row);
var target_range = target_sheet.getRange("A" (last_row 1) ":C" (last_row 1));
source_range.copyTo(target_range,{contentsOnly: true});
var ss = SpreadsheetApp.getActiveSpreadsheet();
var source_sheet = ss.getSheetByName("CONFIDENTIAL : MSA");
var Avals = source_sheet.getRange("B5:B").getValues();
var Alast = Avals.filter(String).length;
var Alast1 = Alast;
//Logger.log(Alast);
var target_sheet = ss.getSheetByName("Collection Sheet");
//var roww = target_sheet.getDataRange.length;
//var Avals = ss.getRange("A1:A").getValues();
//var Alast = Avals.filter(String).length;
//Logger.log(Alast);
var source_range = source_sheet.getRange("B5:F" Alast 5);
//var target_range = target_sheet.getRange("A6:H");
var last_row = target_sheet.getLastRow();
//target_sheet.insertRowAfter(last_row);
var target_range = target_sheet.getRange("A" (last_row 1) ":C" (last_row 1));
source_range.copyTo(target_range,{contentsOnly: true});
//SpreadsheetApp.getActiveSheet().getRange('F2').setValue('Hello');
target_sheet.getRange('F4').setValue('???????');
target_sheet.getRange('G4').setValue('?????');
var curDate = Utilities.formatDate(new Date(), "GMT 6", "MM/dd/yyyy")
Avals = target_sheet.getRange("A1:A").getValues();
Alast = Avals.filter(String).length;
target_sheet.getRange(5,7, Alast-4 ).setValue(curDate);
var sheet = SpreadsheetApp.getActiveSpreadsheet();
SpreadsheetApp.setActiveSheet(sheet.getSheetByName('Collection Sheet'))
}
uj5u.com熱心網友回復：
試試appendRows_()實用函式。你可以像這樣使用它：
function appendToSubmitSheet() {
const ss = SpreadsheetApp.getActive();
const targetSheet = ss.getSheetByName('Collection Sheet');
const values =
ss.getRange('CONFIDENTIAL : MIS!B4:F').getValues()
.concat(ss.getRange('CONFIDENTIAL : MSA!B4:F').getValues())
.filter(row => row.join(''));
appendRows_(targetSheet, values, 2);
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/489492.html