I am trying to generate reports from template. Here is my workflow:

1. Make a copy from my template sheet
2. Append data from other sheet
3. Set border (problem in this stage)
4. Convert this file as a PDF file and store in a constant as blob file
5. Convert this file as a Excel file and store in a constant as blob file
6. Send the blob constant in a new function to send the blob as mail
7. Delete the copied template

The problem is in step 3. It set the border but after the step 5. That means the blob file don't have the border but the copied template file has the border (I check before deleting the file). As a result, email attached file without border.

You can see my code here:

/**
 * To add grid to the excel file
 */
function setGridToFile(id) {
  var ss = SpreadsheetApp.openById(id).getSheetByName('Feuil');

  ss.getRange(4, 1, ss.getLastRow() - 3, ss.getLastColumn()).setBorder(true, true, true, true, true, true);

  return id;
}

// To create report of one product and return 2 file as blob
function createReportForEachItem() {
  var data = [['Thibaud', 'DU MERLE', 'Examen wset 2 à Paris', 17/12/2021, '09h00-10h00', 1, 36.0], ['Christine', 'Meyer-Wachsmuth', 'Examen wset 2 à Paris', 17/12/2021, '09h00-10h00', 1, 42.0]]
  // Script stop if there is no data in the summery sheet
  if(data.length === 0) { return 'No data in summery sheet!' }

  // Create a duplicate from template file
  var finalReport = DriveApp.getFileById('1Oi2mwvZlhCIDUWEGae0Z8QlF_adS8hTXQS69WrSPaj8').makeCopy('ParticipantsLists_', DriveApp.getRootFolder()).getId();
  // var frs = SpreadsheetApp.openById(finalReport);
  var total = 0;
  var rowList = [];

  // Sort the data based on the time column
  data.sort(function(a, b) {
    if(a[4] > b[4])
      return 1;
    else if(a[4] < b[4])
      return -1;
    return 0;
  });
  
  Logger.log('5');
  // Append the data in the template
  data.map(function(item) {
    SpreadsheetApp.openById(finalReport).appendRow([item[0], item[1], item[5], item[2], item[3], item[4]]);
    total  = parseInt(item[5]);
    rowList.push(item[6]);
  });

  Logger.log('6');
  // Add Total quantity and table border
  SpreadsheetApp.openById(finalReport).appendRow(['', 'TOTAL', total]);

  Logger.log('7');
  // Add Grid/border
  var setGrid = setGridToFile(finalReport);
  Logger.log(setGrid);

  // Convert PDF file, rename and save as xlsx
  var finalPdfReport = SpreadsheetApp.openById(setGrid).getAs('application/pdf').setName(data[0][2]   '.pdf');
  var blobFileReturn = [finalPdfReport, convertSheetAsXlsx(setGrid, data[0][2]   '_'   data[0][3])[0]];

  Logger.log('8');
  // Add status "Yes" in column G
  rowList.map(function(row) {
    SpreadsheetApp.getActive().getSheetByName('Participants lists').getRange(row, 7, 1, 1).setValue(['YES']);
  });
  
  Logger.log('9');
  return [blobFileReturn, [finalReport]];
}

CodePudding user response：

Spreadsheet operations are sometimes bundled together to improve performance, such as when doing multiple calls to Range.getValue(). However, sometimes you may want to make sure that all pending changes are made right away, for instance to show users data as a script is executing.

To apply all pending Spreadsheet changes, do a flush().

You may place it right after setGridToFile or before var finalPdfReport ....