Skip to content

Instantly share code, notes, and snippets.

@RomainVialard
Last active August 16, 2024 00:32
Show Gist options
  • Save RomainVialard/3396b629d823d6920631c2a84ab6d60b to your computer and use it in GitHub Desktop.
Save RomainVialard/3396b629d823d6920631c2a84ab6d60b to your computer and use it in GitHub Desktop.
Google Apps Script - Google Sheets - Use the getActiveRangeList() method to get indexes of selected rows
function myFunction() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
const selectedRows = getIndexesOfSelectedRows(sheet);
for (let i = 0; i < data.length; i++) {
// skip not selected rows
if (!selectedRows.includes(i + 1)) continue;
const rowData = data[i];
console.log(rowData);
// perform the relevant action: generate a document, send an email,...
}
}
/**
* @param {SpreadsheetApp.Sheet} dataSheet
*
* @returns {Array<number>} selectedRows an array of row indexes for all selected rows; row indexing starts with 1.
*/
function getIndexesOfSelectedRows(dataSheet) {
let selectedRowsIndexes = [];
// check current selection
// if specific row(s) are selected, allow user to perform a merge only on those rows
const rangeList = dataSheet.getActiveRangeList();
const ranges = rangeList.getRanges();
for (let i in ranges) {
const a1Notation = ranges[i].getA1Notation();
// if range notation doesn't contain ":", it's a single cell, skip it
if (!a1Notation.includes(":")) continue;
const tmp = a1Notation.split(":").map(Number);
// if range notation contains a letter, specific columns are selected, not a whole row, skip it
if (!Number.isInteger(tmp[0]) || !Number.isInteger(tmp[1])) continue;
if (tmp[0] == tmp[1]) {
selectedRowsIndexes.push(tmp[0]);
}
else {
for (let j = tmp[0]; j < tmp[1] + 1; j++) {
selectedRowsIndexes.push(j);
}
}
}
return selectedRowsIndexes;
}
/**
* similar behavior can be tested in Mergo (mail merge) & Publigo (document merge)
* https://workspace.google.com/marketplace/app/mergo_mail_merge/91302273949?pann=b
* https://workspace.google.com/marketplace/app/publigo_document_merge/677216675116?pann=b
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment