How to reduce Apps Script execution time to under 45 seconds for GWAO when already using arrays

Question

I can't figure out how to make this script run in under 45 seconds (the limit for Google Workspace Add-ons). The script takes content from different columns in a Google Docs table and ports it to columns F and H in a spreadsheet. The table and spreadsheet have ~700 rows. Any help is appreciated!

function docToSheet(event) {
  try {
    let ssInput = event.formInputs.url[0];
    let sheetName = event.formInputs.sheet_name[0];
    let sourceSs = SpreadsheetApp.openByUrl(ssInput);
    let sourceSheet = sourceSs.getSheetByName(sheetName);
    let range = sourceSheet.getDataRange();
    let length = range.getNumRows();
    let sourceColF = sourceSheet.getRange("F3:F" + length);
    let sourceColH = sourceSheet.getRange("H3:H" + length);


    let doc = DocumentApp.getActiveDocument();
    let body = doc.getBody();

    let searchElement = body.findElement(DocumentApp.ElementType.TABLE);
    let element = searchElement.getElement();
    let table = element.asTable();

// Creates array to set values with for Column F
    let array = [];
    for (let i = 0; i < table.getNumRows(); i++) {
      array[i] = [];
      let cellText = table.getCell(i, 3).getText();
      for (let j = 0; j < table.getNumRows(); j++) {
        array[i][0] = cellText;
      }
    }
    
    // Creates array to set values with for Column H

    let array2 = [];
    for (let i = 0; i < table.getNumRows(); i++) {
      array2[i] = [];
      let cellText = table.getCell(i, 5).getText();
      for (let j = 0; j < table.getNumRows(); j++) {
        array2[i][0] = cellText;
      }
    }

    sourceColF.setValues(array);
    sourceColH.setValues(array2);

    let card = docsSuccessCard();
    let navigation = CardService.newNavigation().pushCard(card);

    return CardService.newActionResponseBuilder()
      .setNavigation(navigation)
      .build();
  }
  catch {
    let card = docsErrorCard();
    let navigation = CardService.newNavigation().pushCard(card);
    return CardService.newActionResponseBuilder()
      .setNavigation(navigation)
      .build();
  }

}

Answer 1

In order to be able to reduce the execution time of a Google Apps Script function you should try to reduce the calls to Google Apps Script services.

Ideas

1. Instead of using multiple Sheet.getRange use Sheet.getRangeList
2. Instead of doing multiple Range.setValues try to do a single one
3. Whenever it be possible instead of reading some parameters use hardcoded values.

Or

• Instead of using the Spreadsheet Service use the Advanced Sheets Service
• Instead of using the Document Service use the Advanced Documents Service
• When writing to a spreadsheet / document use the corresponding batchUpdate method.

Consider to try a different approach

1. Instead of doing many things on a single function, split them into two or more functions.
2. Use your function to create one time trigger
3. Use a jobs queue and process them using a trigger
4. Instead of using a Workspace add-on, use an Editor add-on as it allows use client-side code for your add-on UI as it has execution time limit of 6 / 30 mins depending on the account type.