Search code examples
javascriptfunctiongoogle-apps-scriptglobal-variables

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),"")
Solution

  • 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