我正在嘗試將資料從源表復制到我的目標表。有沒有可以縮短此代碼的版本？似乎 .getRange 只能接受一個字串。
當我嘗試這行代碼const sRange = sSheetDay.getRange('I4', '15', 'I6');等時，它總是說找不到范圍。我知道我的提問不是很好，尤其是我真的是這方面的初學者。希望有人有同樣的經歷。
我的主要目標是從特定單元格復制資料。而且我似乎找不到解決方案。
function copyDataFilters() {
const dTable = SpreadsheetApp.getActiveSpreadsheet();
const sSheetDay = dTable.getSheetByName('Day 1');
const sRange = sSheetDay.getRange('I4');
const sRange2 = sSheetDay.getRange('I5');
const sRange3 = sSheetDay.getRange('I6');
const sRange4 = sSheetDay.getRange('I7');
const sRange5 = sSheetDay.getRange('I8');
const sRange6 = sSheetDay.getRange('I9');
const sRange7 = sSheetDay.getRange('I12');
const sRange8 = sSheetDay.getRange('I13');
const sRange9 = sSheetDay.getRange('I16');
const sRange10 = sSheetDay.getRange('I17');
const sRange11 = sSheetDay.getRange('I20');
const sRange12 = sSheetDay.getRange('I21');
const sRange13 = sSheetDay.getRange('I24');
const sRange14 = sSheetDay.getRange('I25');
const sRange15 = sSheetDay.getRange('I27');
const sValue = sRange.getValues();
const sValue2 = sRange2.getValues();
const sValue3 = sRange3.getValues();
const sValue4 = sRange4.getValues();
const sValue5 = sRange5.getValues();
const sValue6 = sRange6.getValues();
const sValue7 = sRange7.getValues();
const sValue8 = sRange8.getValues();
const sValue9 = sRange9.getValues();
const sValue10 = sRange10.getValues();
const sValue11 = sRange11.getValues();
const sValue12 = sRange12.getValues();
const sValue13 = sRange13.getValues();
const sValue14 = sRange14.getValues();
const sValue15 = sRange15.getValues();
const targetSheet = dTable.getSheetByName('All filters report');
const targetRange = targetSheet.getRange('B4:C4');
const targetRange2 = targetSheet.getRange('B5:C5');
const targetRange3 = targetSheet.getRange('B6:C6');
const targetRange4 = targetSheet.getRange('B7:C7');
const targetRange5 = targetSheet.getRange('B8:C8');
const targetRange6 = targetSheet.getRange('B9:C9');
const targetRange7 = targetSheet.getRange('B11:C11');
const targetRange8 = targetSheet.getRange('B12:C12');
const targetRange9 = targetSheet.getRange('B14:C14');
const targetRange10 = targetSheet.getRange('B15:C15');
const targetRange11 = targetSheet.getRange('B17:C17');
const targetRange12 = targetSheet.getRange('B18:C18');
const targetRange13 = targetSheet.getRange('B20:C20');
const targetRange14 = targetSheet.getRange('B21:C21');
const targetRange315 = targetSheet.getRange('B24:C24');
targetRange.setValue(sValue);
targetRange2.setValue(sValue2);
targetRange3.setValue(sValue3);
targetRange4.setValue(sValue4);
targetRange5.setValue(sValue5);
targetRange6.setValue(sValue6);
targetRange7.setValue(sValue7);
targetRange8.setValue(sValue8);
targetRange9.setValue(sValue9);
targetRange10.setValue(sValue10);
targetRange11.setValue(sValue11);
targetRange12.setValue(sValue12);
targetRange13.setValue(sValue13);
targetRange14.setValue(sValue14);
targetRange315.setValue(sValue15);
}
uj5u.com熱心網友回復：
使用參考文獻中的 getRangeList
// Get a list of ranges A1:D4, F1:H4.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var rangeList = sheet.getRangeList(['A1:D4', 'F1:H4']);
參考
- https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#getrangelista1notations
- https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet?hl=en#getrangelista1notations
uj5u.com熱心網友回復：
function elfunko() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet0');
//you can do this with values
const [v4,v5,v6,v7,v8,v9,,,v12,v13,,,v16,v17,v18,v19,v20,v21,,,v24,v25,v26,v27] = sh.getRange("I4:I27").getValues();
Logger.log(v4);
//unfortunatey you cannot do this with ranges. It resullts in an error
const [I4,I5,I6,I7,I8,I9,,,I12,I13,,,I16,I17,I18,I19,I20,I21,,,I24,I25,I26,I27] = sh.getRange("I4:I27");
Logger.log(I4.getValue());
}
所以被困在使用范圍串列處理多個范圍，如魯本的回答所示
uj5u.com熱心網友回復：
在當前階段，當試圖從分散的單元格中獲取和放入不同的值時，電子表格服務（SpreadsheetApp）沒有直接實作這一目標的方法。因此，為了直接實作這一目標，作為當前的解決方法，可以使用 Sheets API。當這通過包含腳本的目標而反映在示例腳本中時，它變為如下。
示例腳本：
在使用此腳本之前，請在 Advanced Google services 中啟用 Sheets API。
function copyDataFilters2() {
const srcSheet = "Day 1";
const dstSheet = "All filters report";
const src = ["I4", "I5", "I6", "I7", "I8", "I9", "I12", "I13", "I16", "I17", "I20", "I21", "I24", "I25", "I27"].map(e => `'${srcSheet}'!${e}`);
const dst = ["B4:C4", "B5:C5", "B6:C6", "B7:C7", "B8:C8", "B9:C9", "B11:C11", "B12:C12", "B14:C14", "B15:C15", "B17:C17", "B18:C18", "B20:C20", "B21:C21", "B24:C24"].map(e => `'${dstSheet}'!${e}`);
const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
const values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: src }).valueRanges.map(({ values }) => values ? [Array(2).fill(values[0][0])] : [[""]]);
const data = values.map((e, i) => ({ values: e, range: dst[i] }));
Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, ssId);
}
- 運行此腳本時，將從
"I4", "I5", "I6", "I7", "I8", "I9", "I12", "I13", "I16", "I17", "I20", "I21", "I24", "I25", "I27"“第 1 天”作業表的單元格中檢索值，并將檢索到的值放入"B4:C4", "B5:C5", "B6:C6", "B7:C7", "B8:C8", "B9:C9", "B11:C11", "B12:C12", "B14:C14", "B15:C15", "B17:C17", "B18:C18", "B20:C20", "B21:C21", "B24:C24".
參考：
- 地圖（）
- 方法：電子表格.values.batchGet
- 方法：電子表格.values.batchUpdate
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/446576.html
上一篇：檢查另一列子字串上的一列字串
下一篇：兩列中的條件格式