如果沒有資料驗證單元格在正在寫入的作業表上引發錯誤，則此方法非常有效。但是，一旦腳本到達復制值不遵循資料驗證規則的單元格，它就會回傳錯誤并停止執行。如何忽略此錯誤，繼續寫入單元格并繼續運行腳本？
function addNew() {
Week = "2022-01-03"
x = 4
y = 17
for (var i=0; i<21; i )
{
var readRange = SpreadsheetApp.getActive().getRange(`Versionality!I${x}:K${y}`)
var readValues = readRange.getValues();
var writeRange = SpreadsheetApp.openById("------");
var writeValues = writeRange.getRange(`${Week}!H${x}:J${y}`)
writeValues.setValues(readValues);
x = 17
y = 17
}
}
uj5u.com熱心網友回復：
以下是如何“禁用/啟用”資料驗證以復制資料的示例：
示例代碼：
function myFunction(){
// IMPORTANT: Make sure source and target dimensions are the same.
var sourceRange = SpreadsheetApp.getActive().getRange("source!B11:D11"); //Get source range of cells to be copied
var sourceRangeDVs = sourceRange.getDataValidations(); // cache the data validations currently applied to the sourceRange
sourceRange.clearDataValidations(); //clear validations on the source range before getting source values
var sourceValues = rangeSource.getValues(); //getting source values
//Next, get target range where the data will be set.
//Note: if you have to open a new file and you are handling several ranges to be copied in a loop,
// you may want to cache the target file on a variable to avoid opening every iteration of the loop
var targetRange = SpreadsheetApp.getActive().getRange("target!E6:G6"); //Get target range of cells for the values to be set
targetRange.setValues(sourceValues); //Set values copied from source in the target range
sourceRange.setDataValidations(sourceRangeDVs); //Set Data Validations cached back to source range
}
請注意，上述示例代碼中設定的電子表格名稱和范圍僅為示例，請相應修改。
uj5u.com熱心網友回復：
讓我提出這個建議。您正在執行很多可能導致性能問題的 getValues()/setValues()。我喜歡做的是只使用一個 getValues() 然后我們可以從整個陣列中提取我們需要的內容并根據需要撰寫我們的部分。
此外，您還通過 id 反復打開同一個電子表格。
我沒有測驗它，因為您的日期結構很難設定。但我很有信心它會奏效。
function addNew() {
let Week = "2022-01-03";
let x = 3; // array index is 1 less than row
let y = 17;
let spread = SpreadsheetApp.getActiveSpreadsheet();
let readSheet = spread.getSheetByName("Versionality");
let readValues = readSheet.getDataRange().getValues();
let writeSheet = SpreadsheetApp.openById("------").getSheetByName(Week);
for (let i=0; i<21; i ) {
let subArray = [];
let j=x;
while( j < y ) {
subArray.push(readValues[j].slice(7,10)); // columns I to K
j ;
}
writeSheet.getRange(x 1,8,14,3).setValues(subArray); // columns H to J
x = 17;
y = 17;
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/521182.html
上一篇：根據條件將值放入列中的資料驗證