1

The gist of the project is: I have Google Form answers that get populated on a Google doc, let's call this doc the template. The template is copied so I never overwrite the original. That copy is converted to PDF, sent to email, and moved to a specific folder on my Drive. This function happens flawlessly with every Form submission and gets triggered on submit. My next function is supposed to send that copied doc to my Google Cloud Print, but I'm having trouble writing the code for that. I have it to the point where it will print the doc on Form submit, but I have to specifically define the doc's ID. Unfortunately the ID is not static since a new doc is made with every submission. Here's my full code minus any sensitive information:

// Work Order


// Get template from Google Docs and name it
var docTemplate = ""; // *** replace with your template ID ***
var docName = "Work Order";

function addDates() {
  var date = new Date(); // your form date
  var holiday = ["09/04/2017", "10/09/2017", "11/23/2017", "12/24/2017", "12/25/2017", "01/01/2018"]; //Define holiday dates in MM/dd/yyyy
  var days = 5; //No of days you want to add
  date.setDate(date.getDate());
  var counter = 0;
  if (days > 0) {
    while (counter < days) {
      date.setDate(date.getDate() + 1);
      var check = date.getDay();
      var holidayCheck = holiday.indexOf(Utilities.formatDate(date, "EDT", "MM/dd/yyyy"));
      if (check != 0 && check != 6 && holidayCheck == -1) {
        counter++;
      }
    }
  }
  Logger.log(date) //for this example will give 08/16/2017
  return date;
}

function createNewDoc(values) {
//Get information from form and set as variables
  var email_address = "";
  var job_name = values[1];
  var ship_to = values[11];
  var address = values[12];
  var order_count = values[7];
  var program = values[2];
  var workspace = values[3];
  var offer = values[4];
  var sort_1 = values[5];
  var sort_2 = values[6];
  var image_services = values[9];
  var print_services = values[10];
  var priority = values[13];
  var notes = values[14];
  var formattedDate = Utilities.formatDate(new Date(), "EDT", "MM/dd/yyyy");
  var expirationDate = Utilities.formatDate(addDates(), "EDT", "MM/dd/yyyy");

  // Get document template, copy it as a new temp doc, and save the Doc's id
  var copyId = DriveApp.getFileById(docTemplate)
    .makeCopy(docName + ' for ' + job_name)
    .getId();
  // Open the temporary document
  var copyDoc = DocumentApp.openById(copyId);
  // Get the document's body section
  var copyBody = copyDoc.getActiveSection();

  // Replace place holder keys,in our google doc template  
  copyBody.replaceText('keyJobName', job_name);
  copyBody.replaceText('keyShipTo', ship_to);
  copyBody.replaceText('keyAddress', address);
  copyBody.replaceText('keyOrderCount', order_count);
  copyBody.replaceText('keyProgram', program);
  copyBody.replaceText('keyWorkspace', workspace);
  copyBody.replaceText('keyOffer', offer);
  copyBody.replaceText('keySort1', sort_1);
  copyBody.replaceText('keySort2', sort_2);
  copyBody.replaceText('keyImageServices', image_services);
  copyBody.replaceText('keyPrintServices', print_services);
  copyBody.replaceText('keyPriority', priority);
  copyBody.replaceText('keyNotes', notes);
  copyBody.replaceText('keyDate', formattedDate);
  copyBody.replaceText('keyDue', expirationDate);

  // Save and close the temporary document
  copyDoc.saveAndClose();

  // Convert temporary document to PDF by using the getAs blob conversion
  var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");

  // Attach PDF and send the email
  var subject = "New Job Submission";
  var body = "Here is the work order for " + job_name + "";
  MailApp.sendEmail(email_address, subject, body, {
    htmlBody: body,
    attachments: pdf
  });

  // Move file to folder
  var file = DriveApp.getFileById(copyId);
  DriveApp.getFolderById("").addFile(file);
  file.getParents().next().removeFile(file);
}

function printGoogleDocument(copyId, docName) {
  // For notes on ticket options see https://developers.google.com/cloud-print/docs/cdd?hl=en
  var ticket = {
    version: "1.0",
    print: {
      color: {
        type: "STANDARD_COLOR"
      },
      duplex: {
        type: "NO_DUPLEX"
      },
    }
  };

  var payload = {
    "printerid": "",
    "content": copyId,
    "title": docName,
    "contentType": "google.kix", // allows you to print google docs
    "ticket": JSON.stringify(ticket),
  };

  var response = UrlFetchApp.fetch('https://www.google.com/cloudprint/submit', {
    method: "POST",
    payload: payload,
    headers: {
      Authorization: 'Bearer ' + GoogleCloudPrint.getCloudPrintService().getAccessToken()
    },
    "muteHttpExceptions": true
  });

  // If successful, should show a job here: https://www.google.com/cloudprint/#jobs

  response = JSON.parse(response);
  if (response.success) {
    Logger.log("%s", response.message);
  } else {
    Logger.log("Error Code: %s %s", response.errorCode, response.message);
  }
  return response;
}

// When Form Gets submitted
function onFormSubmit(e) {
  var values = e.values;
  createNewDoc(values);
  printGoogleDocument(copyId, docName);
}
Brandon
  • 93
  • 9

1 Answers1

0

Return the new document from the 'createNewDoc(values);' function by changing by adding this to the end of the createNewDoc() function, right before the closing bracket:

//Starting at the code here
// Move file to folder
var file = DriveApp.getFileById(copyId);
DriveApp.getFolderById("").addFile(file);
file.getParents().next().removeFile(file);

//Add this
var newDocName = docName + ' for ' + job_name;
return [file, newDocName];
//To this point

}

Then change the onFormSubmit() function as shown below:

// When Form Gets submitted
function onFormSubmit(e) {
  var values = e.values;
  var returnedDocValues = createNewDoc(values);
  var file = returnedDocValues[0];
  var docName= returnedDocValues[1];
  printGoogleDocument(file, docName);
}

Let me know if there are errors as I have not tested this code myself.

Karl_S
  • 3,364
  • 2
  • 19
  • 33
  • I think we're really close. Everything still works, but it's still not able to send the file to the printer. However, when I change the "content" value under this variable: "var payload" with the docTemplate ID at the top, it prints fine. But obviously it's printing my template doc and not the new one. Is there something I should be doing different there? – Brandon Aug 15 '17 at 14:13
  • I had a bit of a typo in the onFormSubmit() code where I had a space in front of each [ character. I edited the response above. The changed lines are the last two starting with var: ` var file = returnedDocValues[0];` and ` var docName= returnedDocValues[1];` – Karl_S Aug 15 '17 at 14:22
  • I've made the change but still got the same result. – Brandon Aug 15 '17 at 14:27
  • OK, if content is looking for the ID and not the file itself, then change the return line in the createNewDoc() function to `return [copyId, newDocName];` I do not have cloudprint here to test with. Basically whatever the value of content should be is what we need to return from the createNewDoc() function a the first item. – Karl_S Aug 15 '17 at 14:42
  • Still having some trouble even with the copyId, but I'm going to keep troubleshooting. – Brandon Aug 15 '17 at 16:04
  • It turns out this was a really simple fix. All I had to do was place your new code: 'var newDocName = docName + ' for ' + job_name;' 'return [file, newDocName];' before my move file to folder code. I also replaced every instance of file to copyId and it worked perfect. Thanks a lot! – Brandon Aug 15 '17 at 18:27
  • Moving the line `'return [file, newDocName];` in front of the move code will stop the move from occurring. The return will cause the function to return to the calling function and ignore all code afterwards. This suggests a problem with your Move code. See [the Troubleshooting page](https://developers.google.com/apps-script/guides/support/troubleshooting) for help troubleshooting the code. Especially moving the return to the end of the function and running the debugger with a stop on the return to see if an error occurs. – Karl_S Aug 15 '17 at 18:42
  • Yeah you're right, it did stop the move from occurring. Back to troubleshooting. – Brandon Aug 15 '17 at 19:46
  • You need to provide a folder ID to move the file to. This line `DriveApp.getFolderById("").addFile(file);` needs the folder ID inside the quotes in the portion `getFolderById("")` If this is always the same folder, you can hard code the ID, which is the long unusual string at the end of the Drive URL when you get a link to the folder. So for https://drive.google.com/drive/u/0/folders/0B91nRlqGXdmtvc0xxcHpnMDA this is 0B91nRlqGXdmtvc0xxcHpnMDA. See the [move function supplied in this answer to another thread](https://stackoverflow.com/a/38810986/3354226), as well as the comment to the reply. – Karl_S Aug 15 '17 at 20:25
  • Yep I have that in my working code. I just took it out for the post. The project I'm working on is sensitive so I was being cautious by taking out any ID values. – Brandon Aug 15 '17 at 22:09
  • Did you try copying the moveFile() function from the link in my last comment and changing your move code to call it? You seem to have both ID's that function is requesting. – Karl_S Aug 16 '17 at 12:18
  • Oddly enough, I changed the code again back to where you originally had it laid out, made sure to change all instances of 'file' to 'copyId' (except the var in the 'move' code), and everything is working now. I thought I had tried that before, but I probably missed changing one of the 'file' variables. So it's successfully moving the file and sending to my cloud print. I really appreciate the help and the continual follow-up! – Brandon Aug 17 '17 at 12:50