Generate Google Docs from a sheet and rename them dynamically

Question

With this script, I can generate first the Google Docs and then convert them in PDFs for all the rows of a spreadsheet and rename each PDF with the values in column "File Name", then the Google Doc are deleted.

I would only generate the Google Docs and not the PDFs.

(I have already commented the string copyFile.setTrashed(true) to save the Google Doc, but how to rename them with the column File Name and also, how to avoid to generate the PDFs?

The Code

var ss = SpreadsheetApp.openById("xxxx").getActiveSheet();
var TEMPLATE_ID = ss.getRange("C2").getValue();
var RESULTS_FOLDER_ID = ss.getRange("D2").getValue();

var PDF_FILE_NAME = ''

var FILE_NAME_COLUMN_NAME = 'File Name'

var EMAIL_COLUMN_NAME = 'Email'

var DATE_FORMAT = 'dd/MM/yyyy';

function createNewDocInFolder(newName, RESULTS_FOLDER_ID) {
  var fileID,fileJson,resource;

  resource = {};
  resource.title = newName;
  resource.mimeType = MimeType.GOOGLE_DOCS;
  resource.parents = [{ id: RESULTS_FOLDER_ID }];

  fileJson = Drive.Files.insert(resource);
  fileID = fileJson.id;//The id of the new Google Doc

  return fileID;
}

function GeneraAttestati() {

  var ui = SpreadsheetApp.getUi();

  if (TEMPLATE_ID === '') {    
    ui.alert('TEMPLATE_ID needs to be defined in code.gs')
    return
  }

  var templateFile = DriveApp.getFileById(TEMPLATE_ID)
  var activeSheet = SpreadsheetApp.getActiveSheet()
  var allRows = activeSheet.getDataRange().getValues()
  var headerRow = allRows.shift()
  var copyFile = templateFile.makeCopy();
  var copyId = copyFile.getId()
  var copyDoc = DocumentApp.openById(copyId);
  var copyBody = copyDoc.getBody();
  var numberOfColumns = headerRow.length;

  allRows.forEach(function(row) {

    function makeDoc(copyBody, headerRow, activeRow, numberOfColumns) {

      var body,
          contentAsTxt,
          newName,
          newDocFileID,
          newDoc,
          headerValue,
          activeCell,
          ID = null,
          recipient = null,
          copyFile,
          columnIndex;

      for (columnIndex = 0; columnIndex < numberOfColumns; columnIndex++) {

        headerValue = headerRow[columnIndex]
        activeCell = activeRow[columnIndex]
        activeCell = formatCell(activeCell);

        copyBody.replaceText('%' + headerValue + '%', activeCell)

        if (headerValue === FILE_NAME_COLUMN_NAME) {

          ID = activeCell

        } else if (headerValue === EMAIL_COLUMN_NAME) {

          recipient = activeCell
        }
      }

      contentAsTxt = copyBody.getText();
      newName = PDF_FILE_NAME !== '' ? PDF_FILE_NAME : ID;

      if (RESULTS_FOLDER_ID !== '') {
        newDocFileID = createNewDocInFolder(newName,RESULTS_FOLDER_ID);// Create the DOC file in the results folder
      }

      newDoc = DocumentApp.openById(newDocFileID);
      body = newDoc.getBody();
      body.setText(contentAsTxt);
      newDoc.saveAndClose();
    }

    makeDoc(copyBody, headerRow, row, numberOfColumns);

  })

  ui.alert('New DOC files created')

  return

  function formatCell(value) {

    var newValue = value;

    if (newValue instanceof Date) {

      newValue = Utilities.formatDate(
        value, 
        Session.getScriptTimeZone(), 
        DATE_FORMAT);

    } else if (typeof value === 'number') {

      newValue = Math.round(value * 100) / 100
    }

    return newValue;

  }

}

Alan Wells Alan Wells · Accepted Answer · 2020-06-07T20:37:24

The only way to do this is with the Advanced Drive Service. You must enable it from the code editor, form the "Resources" menu and "Advanced Google services"

Some of the variables were moved out of the loop, and then are passed in, in order to avoid reassigning the same values over and over again on every iteration.

var EMAIL_COLUMN_NAME = 'Email',DATE_FORMAT = 'dd/MM/yyyy';

function createNewDocInFolder(newName, RESULTS_FOLDER_ID) {
  var fileID,fileJson,resource;

  resource = {};
  resource.title = newName;
  resource.mimeType = MimeType.GOOGLE_DOCS;
  resource.parents = [{ id: RESULTS_FOLDER_ID }];

  fileJson = Drive.Files.insert(resource);
  fileID = fileJson.id;//The id of the new Google Doc

  return fileID;
}

function GeneraAttestati() {

  var ui = SpreadsheetApp.getUi();

  if (TEMPLATE_ID === '') {    
    ui.alert('TEMPLATE_ID needs to be defined in code.gs')
    return
  }

  var templateFile = DriveApp.getFileById(TEMPLATE_ID)
  var activeSheet = SpreadsheetApp.getActiveSheet()
  var allRows = activeSheet.getDataRange().getValues()
  var headerRow = allRows.shift()
  var copyFile = templateFile.makeCopy();
  var copyId = copyFile.getId()
  var copyDoc = DocumentApp.openById(copyId);
  var copyBody = copyDoc.getBody();
  var numberOfColumns = headerRow.length;

  allRows.forEach(function(row) {

    function makeDoc(copyBody, headerRow, activeRow, numberOfColumns) {

      var body,
          contentAsTxt,
          newName,
          newDocFileID,
          newDoc,
          headerValue,
          activeCell,
          ID = null,
          recipient = null,
          copyFile,
          columnIndex;

      for (columnIndex = 0; columnIndex < numberOfColumns; columnIndex++) {

        headerValue = headerRow[columnIndex]
        activeCell = activeRow[columnIndex]
        activeCell = formatCell(activeCell);

        copyBody.replaceText('%' + headerValue + '%', activeCell)

        if (headerValue === FILE_NAME_COLUMN_NAME) {

          ID = activeCell

        } else if (headerValue === EMAIL_COLUMN_NAME) {

          recipient = activeCell
        }
      }

      contentAsTxt = copyBody.getText();
      newName = PDF_FILE_NAME !== '' ? PDF_FILE_NAME : ID;
      Logger.log('newName: ' + newName)

      if (RESULTS_FOLDER_ID != '' && newName != '') {


        newDocFileID = createNewDocInFolder(newName,RESULTS_FOLDER_ID);// Create the DOC file in the results folder


        newDoc = DocumentApp.openById(newDocFileID);
        body = newDoc.getBody();
        body.setText(contentAsTxt);
        newDoc.saveAndClose();
      }
    }

    makeDoc(copyBody, headerRow, row, numberOfColumns);

  })

  ui.alert('New DOC files created')

  return

  function formatCell(value) {

    var newValue = value;

    if (newValue instanceof Date) {

      newValue = Utilities.formatDate(
        value, 
        Session.getScriptTimeZone(), 
        DATE_FORMAT);

    } else if (typeof value === 'number') {

      newValue = Math.round(value * 100) / 100
    }

    return newValue;

  }

}

Generate Google Docs from a sheet and rename them dynamically

1 Answers