4

I need to get all values of a row of a spreadsheet based on the cell that I have selected. Example: I have selected the Cell F1. Now I want the all value in that row (for example F1, F2, F3, F4, F5).

What I tried:

  var selection = SpreadsheetApp.getActiveSheet().getSelection()

  var val1 = selection.getValue()
  var val2 = values.getNextDataRange(SpreadsheetApp.Direction.NEXT).getValue()
  var val3 = values.getNextDataRange(SpreadsheetApp.Direction.NEXT).getValue()
  ...

Then I thought I could selection the whole row in which I am interested so that I have a list of all values that I could get with xy[0], xy[1], xy[2]... but that was not succesful.

  var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data').getSelection().getActiveRange();
  
  var val1 = values[0]
  var val2 = values[1]
  ...

  var values = SpreadsheetApp.getActive().getActiveRange().getA1Notation();
  var val1 = values[0]
  var val2 = values[1]
  ...

My last idea was to get just the position of the selected cell, e.g. F1. Thus I could get all values directly:

  var value1 = SpreadsheetApp.getActiveSheet().getRange(5, 0).getValue();
  var value1 = SpreadsheetApp.getActiveSheet().getRange(5, 1).getValue();
  var value1 = SpreadsheetApp.getActiveSheet().getRange(5, 2).getValue();
  ...

Unfortunately nothing worked so far. Has someone a solution to my problem?

player0
  • 124,011
  • 12
  • 67
  • 124
Justus_505
  • 45
  • 1
  • 5

2 Answers2

3

Assuming that you actually want to retrieve the values in the selected row, and not column (so, if F1 is selected, you want to retrieve A1, B1, C1, etc., not F1, F2, F3, etc., you can just do this:

  • Get the row index of the selected cell, via getCurrentCell() and getRow().
  • Get the values corresponding to that row with getRange(row, column, numRows, numColumns) (specifying that the first row is the current row index, which was retrieved in previous step, and that the last column refers to the last column with content (getLastColumn()), and getValues() which returns a 2D array corresponding to the specified range. The first element of this 2D array is a simple array with all the values in the row.

Code sample:

function getRowValues() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rowIndex = sheet.getCurrentCell().getRow();
  var rowValues = sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn()).getValues()[0];
  return rowValues;
}
Iamblichus
  • 18,540
  • 2
  • 11
  • 27
0

Get an array of selected rows

function selectRowsBasedOnCriteria() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Your Sheet Name');//enter sheet name
  const sr=2;
  const sc=1;
  const rg=sh.getRange(sr,sc,sh.getLastRow()-sr+1,sh.getLastColumn()-sc+1);
  const vs=rg.getValues();
  let oA=[];
  vs.forEach(function(r,i){
    if(r[4]>10) {//selection criteria you chose whatever you want
      oA.push(r);//save selected row
    }
  });
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(oA.join('<br />')).setWidth(800), "Rows");//just a way of displaying the selected rows
  //const osh=ss.getSheetByName('output');//to display in another sheet
  //osh.getRange(1,1,oA.length,oA[0].length).setValues();//display in another sheet
  return oA;//this is an array of selected rows
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thank you very much, that helpS already as I get the values of a row in an array now! But I have a few questions though: I implemented your code to my scripteditor and I get a display of the selected values (Very nice!). I thought, as oA is now an array with the values, I could get the values by like: ``` var value1 = oA[1] var value2 = oA[2] ... ``` But this does not work. How can I get the single values? I have another problem. Is seems that it does not work when there is a column with no entry. In that case I do not get the display of the selected values.. Is there a trick? – Justus_505 Jul 02 '20 at 07:46
  • I guess Iamblichus answer is sufficient for me, but thanks anyway! – Justus_505 Jul 02 '20 at 08:03