CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
Warren Meroney
Warren Meroney

Reputation: 39

Function for an Extended Vlookup?

So I want to know if there is a robust way to using vlookup, query, or some Drive API to search through spreadsheets in a Drive folder (all of them named by just date) using a date cell on my sheet as a reference. Once it finds the correct spreadsheet, then I want it to perform a normal Vlookup ImportRange function using the same cell value. So I essentially want to vlookup or query or whatever twice in one function, one for the actual spreadsheet file, and another for the cell value in the sheet of the correspondent row of the referenced column of the vlookup.

Step 1: Drive> Folder > File Name (Date) = Sheet1!A2 (Date)

Step 2: File Name (Date) > Sheet > Column > Row = Sheet1!A2 (Date), 7

Sorry if this is poorly described.

Upvotes: 1

Views: 130

Answers (1)

ZektorH
ZektorH

Reputation: 2760

You can do this by creating a custom function with Apps Script that utilizes the DriveService to:

  1. Search for the file with the correct name
  2. Query that file for the desired values
    1. Opening the file with SpreadsheetApp
    2. Get the values you are looking for
    3. Do the processing you need on them
  3. Return the results of the query

Hope this helps!

Upvotes: 1

Related Questions