Run Google script on single sheet instead of entire spreadsheet

1
votes

With my spreadsheet, I have 2 Google forms tied to 2 sheets. When a form gets submitted the script executes and does it's thing. However, I only want the script to execute based on a submission from a single sheet. As it is now, the script executes when either of the forms get submitted.

My two sheets are: Job Submission and Order Submission

Any advice?

// Work Order


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

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 order_count = values[2];
  var order_form = values[7];
  var print_services = values[3];
  var priority = values[5];
  var notes = values[6];
  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('keyOrderCount', order_count);
   copyBody.replaceText('keyOrderForm', order_form);
   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 - " + job_name;
   var body    = "Here is the work order for " + job_name + ". Job is due " + expirationDate + ".";
   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);

   var newDocName = docName + ' for ' + job_name;
   return [copyId, newDocName];
}

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;
  var returnedDocValues = createNewDoc(values);
  var copyId = returnedDocValues[0];
  var docName= returnedDocValues[1];
  printGoogleDocument(copyId, docName);
}

Edit: I'm not sure how to do a complete or verifiable example since it's dependent on the form submission. I don't often work with javascript so I'm still learning.

Anyway, I updated my onFormSubmit function, however, my other functions have failed to execute. The script doesn't create the doc and in turn doesn't get sent to the google cloud print

// When Form Gets submitted
function onFormSubmit(e) { 
// Initialize
  var rng = e.range;
  var sheet = rng.getSheet();
  var name = sheet.getName();
// If the response was not submitted to the right sheet, exit.
  if(name != "Job Submission") return; 
  var values = e.values;
  var returnedDocValues = createNewDoc(values);
  var copyId = returnedDocValues[0];
  var docName= returnedDocValues[1];
  printGoogleDocument(copyId, docName);
}
2
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-forms
Is there a way to execute the script on a specific sheet only? It doesn't necessarily have to look at the form, but only run the script when Sheet1 has a new form submission. – Brandon
It's better to post a minimal reproducible example rather than the full code. – Rubén
Wasn't sure how to do a verifiable code since it's dependent on the form, but I've updated the function in question. – Brandon
Have you set your trigger failure notification to be sent immediately? Are you sure that the form is sending the responses to the "Job Submission" sheet? – Rubén
Trigger failures are daily. And yes, the responses populate in the Job Submission sheet. – Brandon

2 Answers

4
votes

If your onFormSubmit function is on a script bounded to the spreadsheet, the event object includes a range object. You could use getSheet to get the sheet and then getName to get the sheet name.

Example:

function onFormSubmit(e){
  // Initialize
  var rng = e.range;
  var sheet = rng.getSheet();
  var name = sheet.getName();

  // If the response was not submitted to the right sheet, exit.
  if(name != "Responses 1") return; 

  //Otherwise continue
}
0
votes

Going off of Ruben's suggestion, here is what I ended up with

function onFormSubmit(e) {
  // Initialize
  var name = e.range.getSheet().getName();
  // If the response was not submitted to the right sheet, exit.
  if (name != "Job Submission") return;
  var values = e.values;
  var returnedDocValues = createNewDoc(values);
  var copyId = returnedDocValues[0];
  var docName = returnedDocValues[1];
  printGoogleDocument(copyId, docName);
}