Google apps script too slow

Question

So my script takes 5 minutes to run. It is copying and pasting a file into a folder, then replaces text at the words with {{}}.

function b(row,sheet){
  return sheet.getRange(row,sheet.getMaxColumns()).getValue()
}
function makedoc(){
  let forma = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("forma");
  let datab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("baza");
  let rows = forma.getRange(1,2,forma.getMaxRows()).getValues();
  let googleDocTemplate = DriveApp.getFileById("notrevealing");
  let destinationFolder = DriveApp.getFolderById("not revealing");
  let copy = googleDocTemplate.makeCopy(rows[1]+" "+rows[2]+" File", destinationFolder);
  let doc = DocumentApp.openById(copy.getId());
  let body = doc.getBody();
  body.replaceText("{{date1}}", Utilities.formatDate(new Date(), "GMT+2", "dd.MM/yyyy"));
  body.replaceText("{{date2}}", Utilities.formatDate(new Date(), "GMT+2", "dd.MM.yyyy"));
  body.replaceText("{{date3}}", Utilities.formatDate(new Date(), "GMT+2", " dd.MM "));
  body.replaceText("{{name}}",b(2,forma));
  body.replaceText("{{name2}}",b(3,forma));
  body.replaceText("{{name3}}",b(4,forma));
  body.replaceText("{{firma}}",b(25,forma));
  body.replaceText("{{price}}",b(23,forma));
  body.replaceText("{{price2}}",word(b(23,forma)));
  body.replaceText("{{price3}}",String(b(23,forma)+" dl."));
  body.replaceText("{{price4}}",String(word(b(23,forma))+" dl."));
  body.replaceText("{{a2}}",b(9,forma));
  body.replaceText("{{a3}}",b(10,forma));
  body.replaceText("{{a4}}",b(11,forma));
  body.replaceText("{{a5}}",b(12,forma));
  body.replaceText("{{a6}}",b(13,forma));
  body.replaceText("{{a7}}",b(14,forma));
  body.replaceText("{{a8}}",b(15,forma));
  body.replaceText("{{a9}}",b(15,forma));
  body.replaceText("{{a10}}",b(16,forma));
  body.replaceText("{{a11}}",b(17,forma));
  body.replaceText("{{a12}}",b(18,forma)); 
  body.replaceText("{{egn}}",b(7,forma));
  body.replaceText("{{number}}",b(5,forma));
  body.replaceText("{{address}}",b(6,forma));
  body.replaceText("discount",b(24,forma));
  body.replaceText("pay",b(23,forma)-b(24,forma));
  body.replaceText("discount2",word(b(24,forma)));
  body.replaceText("pay2",word(b(23,forma)-b(24,forma)));
}

If you ask about why I do every a thing myself, I tried using for but it didn't work.

Duplicate of https://stackoverflow.com/questions/35289183/long-processing-time-likely-due-to-getvalue-and-cell-inserts — TheMaster, Dec 09 '21 at 19:25

score 1 · Answer 1 · answered Dec 09 '21 at 22:31

There are still issues because word is undefined but this would run a lot faster because it gets all of the replacement data at one time.

function makedoc() {
  const c = getMaxColumns();
  const ss = SpreadsheetApp.getActive();
  let fsh = ss.getSheetByName("forma");
  let dsh = ss.getSheetByName("baza");
  let fvs = fsh.getRange(2, 2, fsh.getMaxRows() - 1, fsh.getLastColumn()).getValues();
  let gtemp = DriveApp.getFileById("notrevealing");
  let dfldr = DriveApp.getFolderById("not revealing");
  let copy = gtemp.makeCopy(fvs[1] + " " + fvs[2] + " File", dfldr);
  let doc = DocumentApp.openById(copy.getId());
  let body = doc.getBody();
  body.replaceText("{{date1}}", Utilities.formatDate(new Date(), "GMT+2", "dd.MM/yyyy"));
  body.replaceText("{{date2}}", Utilities.formatDate(new Date(), "GMT+2", "dd.MM.yyyy"));
  body.replaceText("{{date3}}", Utilities.formatDate(new Date(), "GMT+2", " dd.MM "));
  body.replaceText("{{name}}",fvs[2-2][c-1]);
  body.replaceText("{{name2}}",fvs[3-2][c-1]);
  body.replaceText("{{name3}}",fvs[4-2][c-1]);
  body.replaceText("{{firma}}",fvs[25-2][c-1]);
  body.replaceText("{{price}}",fvs[23-2][c-1]);
  body.replaceText("{{price2}}",word(fvs[23-2][c-1]));
  body.replaceText("{{price3}}",String(fvs[23-2][c-1])+" dl.");
  body.replaceText("{{price4}}",String(word(fvs[23-2][c-1]))+" dl.");
  body.replaceText("{{a2}}",fvs[9-2][c-1]);
  body.replaceText("{{a3}}",fvs[10-2][c-1]);
  body.replaceText("{{a4}}",fvs[11-2][c-1]);
  body.replaceText("{{a5}}",fvs[12-2][c-1]);
  body.replaceText("{{a6}}",fvs[13-2][c-1]);
  body.replaceText("{{a7}}",fvs[14-2][c-1]);
  body.replaceText("{{a8}}",fvs[15-2][c-1]);
  body.replaceText("{{a9}}",fvs[15-2][c-1]);
  body.replaceText("{{a10}}",fvs[16-2][c-1]);
  body.replaceText("{{a11}}",fvs[17-2][c-1]);
  body.replaceText("{{a12}}",fvs[18-2][c-1]); 
  body.replaceText("{{egn}}",fvs[7-2][c-1]);
  body.replaceText("{{number}}",fvs[5-2][c-1]);
  body.replaceText("{{address}}",fvs[6-2][c-1]);
  body.replaceText("discount",fvs[24-2][c-1]);
  body.replaceText("pay",fvs[23-2][c-1]-fvs[24-2][c-1]);
  body.replaceText("discount2",word(fvs[24-2][c-1]));
  body.replaceText("pay2",word(fvs[23-2][c-1]-fvs[24-2][c-1]));
}

It most likely still needs tweak because I did all of the replacements with regex but without data which I don't wish to produce it's difficult to debug any further and based upon your previous you probably don't know how to take it much further on your own. So please provide data.

Google apps script too slow

1 Answers1