How do I get the values of cells in same row by using 1 input?

Question

Currently my function looks like this:

function myFunction(value1, value2, value3, value4, value5, value6)

Basically value1 is always within column A, but shifts between rows. value2-6 always going be 1 cell to the right of each other. I.e., value1=A1, which means value2=B1, value3=B3 etc. OR value3=A5, value2=B5 etc.

I basically just want my input to be the 1st column and my program knows the read the values of 2-6, like this:

myFunction(value1)

How can I achieve that?

FatFingersJackson · Answer 1 · 2017-10-18T19:36:00.163

The following should be quite clear and efficient. At least it is working. Note that especially with a simple pattern like this (all the cells in a row ) you should first define the range, then pick the values at once in an array. Picking the values one cell at a time is noticeably slower.

function myFunction( value1 )
{
  var cell1 = SpreadsheetApp.getActiveSheet().getRange(value1);

// myFunction("A1") is the same as following:
//cell1 = SpreadsheetApp.getActiveSheet().getRange("A1");

// Define the value here, if it is fixed. 
//Otherwise in the function parameters
var columnCount = 6;

// Define the range to get the values from. Parameters: 
// Starting row of the range : row of Cell1
// Starting column of the range : column of cell1 
// Number of rows = 1 
// And the number of columns we wanted
var values = SpreadsheetApp.getActiveSheet()
                         .getRange( cell1.getRow() , cell1.getColumn() , 1 , columnCount)
                         .getValues();

//  var values == [[ value, value, value, value, value, value ]]
//  so actually values[0] is what you requested, the values from A1 to F1

// Now if you want to, for example sum up the values
// You just iterate through the values[0]

var sum = Number(0);
for ( var i = 0; i < values[0].length; i++ )
{
 var cellValue = values[0][i]; 

 sum += Number(cellValue);
}

return sum; 
}

Remember that when you call the function in the Spreadsheet cell, you cannot add the parameter by just clicking a cell, as it would result as this: =myFunction(A1) - That way A1 adds uses the VALUE of cell A1 as parameter.

You need to add quotation marks when calling the function, like this: =myFunction("A1") -That way you use the cell as parameter.

The error I get is TypeError: Cannot call method "getRow" of undefined. (line 15, file "test") — Daniel Van Wetering, Oct 16 '17 at 19:41
So sorry, should not change diapers when posting, I erased one line from the code. OK, i corrected the code. Note that I changed the parameter name to value1 and added the first row. Should work now. — FatFingersJackson, Oct 16 '17 at 19:42
I'm now getting; Error Range not found (line 3). It looks like variables can't be read as cells? — Daniel Van Wetering, Oct 17 '17 at 00:10
Looks like it wont work https://stackoverflow.com/questions/12371434/how-do-i-pass-a-cell-argument-from-a-spreadsheet-to-a-custum-function-as-a-range — Daniel Van Wetering, Oct 17 '17 at 01:13
How did you call it ? If you call it from the sheet, you cannot assign the cell by just clicking it, you need to add quotation marks like this -> =myFunction("A1") — FatFingersJackson, Oct 17 '17 at 01:44
I edited the post just in case to explain the correct way to call the function. Variable can be read as cell, or as cell value, it depends on the quotation marks. — FatFingersJackson, Oct 18 '17 at 19:37

score 0 · Answer 2 · answered Oct 17 '17 at 00:05

I am guessing that value1 is not a cell range, as google built-in Spreadsheet Service already provides getRange(row, column) with getRange().getA1Notation() and others in the Range class.

Sounds like you want the script to search column A for argument value1 and return the found row data?

function getValueRow (value1) {
  var sh = SpreadsheetApp.getActive().getSheets()[1];
  // Get each cell value in column "A" and flatten for indexOf() use
  var bound_col = sh.getRange(1, 1, sh.getLastRow()).getValues().join().split(",");
  // Compare value1 to column "A" data to find its row
  // `indexOf()` returns the index of the first match
  var argument_row = bound_col.indexOf(value1);

  if (argument_row != -1) { 
    // Get the row data of value1 from column "A" to column "F"
    var row_data = sh.getRange((argument_row + 1), 1, 1, 6).getValues();

    Logger.log("%s is in row %s", value1, (argument_row + 1));
    Logger.log("The row_data is: %s", row_data);

  } else {
    Logger.log("Can not find %s in column A", value1);
  }
}

Looks like it wont work https://stackoverflow.com/questions/12371434/how-do-i-pass-a-cell-argument-from-a-spreadsheet-to-a-custum-function-as-a-range — Daniel Van Wetering, Oct 17 '17 at 01:13
Would be helpful to add to your question that you are using it for a `Custom function` and not as a script app. They are different things. Where are you calling the `myFunction(value1...)` from? More detail + code from your script would also be helpful — random-parts, Oct 17 '17 at 01:36
I think I am lost, it is okay. I am just going to have to call like 12 arguments eventually haha — Daniel Van Wetering, Oct 17 '17 at 01:47

How do I get the values of cells in same row by using 1 input?

2 Answers2