Why is my code timing out. It seems to simple

[Bruce Axtens]

The script creates lookup tables. 

```

function LutFromRangeToRangeMenuItem() {

  const workbook = SpreadsheetApp.getActiveSpreadsheet();

  const worksheets = workbook.getSheets();

  const worksheetArray = worksheets.filter(sheet => sheet.getName() === 'lutRanges');

  let worksheet;

  if (worksheetArray.length === 1) {

    worksheet = worksheetArray[0];

  }

  else {

    worksheet = workbook.insertSheet('lutRanges');

    worksheet.getRange("A1").setValue('Source').setFontWeight('bold');

    worksheet.getRange("B1").setValue('Target').setFontWeight('bold');

    Browser.msgBox(workbook.getName(), 'lutRanges sheet created', Browser.Buttons.OK);

    return;

  }

  const workrange = worksheet.getRange("A2:B");

  const rows = workrange.getDisplayValues().filter(row => row[0] !== "" && row[1] !== "");

  for (let R = 0; R < rows.length; R++) {

    const row = rows[R];

    /*DaisyChain.*/CreateLutFromRangeToRange(row[0], row[1]);

  }

  Browser.msgBox(workbook.getName(), 'lutRanges traversed', Browser.Buttons.OK);

}

/**

 * Build LUT from source to target

 * @param  {string} source

 * @param  {string} target

 * @returns void

 */

function CreateLutFromRangeToRange(source, target) {

  const workbook = SpreadsheetApp.getActiveSpreadsheet();

  const sourceParts = source.split("!");

  const workrange = workbook.getRange(source);

  const workvalues = workrange.getDisplayValues().filter(row => row.join("") !== "");

  const columnCount = workvalues[0].length;

  const workblend = workvalues.map(item => item.join("|"));

  const workfiltered = workblend.filter((item, indx) => {

    return workblend.indexOf(item) == indx;

  }).map(item => item.split("|"));

  const targetRange = workbook.getRange(target);

  const targetValues = targetRange.getDisplayValues().filter(row => row.join("") !== "");

  const targetblend = targetValues.map(item => item.join("|"));

  workfiltered.forEach(item => {

    if (!targetblend.includes(item.join("|"))) {

      targetValues.push(item);

    }

  });

  const sheetOfTarget = targetRange.getSheet().getSheetName();

  const destRange = workbook.getSheetByName(sheetOfTarget).getRange(targetRange.getRow(), targetRange.getColumn(), targetValues.length, columnCount);

  destRange.setValues(targetValues);

}

We start with a declaration of intent in one range in the `lutRanges` sheet

The `luts` sheet has

When the script runs it takes the source and target pairs and uses them as source and target for the lookup table generator. The first pass through work, albeit not speedily. The second time through fails with various errors, most common being

Is this just an occasional Google Sheets performance glitch that will resolve overnight or something more drastic?

-Bruce

[Keith Andersen]

Bruce,

Is it possible to share your sheet or a copy (better option) with any sensitive or proprietary information stripped? 

I will be able to take a look at it tomorrow afternoon. 

Not enough info to help as it stands.

It's 12:30am here, hence getting to it tomorrow afternoon.

Cheers

Keith 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/92c0ae25-0e1b-41be-b9de-8a6989612ed9n%40googlegroups.com.

[Bruce Axtens]

It would appear that the issue has to do with how much data is in the other sheets, how long it takes to recalc (and Google Sheets still persist in not allowing me to temporarily disable recalc), and maybe something to do with the sources ranges being derived from a complicated formula, viz 
```

=unique(ARRAYFORMULA(if(B4="NUMBER", (TO_PURE_NUMBER(iferror(if(B$3<>"", (substitute(INDIRECT(INDEX(SPLIT($A$1, "!"),1)&"!$"&REGEXEXTRACT(ADDRESS(INDEX(SPLIT($A$1, "$"),5), MATCH(B5, INDIRECT($A$1), 0)), "[A-Z]+")&INDEX(SPLIT($A$1, "$"),5)+1&":$"&REGEXEXTRACT(ADDRESS(INDEX(SPLIT($A$1, "$"),5), MATCH(B5, INDIRECT($A$1), 0)), "[A-Z]+")),iferror(left(B3, FIND("|",B3)-1),""),iferror(RIGHT(B3, FIND("|",B3)-1),""))), (INDIRECT(INDEX(SPLIT($A$1, "!"),1)&"!$"&REGEXEXTRACT(ADDRESS(INDEX(SPLIT($A$1, "$"),5), MATCH(B5, INDIRECT($A$1), 0)), "[A-Z]+")&INDEX(SPLIT($A$1, "$"),5)+1&":$"&REGEXEXTRACT(ADDRESS(INDEX(SPLIT($A$1, "$"),5), MATCH(B5, INDIRECT($A$1), 0)), "[A-Z]+")))) ,"")) ), (TO_TEXT(iferror(if(B$3<>"", (substitute(INDIRECT(INDEX(SPLIT($A$1, "!"),1)&"!$"&REGEXEXTRACT(ADDRESS(INDEX(SPLIT($A$1, "$"),5), MATCH(B5, INDIRECT($A$1), 0)), "[A-Z]+")&INDEX(SPLIT($A$1, "$"),5)+1&":$"&REGEXEXTRACT(ADDRESS(INDEX(SPLIT($A$1, "$"),5), MATCH(B5, INDIRECT($A$1), 0)), "[A-Z]+")),iferror(left(B3, FIND("|",B3)-1),""),iferror(RIGHT(B3, FIND("|",B3)-1),""))), (INDIRECT(INDEX(SPLIT($A$1, "!"),1)&"!$"&REGEXEXTRACT(ADDRESS(INDEX(SPLIT($A$1, "$"),5), MATCH(B5, INDIRECT($A$1), 0)), "[A-Z]+")&INDEX(SPLIT($A$1, "$"),5)+1&":$"&REGEXEXTRACT(ADDRESS(INDEX(SPLIT($A$1, "$"),5), MATCH(B5, INDIRECT($A$1), 0)), "[A-Z]+")))) ,"")) ))) )

```
If the source ranges are simple values, everything runs swiftly.

-Bruce

[Keith Andersen]

Well, to suspended the recalc you could, via a script, clear the complicated formula in the cell and replace it in the cell when needed. Hard code the formula into a variable and use a function to setFormula() when needed. Simply attach function to a button or special menu.