Looking Up Values in Spreadsheet

[Kelly Konechny]

Hi there - 

We have a Google Sheet that has values that we would like to look up using an AdWords Script. The lookup would be similar to a vlookup function in Excel, we would provide a variable in the script that would lookup against the first column in the spreadsheet and return a value from a column that we choose based on the variable match from the row.

I'm not sure how to execute a lookup in a spreadsheet using an AdWords script though.

Thanks in advance for the help!

Kelly

[Jaren Callo (AdWords Scripts Team)]

Hi Kelly,

The solution would be loop through the rows until you find the row. But this is N^2 which isn't ideal for large data. You can use VLOOKUP by using the script below:

function main(){
  var ss_url = 'SPREADSHEET_URL';
  var ss = SpreadsheetApp.openByUrl(ss_url);
  var sheet = ss.getActiveSheet();
 
  var tableRows = ['=VLOOKUP('+SEARCH_KEY+','+RANGE+','+INDEX+', '+IS_SORTED+')'];
  sheet.appendRow(tableRows);
 
  Logger.log(sheet.getRange(sheet.getLastRow(), 1).getDisplayValue());
  //sheet.deleteRow(sheet.getLastRow()); // delete the VLOOKUP function
 
}

Thanks,

Jaren P. Callo

AdWords Scripts Team