CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsreturn-valuegoogle-apps-script-editorgetvalue
Justus_505
Justus_505

Reputation: 45

I want to get the values of a row of a spreadsheet based on the selected cell

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?

Upvotes: 4

Views: 5564

Answers (2)

Iamblichus
Iamblichus

Reputation: 19309

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;
}

Upvotes: 3

Cooper
Cooper

Reputation: 64040

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
}

Upvotes: 0

Related Questions