Exception: The document is inaccessible. Please try again later. Google Scripts

Question

I've been trying to fix this for the whole day and i can't solve it.

The problem is with the generatePDF function, when i try to open the file after i copy it. The document is converted to word and therefore it won't let me open it, because DriveApp only supports Google Docs. Please find me some solutions.

The only function that is wrong is generatePDF() because i can't open the template file. In that function i set the participant_row variable to '2', because i just want it to work at least for one person, that's why.

// Global variables
var PDF_FOLDER_ID = "1OiCzqoHChrBoPKBo2n8rf-xhpc71S86G";
var TMP_FOLDER_ID = "11mtvVbt4qNUmfWK-zlZHE0W60O14aBB5";
var TEMPLATE_DOCS_FILE_ID = "1fVOB5sdkSGvEhjGYPlJsYiOGgPMcyqGo";
var sheet_name = "Foaie1";
var FIRST_NAME = 2;
var SECOND_NAME = 3;
var email_position = 7;
var PARTICIPANT_ADDRESS = 8;
var PARTICIPANT_CITY = 9;
var PARTICIPANT_POSTAL_CODE = 10;
var PARTICIPANT_COUNTRY = 11;
var PARTICIPANT_ID = 29;
var yesno_position = 30;

// Open spreadsheet
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name).activate();
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Get person's email
function get_email(spreadsheet, participant_row)
{
  //                               ROW        , COLUMN
  return spreadsheet.getRange(participant_row, email_position).getValue();
}


// Dont send the same email to the same person
function check_already_sent(spreadsheet, participant_row)
{
  var bool_alreadySentEmail = spreadsheet.getRange(participant_row, yesno_position).getValue();
  if(bool_alreadySentEmail.toString().toUpperCase() == "YES") {
    Logger.log("Skipped " + spreadsheet.getRange(participant_row, email_position).getValue());
    return 1.
  }
  else {
    Logger.log("Sending to " + spreadsheet.getRange(participant_row, email_position).getValue());
    return 0;
  }
}


// Return person's name
function get_name(spreadsheet, participant_row) {
  return spreadsheet.getRange(participant_row, FIRST_NAME).getValue() + " " + spreadsheet.getRange(participant_row, SECOND_NAME).getValue();
}


// Generate custom PDF for every person
function generatePDF(spreadsheet, participant_row)
{
  participant_row = 2;
  // Get files
  var pdfFolder = DriveApp.getFolderById(PDF_FOLDER_ID);
  var tmpFolder = DriveApp.getFolderById(TMP_FOLDER_ID);
  var templateDocs = DriveApp.getFileById(TEMPLATE_DOCS_FILE_ID);

  // Make temporary files in the tmpFolder
  var newTMP_File = templateDocs.makeCopy(tmpFolder);

  // Change values
  var opened_Docs = DocumentApp.openById(newTMP_File.getId());
  var get_body = opened_Docs.getBody();

  // Execute replacements
  get_body.replaceText("{{First Name}}",spreadsheet.getRange(participant_row, FIRST_NAME));
  get_body.replaceText("{{Second Name}}",spreadsheet.getRange(participant_row, SECOND_NAME));
  get_body.replaceText("{{ID}}",spreadsheet.getRange(participant_row, PARTICIPANT_ID));
  get_body.replaceText("{{Address}}",spreadsheet.getRange(participant_row, PARTICIPANT_ADDRESS));
  get_body.replaceText("{{City}}",spreadsheet.getRange(participant_row, PARTICIPANT_CITY));
  get_body.replaceText("{{Postal Code}}",spreadsheet.getRange(participant_row, PARTICIPANT_POSTAL_CODE));
  get_body.replaceText("{{Country}}",spreadsheet.getRange(participant_row, PARTICIPANT_COUNTRY));


  // Create PDF with the modified temporary file
  var newPDF = newTMP_File.getAs(MimeType.PDF);
  var savedPDF_File = pdfFolder.createFile(newPDF).setName("JEE_Summer_Conference_Invoice " + FIRST_NAME + "_" + SECOND_NAME);
}


// Check for duplicate entry in the excel sheet
function count_duplicate(spreadsheet, participant_row)
{
  var duplicates = 0;
  var counter = 1;
  for(var i = participant_row; i >=0 ; i--)
    if(get_email(spreadsheet, participant_row).localeCompare(get_email(spreadsheet, participant_row - counter)) == 0) {
      duplicates++;
      counter++;
    }
    else
      break;
  
  return duplicates;
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Send PDFs').addItem('SEND PDFs','main').addToUi();
}

// Main function
function main()
{
  // GET THE INDEX OF THE LAST ROW
  var last_row = spreadsheet.getLastRow();

  // Generate PDF for every participant
  for(var participant_row = 2; participant_row <= last_row; ++participant_row) {
  
    // Check if we didn't already send an email to that person
    if(check_already_sent(spreadsheet, participant_row) == 1) {
        continue;
    }

    // Check duplicate
    if(participant_row < last_row && get_email(spreadsheet, participant_row).localeCompare(get_email(spreadsheet,participant_row+1)) == 0)
      continue;

    // Generate PDF
    generatePDF(spreadsheet, participant_row);

    // Mark the person as someone who has received the email to not receive another one
    spreadsheet.getRange(participant_row, yesno_position).setValue("YES");

    // Mark duplicates as sent as well
    var nr_of_duplicates = count_duplicate(spreadsheet, participant_row);
    for(var i=1; i<=nr_of_duplicates;i++) {
        var get_value = spreadsheet.getRange(participant_row,yesno_position).getValue();
        spreadsheet.getRange(participant_row - i, yesno_position).setValue(get_value);
    }
  }
}

Answer 1

How to convert a docx file to a Google Document.

For this you need the Drive Advanced Service to be enabled. Note that this will enable v2 of Drive and not the latest v3.

Press the + button next to Services

Choose Drive API, v2, and in the Indentifier, make sure its "Drive". Then you should be able to run the code below.

function convertDocx(id) {
  // Using the normal drive service to get the blob (binary data)
  const docx = DriveApp.getFileById(id)
  const blob = docx.getBlob()

  // Creating a new file
  const newDoc = Drive.newFile()
  // Setting the title
  newDoc.title = "New Converted Document"
  // Converting the docx file to GDoc
  const newGDoc = Drive.Files.insert(newDoc, blob, {convert:true})
  // Return the new id
  return newGDoc.id
}


function test(){
  console.log(convertDocx("[THE DOCUMENT ID]"))
}

This script will take a docx file id (it needs to be the file id, not the containing folder), and convert it to a Google Document, returning the new id.

To then export that to PDF you could do something like

function convertToPdf(id) {
    docBlob = DocumentApp.openById(id).getAs('application/pdf');
    DriveApp.createFile(docBlob);
}

Reference

• Enable advanced services
• DriveApp
• getBlob()