我有两个变量需要在几百页的电子表格中更新。但我只需要在每页的单元格B1中更改它,而不需要任何其他单元格。如果B1是苹果,我需要它说红苹果,如果B1是香蕉,我需要他说黄香蕉。
function run() {
runReplaceInSheet();
replaceInSheet();
}
function runReplaceInSheet() {
var spreadsheet = SpreadsheetApp.openById("ID");
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for ( var i = 0 ; i<sheets.length ; i++) {
var sheet = sheets[i];
// Fetch the range of cells
var dataRange = sheet.getRange(startRow, 1, numRows, 1) // Numbers of rows to process
// Fetch values for each row in the Range
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var values = sheet.getDataRange().getValues();
// Replace Names
replaceInSheet(values, 'Apple', 'Red Apple');
//write the updated values to the sheet, again less call;less overhead
sheet.getDataRange().setValues(values);
}
}
function replaceInSheet(values, to_replace, replace_with) {
//loop over the rows in the array
for (var row in values) {
//use Array.map to execute a replace call on each of the cells in the row.
var replaced_values = values[row].map(function(original_value) {
return original_value.toString().replace(to_replace, replace_with);
});
//replace the original row values with the replaced values
values[row] = replaced_values;
}
}
这是我尝试使用的更新的代码,它会使计时超时。
function run() {
runReplaceInSheet();
replaceInSheet();
}
function runReplaceInSheet() {
var spreadsheet = SpreadsheetApp.openById("ID");
var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
var startRow = 1; // First row of data to process
var numRows = 1; // number of rows to process
// Fetch the range of cells
var dataRange = sheet.getRange(startRow, 1, numRows, 1) // Numbers of rows to process
// Fetch values for each row in the Range
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var value = sheet.getRange('B1').getValue();
// Replace Names
replaceInSheet(values, 'Apple', 'Red Apple');
//write the updated values to the sheet, again less call;less overhead
sheet.getRange('B1').setValue(value);
}
}
function replaceInSheet(values, to_replace, replace_with) {
//loop over the rows in the array
for (var row in values) {
//use Array.map to execute a replace call on each of the cells in the row.
var replaced_values = values[row].map(function(original_value) {
return original_value.toString().replace(to_replace, replace_with);
});
//replace the original row values with the replaced values
values[row] = replaced_values;
}
}
对于电子表格中的每一张表,问题中的代码都会获取所有值,但您只需要替换单个单元格的值。
此外,替换函数在所有单元格中进行替换。
获取B1而不是的值
var values = sheet.getDataRange().getValues();
使用
var value = sheet.getRange('B1').getValue();
然后,您可以使用比较值来查看是否需要更换,如果需要,则不使用
sheet.getDataRange().setValues(values);
你可以使用
sheet.getRange('B1').setValue(value);