The problem with saving a file through a loop in Apps Script

[ИВАН Иванов]

Hi ! I really need help. I made a script that inserts values from a table from a table into a Google document. A new document is created for each line. But for some reason, depending on the number of lines in the table, some contracts are saved as an empty template (at the same time, with a different number of lines, different contracts are saved correctly and incorrectly). Please help me, I have already tried all the options.

my code with comments:

function Creator() {
  // this is a template file
  const docFile = DriveApp.getFileById("1jsPQjkz4eXImbPMCQmO48LRg2gPiN6mIIx5A9nzLTOw");

 // this is the directory for new files
  const tempFolder = DriveApp.getFolderById("1wfcoEm1YbOgWV1ZNZUCM4PElc5DawzoK");

   
  var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
//we determine the number of rows
  var l=list.getLastRow();

 
  var a1 = list.getRange(2, 2).getValue()

//take the values of the columns from the table
 for (var i=2; i <= l; i++) {
   
    var a2 = list.getRange(i, 2).getValue();

    var a3 = list.getRange(i, 8).getValue();

    var a4 = list.getRange(i, 7).getValue();
   
    var a5 = list.getRange(i, 5).getValue();

    var a6 = list.getRange(i, 3).getValue();

    var a7 = "действующий(ая) на основании Уведомления о постановке на учет физического лица в налоговом органе"

    var a8 = list.getRange(i, 6).getValue();

// make a copy of the template

     const tempFile = docFile.makeCopy(a2, tempFolder);



// open the created copy

     const tempDocFile = DocumentApp.openById(tempFile.getId());

// copy file body:
     const body = tempDocFile.getBody();
 
   
   

//replacing values in the template:
   
    body.replaceText("{ФИО}", a2);
    body.replaceText("{ДАТА}", a3);
    body.replaceText("{РЕКВИЗИТЫ}", a4);
    body.replaceText("{КОНТАКТЫ}", a6);
    if(a5 == 'ИП'){
    body.replaceText("{ОБРАЩЕНИЕ}", "Индивидуальный предприниматель");
    body.replaceText("{ОСНОВАНИЯ}", a7);  
    body.replaceText("{ОГРН}", a8);
    body.replaceText("{ПОДПИСАНТ}", a2);
    }else{
      body.replaceText("{ОБРАЩЕНИЕ}", "Зарегистрированный(ая) как плательщик налога на профессиональный доход");
          body.replaceText("{ОСНОВАНИЯ}"," ");  
    body.replaceText("{ОГРН}", "");
    }

   
   tempDocFile.saveAndClose();
   
      }
     
 
 
 

}

[Clark Lind]

Hello, 
Depending on how many rows there are, you are making a call to the sheet to get data for every row, plus every cell. That is very inefficient and could cause problems if Google's system is busy, or someone else is working on the same Spreadsheet, etc etc. You can pull the whole data range into an array, and work with memory instead of making all the data calls to the sheet--so you only make a single call to all the data.  Something like this code will be much more efficient and much faster. 
Keep in mind, creating many new Documents can take a long time just by itself. But this should be 1/10th (or faster) the previous speed.

function Creator() {

  // this is a template file

  const docFile = DriveApp.getFileById("1jsPQjkz4eXImbPMCQmO48LRg2gPiN6mIIx5A9nzLTOw");

 // this is the directory for new files

  const tempFolder = DriveApp.getFolderById("1wfcoEm1YbOgWV1ZNZUCM4PElc5DawzoK");

  var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

//we determine the number of rows

  var lastRow = list.getLastRow();

  var data = list.getRange(2, 2, lastRow -1 , 8). getValues()  //ensure this range is correct. from second row, second column down to last row, 8th column

  var a1 = data[0][0]

//take the values of the columns from the table

 for (var i=0; i < data.length -2; i++) {   //may need to change '-2' to '-1' if last row is skipped

   

  //these variables are not needed, but you can keep them if it helps understand the function better

    //var a2 = list.getRange(i, 2).getValue();

    var a2 = data[i][2];  //or don't use at all (see below)

    //var a3 = list.getRange(i, 8).getValue();

    var a3 = data[i][8];  //or don't use at all (see below)

    //var a4 = list.getRange(i, 7).getValue();

    var a4 = data[i][7];  //or don't use at all (see below)

    //var a5 = list.getRange(i, 5).getValue();

    var a5 = data[i][5];  //or don't use at all (see below)

    //var a6 = list.getRange(i, 3).getValue();

    var a6 = data[i][3];  //or don't use at all (see below)

    //var a7 = "действующий(ая) на основании Уведомления о постановке на учет физического лица в налоговом органе"

    //var a8 = list.getRange(i, 6).getValue();

    var a8 = data[i][6];  //or don't use at all (see below)

// make a copy of the template

     const tempFile = docFile.makeCopy(a2, tempFolder);      //or  const tempFile = docFile.makeCopy(data[i][2], tempFolder);

// open the created copy

     const tempDocFile = DocumentApp.openById(tempFile.getId());

// copy file body:

     const body = tempDocFile.getBody();

 //replacing values in the template:

   

    body.replaceText("{ФИО}", a2);  // or  body.replaceText("{ФИО}", data[i][2]);

    body.replaceText("{ДАТА}", a3);  // or  body.replaceText("{ДАТА}", data[i][8]);

    body.replaceText("{РЕКВИЗИТЫ}", a4);  // or body.replaceText("{РЕКВИЗИТЫ}", data[i][7]);

    body.replaceText("{КОНТАКТЫ}", a6);  // or body.replaceText("{КОНТАКТЫ}", data[i][3]);

        if(a5 == 'ИП'){  //   etc etc   or  if(data[i][5] == 'ИП'){    

        body.replaceText("{ОБРАЩЕНИЕ}", "Индивидуальный предприниматель");

   //a7 can be deleted and replaced directly with the text:

        body.replaceText("{ОСНОВАНИЯ}", "действующий(ая) на основании Уведомления о постановке на учет физического лица в налоговом органе");  

        body.replaceText("{ОГРН}", a8);  // or body.replaceText("{ОГРН}", data[i][6]);

        body.replaceText("{ПОДПИСАНТ}", a2);  // or body.replaceText("{ПОДПИСАНТ}", data[i][2])

[ИВАН Иванов]

thank you very much friend! This code works. But there is the same problem - out of the total number of files, about half is saved as the original template without replacements. It looks like he doesn't have time to save the files

суббота, 23 апреля 2022 г. в 00:55:02 UTC+3, cwl...@gmail.com:

[Clark Lind]

You might have to run them in batches. About how many rows are successful before it stops working? 

[ИВАН Иванов]

I don't see a visible dependency. The script can make the first 3 documents successfully, then 2 unsuccessfully, again several successfully, and so on. Moreover, depending on the number of lines, these will be different documents

понедельник, 25 апреля 2022 г. в 01:05:03 UTC+3, cwl...@gmail.com:

[Matthew Moran]

I've created and saved many more documents without issue - using a similar methodology.

FYI: Also, definitely pull all the sheet data into an array - significantly more efficient.

I would set a dummy line of code just after your tempDocFile.saveAndClose() line.. 
let any=0; // just to stop code

Set a breakpoint there and check all variable values. Oh.. I would also return the new document id to a variable - just for the sake of being able to check that value separately as well.

const tempFile = docFile.makeCopy(a2, tempFolder);
// open the created copy

const tempId = tempFile.getId();
const tempDocFile = DocumentApp.openById( tempId);

It isn't really necessary but lends to readability and should you ever need that value for something else.

Anyway.. set a breakpoint at the dummy line after the saveAndClose() and then debug the app. Open each new document after save and close. Just note any discrepancies the values for the documents that are not created.

Another thing.. it seems you are indicating it is consistent in that it creates 3, doesn't create 2, etc.

Either remove any lines from your sheet for documents that are NOT created. Run the code again and see if that pattern remains true or whether it is successful. If documents that were successfully created previously, are not created, it is a timing issue. Add a Utilities.sleep() call after your saveAndClose() and see if it runs successfully.