Chapter 3 Case study: tidying the monthly sales sheet ・ MISSION 11
Mission 11: Write the result column at once (setValues)
Task
Write "Large" in the category of each entry whose amount is 100,000 or more. Instead of writing row by row, read the column at once and write it at once.
For practice, the table goes back to its starting state every time before the code runs (a real spreadsheet doesn't go back. Script changes can sometimes be undone with Ctrl+Z, but not all at once).
File ＋
Code.gs
Chapter 3 ・ MISSION 11
Write the result column at once (setValues)
Write "Large" in the category of each entry whose amount is 100,000 or more. Instead of writing row by row, read the column at once and write it at once.
Reading the code
- sheet.getRange(2, 4, lastRow - 1, 1).getValues();
- Reads lastRow - 1 rows and 1 column (the amount column) at once, starting at row 2, column 4.
- amounts.map((row) => [row[0] >= 100000 ? 'Large' : '']);
- Builds an array that holds "Large" for rows of 100,000 or more and empty otherwise. condition ? A : B is a way to write if on one line.
- sheet.getRange(2, 5, labels.length, 1).setValues(labels);
- Writes labels to column E at once, as many rows as labels has.
Read at once with getValues, convert row by row with map, and write at once with setValues. It is faster than reading and writing cells one at a time, so even thousands of rows don't keep you waiting. If the range and the array have different numbers of rows, you get the error "The number of rows in the data does not match …".
How to get started with GAS and common code are covered in an article.What is GAS? Getting started and common code
The code you write runs in an isolated place inside this page. It can't reach outside the page or other sites.
* This is a simulation of how Google Sheets and Apps Script behave and may differ in some ways from the real thing.
* Google Sheets is a trademark of Google LLC. This site is not affiliated with Google.