0
421
ViewsGoogle Sheets- How to Clear, Zero Out, or Replace Specific Cells Efficiently Via Script
This is three similar actions needed in one sheet. Hopefully this ends up being a useful question in general.
I have a script that copies the values of arbitrary cells on a sheet to a single row in another sheet, courtesy of doubleunary's answer to a prior question. Now I need to know how to reset the input sheet to be ready for another entry.
Some cells need to be blank, some need to be zeroes, and some need to be reset to a default text value, which is stored on another sheet so it can be changed easily if needed.
The script for pulling the data is this:
function submitDataOhSoMuchFaster() {
const ss = SpreadsheetApp.getActive();
const values = ss.getActiveSheet().getRange('A1:Z13').getValues();
const entry = [
{ column: 26, row: 12 }, // Z12
{ column: 3, row: 2 }, // C2
{ column: 14, row: 2 }, // N2
{ column: 3, row: 10 }, // C10
{ column: 6, row: 2 }, // F2
{ column: 3, row: 4 }, // C4
{ column: 5, row: 4 }, // E4
{ column: 3, row: 5 }, // C5
{ column: 5, row: 5 }, // E5
{ column: 12, row: 5 }, // L5
{ column: 12, row: 4 }, // L4
{ column: 26, row: 13 }, // Z13
{ column: 20, row: 4 }, // T4
{ column: 20, row: 5 }, // T5
{ column: 3, row: 6 }, // C6
];
ss.getSheetByName('Master Log')
.appendRow(entry.map(cell => values[cell.row - 1][cell.column - 1]));
};I want to know if there is a way to elegantly reverse that, and either clear or write zeroes to a set of cells or ranges using a similar method, and how I would efficiently go about writing the values of a small array on a source sheet to arbitrary cells on the target sheet.
As examples, I need to:
Clear C4, E4, J10:J14, etc.
Zero T5:T6, D12:D15
Copy the contents of cell AT2 on one sheet to F2 on the target sheet, AT3 to C10, etc. (all the source data for this is in a single column, so an array seems workable)
I know how to do this in a lengthy manner that uses a ton of Clear, getValue and setValue commands, but how do I do it efficiently?
Edit:
The source range for the third step is here:
And the targets are here:
1 answers
Answer question0
Solution:
The most elegant way I can think of is using RangeList:
- RangeList.setValue sets the value of all cells in all specified ranges at once.
- RangeList.getRanges gets an array with the ranges, and you can then set the new values in a loop.
Code snippet:
function updateSheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const targetSheet = ss.getSheetByName("TARGET");
targetSheet.getRangeList(["C4","E4","J10:J14"]).setValue("");
targetSheet.getRangeList(["T5:T6", "D12:D15"]).setValue(0);
const sourceSheet = ss.getSheetByName("SOURCE");
const values = sourceSheet.getRange("AT2:AT" + sourceSheet.getLastRow())
.getValues().flat();
targetSheet.getRangeList(["F2","C10",...]).getRanges()
.forEach((range,i) => range.setValue(values[i]));
}