The main idea is not to need looping to generate a VLOOKUP because it generates a huge slowdown when the amount of data is very large.

To VLOOKUP on data directly in the sheet I do as follows:

function myFunction() {
  var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();     
  var searchValue = s.getRange("Test!A1").getValue();
  var data = SpreadsheetApp.openById("XXXXXXXXXXXX").getSheetByName("Test 2");
  var dataValues = data.getRange("A1:A").getValues();
  var dataList = dataValues.join("ღ").split("ღ");
  var index = dataList.indexOf(searchValue);
  
  if (index === -1) {
    s.getRange("Test!B1").setValue('off');
  } else {
    var row = index   1;
    var foundValue = data.getRange("D" row).getValue();
    s.getRange("Test!B1").setValue(foundValue);
  }
}

But there is a big problem in this method, because when many different accounts try to access this sheet at the same time, the error type error: could not connect sheet xxxxx appears or causes huge delay sometimes.

So what was the solution I found? Publish spreadsheet pages as CSV so they can be used and this error doesn't happen when many accounts call the same spreadsheet.

Currently, as I haven't found a way to use indexOf using the first column when I import the CSV with several columns of data, I had to create a spreadsheet page only with the copy data of column A, and then I got to the final result of VLOOKUP like this:

(the value in var searchValue in this example case will be two)

function myFunction() {
  var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var url_columnA = 'AAAAA';
  var url_vlookup = 'BBBBB';
  
  var dataSearch = Utilities.parseCsv(UrlFetchApp.fetch(url_columnA));
  var dataList = dataSearch.join("ღ").split("ღ");
  var searchValue = s.getRange("Test!A1").getValue();
  var index = dataList.indexOf(searchValue);

  if (index === -1) {
    s.getRange("Test!B1").setValue('off');
  } else {
    var row = index;
    var dataVlookup = Utilities.parseCsv(UrlFetchApp.fetch(url_vlookup));
    var foundValue = dataVlookup[row][3];
    s.getRange("Test!B1").setValue(foundValue);
  }
}

Return example:

other number

var url_vlookup:

var url_columnA:

Is there any way to handle var url_vlookup data for search the value in column A so that it's not necessary to use this page var url_columnA separated or is the only way to do it without looping?

CodePudding user response：

The first column can easily be separated after parsing using Array.map:

const dataVlookup = Utilities.parseCsv(UrlFetchApp.fetch(url_vlookup));
const url_columnA = dataVlookup.map(row => row[0])