Method to speed up a VLOOKUP on two CSV files created from spreadsheet data-CodePudding

First I need to VLOOKUP twice in a CSV to find the name of the two teams, then I join the two names and do another VLOOKUP in another CSV:

function duoble_vlookup() {
  var s = SpreadsheetApp.getActive();
  var search_values = s.getRange("Import_Data!K2:K3").getValues();

  var teams_url = 'url csv 1';
  var teams_csv = Utilities.parseCsv(UrlFetchApp.fetch(teams_url));
  
  var match_col = teams_csv.map(row => row[0]);
  var match_list = match_col.join("ღ").split("ღ");

  var index = match_list.indexOf(search_values[0][0]);
  if (index === -1) {
    var team_home = 'off';
  } else {
    var row = index;
    var found_value = teams_csv[row][1];
    var team_home = found_value;
  }
  
  var index = match_list.indexOf(search_values[1][0]);
  if (index === -1) {
    var team_away = 'off';
  } else {
    var row = index;
    var found_value = teams_csv[row][1];
    var team_away = found_value;
  }
  
  var match_name = team_home   ' v '   team_away;
  
  var all_values_csv_url = 'url csv 2';
  var all_values_csv = Utilities.parseCsv(UrlFetchApp.fetch(all_values_csv_url));
  var match_col = all_values_csv.map(row => row[2]);
  var match_list = match_col.join("ღ").split("ღ");
  var index = match_list.indexOf(match_name);
  if (index === -1) {
    s.getRange("Gerais!B420:F420").setValues([['-','-','-','-','-']]);
  } else {
    var row = index;
    var found_value = [[all_values_csv[row][5],all_values_csv[row][6],all_values_csv[row][7],all_values_csv[row][8],all_values_csv[row][9]]];
    s.getRange("Gerais!B420:F420").setValues(found_value);
  }
}

This work takes between 28 to 45 seconds to be done, is there any method more agile than it for this or is this really the reality of this case?

For example to tests:

Import_Data!K2:K3:

/95/
/117/

Result in Gerais!B420:F420:

B420	C420	D420	E420	F420
2	4.5	3.45	1.95	2.02

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

Modification points:

When the ternary operator instead of the if statement is used, the process cost can be reduced a little. Ref
As I mentioned in my comment, in your script, var match_col = teams_csv.map(row => row[0]); and var match_list = match_col.join("ღ").split("ღ"); are the same result.
I thought that when UrlFetchApp.fetchAll insted of UrlFetchApp.fetch is used, the process cost can be reduced a little. Ref

When these points are reflected to your script, it becomes as follows.

Modified script:

function duoble_vlookup() {
  var s = SpreadsheetApp.getActive();
  var search_values = s.getRange("Import_Data!K2:K3").getValues();

  var teams_url = 'url csv 1';
  var all_values_csv_url = 'url csv 2';
  var [teams_csv, all_values_csv] = UrlFetchApp.fetchAll([teams_url, all_values_csv_url]).map(r => Utilities.parseCsv(r.getContentText()));

  var match_col1 = teams_csv.map(row => row[0]);
  var index1 = match_col1.indexOf(search_values[0][0]);
  var index2 = match_col1.indexOf(search_values[1][0]);
  var team_home = index1 === -1 ? 'off' : teams_csv[index1][1];
  var team_away = index2 === -1 ? 'off' : teams_csv[index2][1];
  var match_name = team_home   ' v '   team_away;

  var match_col2 = all_values_csv.map(row => row[2]);
  var index = match_col2.indexOf(match_name);
  var values = index === -1 ? [['-', '-', '-', '-', '-']] : [[all_values_csv[index][5], all_values_csv[index][6], all_values_csv[index][7], all_values_csv[index][8], all_values_csv[index][9]]];
  s.getRange("Gerais!B420:F420").setValues(values);
}

Note:

I'm not sure about your actual CSV data. So, when this modification was not useful, I thought that it might be required to test the script using a sample CSV data.

Modification points:

Modified script:

Note:

References: