This is a sample script for retrieving the start and end row numbers of the same values in a column on Google Spreadsheet using Google Apps Script.
There is a case in that I want to retrieve the rows of the same values in a column on Google Spreadsheet using Google Apps Script. In this post, I would like to introduce a simple sample script for achieving this.
Sample script
function myFunction() {
const sheet = SpreadsheetApp.getActiveSheet();
const res = [
...sheet
.getRange("A1:A" + sheet.getLastRow())
.getDisplayValues()
.reduce(
(m, [a], i) => m.set(a, m.has(a) ? [...m.get(a), i + 1] : [i + 1]),
new Map()
)
.values(),
].map((e) => ({ start: e[0], end: e[e.length - 1] }));
console.log(res);
}
-
When this script is for the above sample Spreadsheet, the following result is obtained.
[ { "start": 1, "end": 2 }, { "start": 3, "end": 5 }, { "start": 6, "end": 7 }, { "start": 8, "end": 8 }, { "start": 9, "end": 10 }, { "start": 11, "end": 11 } ] -
As a sample application using this script, when this script is used as follows, the top sample situation can be obtained.
function myFunction() { const sheet = SpreadsheetApp.getActiveSheet(); const res = [ ...sheet .getRange("A1:A" + sheet.getLastRow()) .getDisplayValues() .reduce( (m, [a], i) => m.set(a, m.has(a) ? [...m.get(a), i + 1] : [i + 1]), new Map() ) .values(), ].map((e) => ({ start: e[0], end: e[e.length - 1] })); // Below script set the cell colors. res.forEach(({ start, end }, i) => sheet .getRange(start, 1, end - start + 1) .setBackground(i % 2 ? "#b6d7a8" : "#a4c2f4") ); }