0

The function below gets the rows of data from a web app based form, finds the row in the spreadsheet based on 2 criteria and sets the values to the destination in that sheet. Even if the data coming in is intended to a row only, the function takes about 15 seconds to finish and that is because another function is called after the forEach() and setValue() part of the process. Shouldn't the values be written to the spreadsheet and not necessarily wait for the called function to finish, since that function does something independent?

function formToSheets(rows) {
  var lock = LockService.getScriptLock();
  try {
    lock.waitLock(10000);
  } catch (e) {
    Logger.log('Could not obtain lock after 10 seconds.');
  }
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const atSht = ss.getSheetByName('Approval Tracker');
  const atValuesRng = atSht.getDataRange();
  const atValues = atValuesRng.getValues();

  rows.forEach(function (item) {
    for (let a = atValues.length - 1; a >= 0; a--) {
      if (item[0] == atValues[a][9] && item[1] == atValues[a][11]) {
        let row = a + 1;
        atSht.getRange(row, 3).setValue(item[6]);
        atSht.getRange(row, 8).setValue(item[7]);
      }
    }
  });
  SpreadsheetApp.flush();
  const clientId = rows[0][0];
  //Updates the Client's Project Workbook
  updateClientFile(clientId, rows);
  lock.releaseLock();
}

This is the 2nd function:

var clientSeoProjectWbId = [];
function updateClientFile(clientId, rows) {
  searchaFolder_(DriveApp.getFolderById('ID'), clientId);

  let data = rows.map(e => [e[6], e[3], e[2], e[4] != '' ? e[4].split('"')[1] : '', e[5]]);
  const clientFileId = clientSeoProjectWbId[0];
  if (clientFileId == undefined) {
    const recipients = "email";
    MailApp.sendEmail(recipients,
      'Subject',
      'msg body');
    return;
  }
  const clientSeoProjectWb = SpreadsheetApp.openById(clientFileId);
  const atSht = clientSeoProjectWb.getSheetByName('Approval Tracker');
  atSht.getRange(atSht.getLastRow() + 1, 2, data.length, data[0].length).setValues(data);
}



//Searches for the client's workbook within all folders and subfolders in the parent folder whose ID is passed as a param
function searchaFolder_(_aFolder, clientId) {
  var clientIdSearchTerm = "title contains '" + clientId + "'";
  var clientFiles = _aFolder.searchFiles(clientIdSearchTerm);
  while (clientFiles.hasNext()) {
    var aFile = clientFiles.next();
    if (aFile.getName().indexOf('Workbook') > -1) {
      clientSeoProjectWbId.push(aFile.getId());
      if (aFile.getId() != '') break;
    }
  }
  //Iterates through the subfolders
  var someFolders = _aFolder.getFolders();
  while (someFolders.hasNext()) {
    var newFolder = someFolders.next();
    //launch function again with new folder
    searchaFolder_(newFolder, clientId);
  }
}

I've tried running it without calling the second function and it takes 03 seconds, which is acceptable.

Appreciate your thoughts/direction(s)!

onit
  • 2,275
  • 11
  • 25
  • 2
    Try using SpreadsheetApp.flush() when you want to be sure that the spreasheet has been updated. – Cooper May 29 '22 at 20:25
  • Tried it, @Cooper! Didn't change the timing. Thanks anyway – onit May 30 '22 at 00:11
  • 2
    Where exactly did you implement SpreadsheetApp.flush()? – ziganotschka May 30 '22 at 07:24
  • Hi, @ziganotschka! I did put it outside/after the ```rows.forEach()...``` piece, after the "loop" where values are set. – onit May 30 '22 at 11:36
  • 2
    You can try to flush after each `atSht.getRange(row, 8).setValue(item[7]);` iteration. Also, for debugging purposes, you can log the current timestamp both within each loop, after `forEach()` and at the start of your second function. E.g. `console.log('a: ' + a + ", timestamp: " + new Date())`. – ziganotschka May 30 '22 at 13:50
  • Thank you, @ziganotschka! I'll try that. I'm sure the second function takes long to execute, since it iterates through indefinite folders and its subfolders, but I was wondering why the first function awaits for it before it sets the values where needed. Thank you! – onit May 30 '22 at 18:55

0 Answers0