I'm trying to create a custom formula that can effectively vlookup the value in X cell against a column in another google sheets spreadsheet and return a value in the next column, the same row as the match. If I were querying in sheets directly I would simply use =IMPORTRANGE but I am not sure how to emulate this in apps script?

I have found this article which shows how to perform the lookup using apps script in the current sheet but can't find out how to perform against another sheet.

Any help/advice gratefully received.

  • any reason why not do it with formulas? – user0 Aug 29 '19 at 17:16
  • 1
    I want to be able to wrap the function to make it less techical for myself and colleagues, e.g. get X from Y (e.g. look up internal SKU number from barcode number) – Lawrence C Aug 29 '19 at 20:10
  • 1
    The custom function requires permission to access the "other" spreadsheet. As @Rubén notes 'Custom functions in Google Sheets have limitations like they can't be used to call Google Apps Script services that require permissions. The workaround is to use another means to call the function'. So... what is your preference? – Tedinoz Sep 4 '19 at 7:19