CODEWITHSUNDEEP
javascriptfunctiongoogle-apps-scriptglobal-variables
David Tonkin
David Tonkin

Reputation: 85

Logical paradox - custom function - parameters are coming from another fucntion

I hope I can make this makes sense....

I have gone through a bunch of other functions to end up with a variable that contains the data selected by a user from a drop down list on a sidebar to my google sheet.

The final function that that variable is sent to is like this:

function processFormResponse(formObject ){

  reportMonth = formObject.chosenReport;

  //result of reportMonth is a string. Example "Report_Nov 2017"
}

So now I have a variable called reportMonth that has what I need. But now, I want to write a vlookup that will select the same range every time but on a different sheet depending on the user selection (reportMonth). From what I have researched, I cant use variable inside the vlookup, so I was going to write a custom function that does the lookup for me.

But here's my paradox: I cant use processFormResponse as my custom function because it has the formObject already inside its parameters because its already receiving information from the previous function and I cant do a separate function because I can't seem to get reportMonth to be global... (I with and without var)

I basically something like this:

=IFERROR(VLOOKUP($E3, reportMonth !$F:$J,2,false),"")

Upvotes: 0

Views: 86

Answers (1)

Stack Tracer
Stack Tracer

Reputation: 1028

As mentioned in the Google Apps script documentation:

For the Range object:

setValue(value)

Sets the value of the range. The value can be numeric, string, boolean or date. If it begins with '=' it is interpreted as a formula.

So, if you have a 1-cell Range (a.k.a. a block of cells), then you could run

range.setValue("=IFERROR(VLOOKUP($E3,"+reportMonth+"!$F:$J,2,false),"")");

For Reference: https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluevalue

Upvotes: 1

Related Questions