Google sheet : passing value/data from cell as parameter in google script function

0
votes

I add script using tools/script editor. I add my simple popup function :

function test(){
  Browser.msgBox('This is test')
}

Then in google sheet, i add an image as button and attach the script 'test'.

It works fine, but i want to pass value from a cell. How can i do it?

For example, i modify the function as follow :

function test(val){
  Browser.msgBox('This is test. Value is '+val)
}

if i want to pass value from cell B4 --> test(B4). How to do it properly ? i'm not allowed to include the parameter B4 when assigning script to image/button. It will say 'script could not be found'.

Thanks

2
google-sheets

2 Answers

1
votes

JohnA's solution is correct, but I want to add something to it. I recommend this enhancement to avoid modifying your script every now and then when you want pass another cell as a parameter.

Approach:

  • Store the cell notation you want to pass on a specific cell. (e.g. "B4" in A1)
  • Get the value of A1 and access the value of that cell notation.

Code:

function test() {
  sheet = SpreadsheetApp.getActiveSheet();
  // cell holds the range you want printed
  cell = sheet.getRange("A1").getValue();
  // value now holds the value of the range we got from the cell
  value = sheet.getRange(cell).getValue();
  Browser.msgBox(value);
}

Sheet:

sheet

B4:

b4

B5:

b5

Note:

  • Everytime you change the value of the A1, it will print that cell's value.
  • It will not behave properly when you write an invalid range in A1. (Exception: Range not found)
1
votes

Correct, you can specify a parameter if calling the custom function as a formula in the sheet, but not as attached to a script when a button is pressed. We do not even included the parentheses when we assign a script.

If the customer function always wants to use the value of B4, you can modify your script the get the value from that cell.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var val = sheet.getRange("B4");