Get a value and row number in selected range with gaps between cells?

user_15743317 0
Viewed 367

Example of Sellected Range

I need to get values and row number in Google sheet script out of sellected range.

Original problem was that I was trying to get values and row numbers of sellected range using this script:

sheet = ss.getActiveSheet(),
  sheetvalues = sheet.getActiveRange().getValues();
  for (i=0; i<sheetvalues.length; i++) {
  mr = sheet.getActiveRange().getRow()+i;
  }

But I found out that if you use filer and select a range then all the hidden cells will also be part of the activerange, but I need only those that are inside of a filtered range. I decided that the best way is to select cells separately one by one.By I can't get a value and row index cause it counts as active range only the last selected cell.

google-apps-script google-sheets javascript
1 Answers

I believe your goal as follows.

  • Under the selected rows for the sheet, you want to retrieve the row numbers of no selected rows using Google Apps Script.

In this case, I think that the method of getSelection can be used. And, in order to confirm the rows, getActiveRangeList is used. When this is reflected to a sample script, it becomes as follows.

Sample script:

When you use this script, at first, please select the cells like your sample image and run the script.

function myFunction() {
  // 1. Retrieve the selected ranges.
  const selection = SpreadsheetApp.getSelection();

  // 2. Retrieve the selected row numbers.
  const selectedRows = selection.getActiveRangeList().getRanges().flatMap(r => {
    const row = r.getRow();
    const temp = [];
    for (let i = 0; i < r.getNumRows(); i++) {
      temp.push(row + i);
    }
    return temp;
  }).sort((a, b) => a - b);

  // 3. From data range, retrieve the row numbers except for the selected row numbers.
  const sheet = selection.getActiveSheet();
  const range = sheet.getDataRange();
  const rowNumbers = [];
  for (let r = 1; r <= range.getNumRows(); r++) {
    if (!selectedRows.includes(r)) rowNumbers.push(r);
  }
  console.log(rowNumbers); // Here, you can see the retrieved row numbers at the log.

  // 4. Retrieve the row values of the retrieved row numbers.
  // If you want to retrieve the row values of "rowNumbers", you can also the following script.
  const allValues = range.getValues();
  const values = rowNumbers.map(n => allValues[n]);
  console.log(values); // Here, you can see the values of row numbers at the log.
}

  • As the important point for using the selected range, for example, when the cells of row 3, row 1, row 2 are selected in order, the range list returns the order of cells. So in this sample script, the retrieved row numbers are sorted.

  • When above script is run, for example, when the data range is "A1:C5" and when the cells "A1", "A3" and "A5" are selected, the row numbers of 2, 4 are retrieved.

References:

user_71086530
Related