Download Multiple Files at Once-CodePudding

Here in the code I edit a Google Document automatically by pasting its id to a certain sheet, change some variables (inputs are given in Input tab) and download a pdf version of it. Script first creates a Google Doc version of it and than creates pdf version of it. However, code can only operate for 1 row. How can I ensure it will operate for all rows. Many thanks

function openModalDialog() {
  const html = HtmlService.createHtmlOutputFromFile("Download").setTitle("Succeed");
  SpreadsheetApp.getUi().showSidebar(html);
}

function createDataUrl(type) {
  type = "pdf";
  const blob = CreateReport();
  
  const mimeTypes = { xlsx: MimeType.MICROSOFT_EXCEL, pdf: MimeType.PDF };
  
  return {
    data:
      `data:${mimeTypes[type]};base64,`  
      Utilities.base64Encode(blob.getBytes()),
    filename: `${blob.getName()}.${type}`,
  };
}
function CreateReport(){
  var id = SpreadsheetApp.getActiveSpreadsheet().getId();
  var rawdata = Sheets.Spreadsheets.Values.get(id,'Input!A2:Z');
  var linkdata = Sheets.Spreadsheets.Values.get(id,'Link!B1:B3');
  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const docid = ss.getRange("Link!B2").getValue();
  const pdfid = ss.getRange("Link!B3").getValue();
  const tempId = ss.getRange("Link!B1").getValue();
  const folderdoc = DriveApp.getFolderById(docid);
  const folderpdf = DriveApp.getFolderById(pdfid);
  const templateId = DriveApp.getFileById(tempId);
  for(var i = 0; i < rawdata.values.length; i  ){
    var check = rawdata.values[i][11];
    if(check !== "Done"){
    var Name = rawdata.values[i][0];
    var var1 = rawdata.values[i][1];
    var var2 = rawdata.values[i][2];
    var var3 = rawdata.values[i][3];
    var var4 = rawdata.values[i][4];
    var var5 = rawdata.values[i][5];
    var var6 = rawdata.values[i][6];
    var var7 = rawdata.values[i][7];
    var var8 = rawdata.values[i][8];
    var var9 = rawdata.values[i][9];
    var var10 = rawdata.values[i][10];
    var newdoc = DriveApp.getFileById(tempId).makeCopy();
    var documentId = newdoc.getId();
    var doc = DocumentApp.openById(documentId);
    DriveApp.getFileById(documentId).setName(Name);
    var body = DocumentApp.openById(documentId).getBody();
    body.replaceText('#1#', rawdata.values[i][1]);
    body.replaceText('#2#', rawdata.values[i][2]);
    body.replaceText('#3#', rawdata.values[i][3]);
    body.replaceText('#4#', rawdata.values[i][4]);
    body.replaceText('#5#', rawdata.values[i][5]);
    body.replaceText('#6#', rawdata.values[i][6]);
    body.replaceText('#7#', rawdata.values[i][7]);
    body.replaceText('#8#', rawdata.values[i][8]);
    body.replaceText('#9#', rawdata.values[i][9]);
    body.replaceText('#10#', rawdata.values[i][10]);
    folderdoc.addFile(DriveApp.getFileById(documentId));
    doc.saveAndClose();
    var docblob = doc.getBlob();
    docblob.setContentType
    docblob.setName(doc.getName()   ".pdf");
    folderpdf.createFile(docblob);
    ss.getRange(i 2, 12).setValue("Done");
    SpreadsheetApp.flush();
    return docblob;
    }
  }
}

<h1>Downloading</h1>
<p>Please Wait...</p>

<script>
  google.script.run
      .withSuccessHandler(({ data, filename }) => {
        const a = document.createElement("a");
        document.body.appendChild(a);
        a.download = filename;
        a.href = data;
        a.click();
        google.script.host.close();
      })
      .createDataUrl("pdf");
</script>

CodePudding user response：

In your if block once it does one it will not loop but simply returns. But if you wanted to do multiple Doc files then createDataUrl() would only work for 1 blob. So your logic escapes me.

for(var i = 0; i < rawdata.values.length; i  ){
  var check = rawdata.values[i][11];
  if(check !== "Done"){
    ...
    return docblob;
  }
}

CodePudding user response：

However, code can only operate for 1 row.

This is because in For loop you return the Blob on very first iteration which is the first row of your data

for(var i = 0; i < rawdata.values.length; i  ){
    var check = rawdata.values[i][11];
    if(check !== "Done"){
    return docblob; // Right Here
    }
  }

Now to send blob of multiple files, you need to send all the blob all at once, so you've to them in an array.

I also modified your code a bit, Change this in your server side:-

function createDataUrl(type) {
  type = "pdf";
  const blobs = CreateReport();
  const mimeTypes = { xlsx: MimeType.MICROSOFT_EXCEL, pdf: MimeType.PDF };
  const blob_info = []
  blobs.forEach(r=> blob_info.push([    
   {
    data:
         `data:${mimeTypes[type]};base64,`  
          Utilities.base64Encode(r.getBytes()),
    filename: `${r.getName()}`
}
]))
  return blob_info;
}



function CreateReport(){
  var ssMain = SpreadsheetApp.getActiveSpreadsheet();
  const id = ssMain.getId()
  const rawdata = Sheets.Spreadsheets.Values.get(id,'Input!A2:Z');
  const linkdata = Sheets.Spreadsheets.Values.get(id,'Link!B1:B3');
  const ss = ssMain.getActiveSheet();
  const docid = ss.getRange("Link!B2").getValue();
  const pdfid = ss.getRange("Link!B3").getValue();
  const tempId = ss.getRange("Link!B1").getValue();
  const folderdoc = DriveApp.getFolderById(docid);
  const folderpdf = DriveApp.getFolderById(pdfid);
  const templateId = DriveApp.getFileById(tempId);
  
  const fileBlobs = [] // We will use it to store info of all blob
  
  for(var i = 0; i < rawdata.values.length; i  ){
    var check = rawdata.values[i][11];
    if(check !== "Done")
    {
      var Name = rawdata.values[i][0];
      var newdoc = DriveApp.getFileById(tempId).makeCopy();
      var documentId = newdoc.getId();
      var doc = DocumentApp.openById(documentId);
      DriveApp.getFileById(documentId).setName(Name);
      var body = DocumentApp.openById(documentId).getBody();
      body.replaceText('#1#', rawdata.values[i][1]);
      body.replaceText('#2#', rawdata.values[i][2]);
      body.replaceText('#3#', rawdata.values[i][3]);
      body.replaceText('#4#', rawdata.values[i][4]);
      body.replaceText('#5#', rawdata.values[i][5]);
      body.replaceText('#6#', rawdata.values[i][6]);
      body.replaceText('#7#', rawdata.values[i][7]);
      body.replaceText('#8#', rawdata.values[i][8]);
      body.replaceText('#9#', rawdata.values[i][9]);
      body.replaceText('#10#', rawdata.values[i][10]);
      folderdoc.addFile(DriveApp.getFileById(documentId));
      doc.saveAndClose();
      var docblob = doc.getBlob();
      docblob.setContentType
      docblob.setName(doc.getName()   ".pdf");
      folderpdf.createFile(docblob);
      ss.getRange(i 2, 12).setValue("Done");
      SpreadsheetApp.flush();
      fileBlobs.push(docblob)
    }
  }
  return fileBlobs 
}

and Change this in your client side:-

<script>
  google.script.run.withSuccessHandler(function(r){  
    for (var i = 0 ; i < r.length; i  )
    {    
    var a = document.createElement("a");
    document.body.appendChild(a);
    a.download = r[i][0].filename;
    a.href = r[i][0].data;
    a.click();
    }
    google.script.host.close()
   }).createDataUrl("pdf");
   //
</script>

Reference:

return