CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Gadget
Gadget

Reputation: 71

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

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);
   }
  }
 }
}

Upvotes: 2

Views: 15168

Answers (1)

Vytautas
Vytautas

Reputation: 2286

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

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

which serve 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)
}

Upvotes: 2

Related Questions