Google Apps Script performing Index & Match function between two separate Google Sheets

Question

I am using Google Apps Script to perform a Index and Match function between two separate Google Sheets and retrieve the matching records and populate the designated sheet and column. Although it works fairly well, I would like to get the script to run more efficiently and as such use less time. Could Someone please look at the code example below and provide me with advice/pointers to do so

function SiteIDLookup() {
var search_spreadsheet = SpreadsheetApp.openById("GoogleSheetId");
SpreadsheetApp.setActiveSpreadsheet(search_spreadsheet);
var find_spreadsheet = SpreadsheetApp.openById("GoogleSheetId");
SpreadsheetApp.setActiveSpreadsheet(find_spreadsheet);
var ssheet = search_spreadsheet.getSheetByName("schoolSiteID_Lookup");
var fsheet = find_spreadsheet.getSheetByName("Info_Formatted");
var FMaxR = fsheet.getMaxRows();
fsheet.getRange(2, 16, FMaxR, 1).clear({contentsOnly: true});
var findData = fsheet.getDataRange().getValues();
var searchData = ssheet.getDataRange().getValues();

for(var i=0; i < findData.length; i++) {
 for(var j=0; j < searchData.length; j++) {
  var find = findData[i][14];
  var searchref = searchData[j][0];

  if(find == searchref && find != "" ) {
   var found = ssheet.getRange(j+1,2,1,1).getDisplayValue();
   fsheet.getRange(i+1,16,1,1).setValue(found);
   }
  }
 }
}

How about editing data in array? At "for" loops, there is a setValue. It retrieves data of "fsheet" using getValues, edits the data array at "for" loops. After it exits from "for" loop, it overwrites the edited data array to "fsheet" using "setValues". I think that it leads to reduction of usage count of API and speed up. I'm sorry for my poor English skill. — Tanaike

Vytautas Vytautas · Accepted Answer · 2017-02-01T05:43:33

The comment on your question is pretty much correct. You are doing unnecessary actions like

SpreadsheetApp.setActiveSpreadsheet(search_spreadsheet);
SpreadsheetApp.setActiveSpreadsheet(find_spreadsheet);

which server no purpose. Then inside of the loops you perform an unnecessary action

var found = ssheet.getRange(j+1,2,1,1).getDisplayValue();

considering you already did var searchData = ssheet.getDataRange().getValues(); that command line is redundant and is performed at each iteration to boot. You have 2 data sets in arrays, you need to work with arrays and use .setValues(array) to output the data. Something along the lines of this (you would have to check the indexing etc, since I don't have the actual sheets to test this on)

function SiteIDLookup() {
//  --------------------------------------------------------------------
//  This bit is purely cosmetic, I personaly hate declaring variables
//  inside of loops or middle of the code
  var i, j
  var find
  var searchref
  var found = []
//  --------------------------------------------------------------------

  var search_spreadsheet = SpreadsheetApp.openById("GoogleSheetId");  
  var find_spreadsheet = SpreadsheetApp.openById("GoogleSheetId");

  var ssheet = search_spreadsheet.getSheetByName("schoolSiteID_Lookup");
  var fsheet = find_spreadsheet.getSheetByName("Info_Formatted");
  var FMaxR = fsheet.getMaxRows();

  fsheet.getRange(2, 16, FMaxR, 1).clear({contentsOnly: true});

  var findData = fsheet.getDataRange().getValues();
  var searchData = ssheet.getDataRange().getValues();

  for (i = 0; i < findData.length; i++) {
    for (j = 0; j < searchData.length; j++) {

      find = findData[i][14];
      searchref = searchData[j][0];

      if (find == searchref && find != "" ) {
        found[i] = searchData[j][1]
      }
      else {
        found[i] = ['']
      }
      //        found = ssheet.getRange(j+1,2,1,1).getDisplayValue();
      //        fsheet.getRange(i+1,16,1,1).setValue(found);
    }
  }

  fsheet.getRange(2, 16, found.length, 1).setValues(found)
}

Google Apps Script performing Index & Match function between two separate Google Sheets

1 Answers