Search code examples
google-sheetsgoogle-sheets-formula

how to get live query other spreadsheet with fastest way?

The job process is like below:

Use column A as a condition to query the spreadsheet DATA, and return the column * of the spreadsheet DATA.

But now my spreadsheet is facing the delay issue and lag problem , i have confuse how to settle it.

if is any wrong please forgive me

please check out the example sheets any suggestion is welcome,

and thanks at all

Solution

  • I believe your goal is as follows.

    • You want to reduce the process cost for retrieving your goal.

    In your situation, how about using Google Apps Script as a direction? I thought that when Google Apps Script is used, the process cost might be able to be reduced. When Google Apps Script is used for your situation, it becomes as follows.

    Sample script:

    Please copy and paste the following script to the script editor of Google Spreadsheet and save the script. And, when you use this script using your provided Spreadsheet, please put a custom function of =SAMPLE('INPUT COL B'!B2:B,'DATA'!W2:AF) to a cell. By this, the result is returned.

    function SAMPLE(srcValues, dataValues) {
  const obj = dataValues.reduce((o, [w, ...v]) => {
    const last = v.pop();
    if (v.join("") != "") {
      v.forEach(c => {
        if (!o[c]) o[c] = [w, last];
      });
    }
    return o;
  }, {});
  return srcValues.map(([b]) => obj[b] || [null, null]);
}

    Testing:

    When this script is for your provided Spreadsheet, the following result is obtained.

    enter image description here

    Note:

    • When the data becomes larger, the custom function might not be able to be used. At that time, please run the script by the script editor, custom menu, a button on Spreadsheet, and so on. The script is as follows. In this case, please copy and paste the following script to the script editor of Spreadsheet and save the script. And please run the function with the script editor. By this, in this script, the result value is put to the column "E" of "INPUT COL B" sheet.

        function myFunction() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const [srcSheet, dataSheet] = ["INPUT COL B", "DATA"].map(s => ss.getSheetByName(s));
    const srcValues = srcSheet.getRange("B2:B" + srcSheet.getLastRow()).getValues();
    const dataValues = dataSheet.getRange("W2:AF" + dataSheet.getLastRow()).getValues();
    const obj = dataValues.reduce((o, [w, ...v]) => {
      const last = v.pop();
      if (v.join("") != "") {
        v.forEach(c => {
          if (!o[c]) o[c] = [w, last];
        });
      }
      return o;
    }, {});
    const res = srcValues.map(([b]) => obj[b] || [null, null]);
    srcSheet.getRange(2, 5, res.length, res[0].length).setValues(res);
  }

    Reference: