0

I have a workflow where users can fill out a Google Form, then the associated Google Sheet information populates a Google Doc and sends an email. The script is fully functioning in all my tests, but when I try to set up an Installable Trigger, it fails time after time.

function CreatePro2() {
  
  // specify doc template and get values from spread
  var sleepINT = 1500
  var templateid = "XXXXXXXXXXXXXXXXXX"; // template file id
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.toast("ENGINE INITIALIZING & Feeding the unicorns");
  Utilities.sleep(sleepINT);
  var sheet = ss.getActiveSheet();
  var data = sheet.getRange(2,1,1,23).getValues(); // starting with row 2 and column 1 as our upper-left most column, 
                                                   // get values from cells from 1 row down, and 15 columns along - hence (2,1,1,15)
  //sheet.getRange("F7").setValue('=IMAGE("https://s-media-cache-ak0.pinimg.com/564x/58/5d/8f/585d8f802867c25df8f1ecc0cf7cadc8.jpg",1)');                                                 
  ss.toast("10%: data captured"); 
  Utilities.sleep(sleepINT);
  
  
  //Create and set a counter for the invoice number, then grab the new invoice number
  var oldInvoiceNumber = sheet.getRange("G2").getValue();
  oldInvoiceNumber += 1;
  sheet.getRange("G2").clear();
  sheet.getRange("G2").setValue(oldInvoiceNumber);
  sheet.getRange("G2").setBackgroundColor("#cecece");
  
  var newInvNumber = sheet.getRange("G2").getValue();
  
  ss.toast("20%: invoice number updated"); 
  Utilities.sleep(sleepINT);
  
  // Make a copy of the invoice template, then Fill up it up with the data from the spreadsheet.
  //NOTE: body.replace method does not have to be in any specific order.
  
  for (var i in data) {
     var replaceTextToImage = function(body, searchText, fileId) {
    var width = 590; // Please set this.
    var blob = DriveApp.getFileById(fileId).getBlob();
    var r = body.findText(searchText).getElement();
    r.asText().setText("");
    var img = r.getParent().asParagraph().insertInlineImage(0, blob);
    var w = img.getWidth();
    var h = img.getHeight();
    img.setWidth(width);
      img.setHeight(width * h / w);}
     
    var row = data[i];
    var docid = DriveApp.getFileById(templateid).makeCopy().getId();
    var doc = DocumentApp.openById(docid);
    var body = doc.getActiveSection();
    var Image = row[10].split("=")[1];
    var selectBody = doc.getBody();    
    
    
    body.replaceText("%NAME%", row[0]);
    body.replaceText("%ADD_LN1%", row[1]);
    body.replaceText("%EMAIL%", row[2]);
    body.replaceText("%PHONE%", row[3]);
    body.replaceText("%DATE%", row[4]);
    body.replaceText("%EXPDATE%", row[5]);
    body.replaceText("%INV_NUM%", row[6] +=1);
    body.replaceText("%PTOTAL%", row[7]);
    body.replaceText("%DESC1%", row[8]);
    body.replaceText("%FEE1%", row[9]);  
    replaceTextToImage(body, '%IMAGE%', Image);
    
    doc.saveAndClose();
    
    ss.toast("30%: template data replaced");
    Utilities.sleep(sleepINT);
    
    //copy the modified template to the specified folder, then delete the first copy we made (to modify it) 
    var file = DriveApp.getFileById(doc.getId());
    var newfolder = DriveApp.getFolderById("YYYYYYYYYYYYYYYYYYYY");
    var oldfolder = DriveApp.getFolderById("ZZZZZZZZZZZZZZZZZZZZ");
    newfolder.addFile(file);
    oldfolder.removeFile(file);
    
    ss.toast("40%: invoice has been put in correct folder");
    Utilities.sleep(sleepINT);
    
    //customize the title for the invoice
    var usernamefordoctitle = sheet.getRange(2, 1, 1, 1).getValues() // this is grabbing the customer name field (A2)
    var name = doc.getName();
    doc.setName(newInvNumber + ' - Invoice for ' + usernamefordoctitle);
    ss.toast("50%: named new invoice");
    Utilities.sleep(sleepINT);

    //create and organize pdf version
    var pdffolder = DriveApp.getFolderById("***************");
    var pdfFILE = DriveApp.getFileById(doc.getId()).getAs('application/pdf');
    pdfFILE.setName(doc.getName() + ".pdf");
    var theFolder = pdffolder;
    var theFile = DriveApp.createFile(pdfFILE);
    theFolder.addFile(theFile);
    ss.toast("60%: PDF generated");
    Utilities.sleep(sleepINT);
    
    var email_status = sheet.getRange("C4").getValue();
    
    if (email_status == "YES" ) {
        //send a pdf copy to customer 
        var pdfEMAIL = DriveApp.getFileById(doc.getId()).getAs('application/pdf').getBytes();
      var emailName = sheet.getRange("A11").getValue()
      var emailToName = emailName; // add designer name here
        var message = "Hi " + emailToName + "!, please find " + usernamefordoctitle + "'s project proposal attached. Reply to this email if you run into any issues.";
        var emailAdd = sheet.getRange("A10").getValue()
        var emailTo = emailAdd; // add designer email here
        var subject = "Proposal for " + usernamefordoctitle + " by" + emailToName + " - Proposal No: " + newInvNumber;
        
        var attach = {fileName:"PROPOSAL " + newInvNumber + " " + usernamefordoctitle + '.pdf',content:pdfEMAIL, mimeType:'application/pdf'};
        MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
        ss.toast("70%: emailed customer");
        Utilities.sleep(sleepINT);
        
        }
        
    else {
        
        ss.toast("No email sent");
        
        }
      }
  

  Utilities.sleep(sleepINT);
  ss.toast("90%: feeding the unicorns some more")
  Utilities.sleep(sleepINT);
  ss.toast("100%: high-fiving the neighbour")
  Utilities.sleep(sleepINT);
  ss.toast("SUCCESS Invoice " + newInvNumber + " has been created. It's in a new doc with the ID " + docid); 
  Utilities.sleep(10000);
}

When I run this script, everything works as expected and the email gets sent. When I try to use an Installed Trigger, it gives me the following error:

Exception: Invalid argument: id at replaceTextToImage(Code:35:25) at CreatePro2(Code:62:5)

I don't know javascript so I've just been piecing this together through trial and error so I apologize for the messy code.

Is there an alternate trigger method I could use that wouldn't cause this issue?

Any help would be appreciated!

Improve this question
10
  • could you Logger.log()/console.log() the var Image? Because the error is telling you that it's not an invalid id. Commented Jul 1, 2020 at 20:41
  • I'm guessing that body, searchtext and fileId are globals and perhaps they don't get iniitialized when running on a trigger. Try putting them in the function. Also I would replace (var i in data) with standard for loop syntax you should only use that syntax for interating through keys in an object. Commented Jul 1, 2020 at 21:09
  • 1
    Hi there @AFischer! In addition to what @Salix and @Cooper requested, could you please check the line replaceTextToImage(body, '%IMAGE%', Image);? The code may be expecting an image identifier in the Image variable, but the provided one could be faulty. To prevent this scenario, please try to hardcode a proven id. Commented Jul 2, 2020 at 9:48
  • Thank you for your response! @Salix how do I go about implementing that logger? Commented Jul 2, 2020 at 12:43
  • Thanks @Cooper! How would I go about putting them in a function? Like I said, I don't know javascript or programming in general so putting this together has been stretching me as is. Commented Jul 2, 2020 at 12:44

2 Answers 2

Reset to default
1

I was talking about this line:

var replaceTextToImage = function(body, searchText, fileId) {

and before I can do anything with them I need to see how they are defined. So you're going to have to stretch yourself a bit more to find and share those declarations.

We need to resolve the undefined variable in here. Refer to my comments.

function CreatePro2() {
  var sleepINT = 1500
  var templateid = "XXXXXXXXXXXXXXXXXX";
  var ss = SpreadsheetApp.getActive(); 
  var sheet = ss.getActiveSheet();
  var data = sheet.getRange(2,1,1,23).getValues();
  var oldInvoiceNumber = sheet.getRange("G2").getValue();
  oldInvoiceNumber += 1;
  sheet.getRange("G2").clear();
  sheet.getRange("G2").setValue(oldInvoiceNumber);
  sheet.getRange("G2").setBackgroundColor("#cecece");
  var newInvNumber = sheet.getRange("G2").getValue();
  for (var i=0;i<data.length;i++) {
    var replaceTextToImage = function(body, searchText, fileId) {//body,searchText and fileId undefined
      var width = 590;
      var blob = DriveApp.getFileById(fileId).getBlob();//fileId is undefined
      var r = body.findText(searchText).getElement();//searchText is undefined
      r.asText().setText("");
      var img = r.getParent().asParagraph().insertInlineImage(0, blob);
      var w = img.getWidth();
      var h = img.getHeight();
      img.setWidth(width);
      img.setHeight(width * h / w);
    }
    var row = data[i];
    var docid = DriveApp.getFileById(templateid).makeCopy().getId();
    var doc = DocumentApp.openById(docid);
    var body = doc.getActiveSection();//Who is making the selection?
    var Image = row[10].split("=")[1];
    var selectBody = doc.getBody();    
    body.replaceText("%NAME%", row[0]);//body is undefined
    body.replaceText("%ADD_LN1%", row[1]);
    body.replaceText("%EMAIL%", row[2]);
    body.replaceText("%PHONE%", row[3]);
    body.replaceText("%DATE%", row[4]);
    body.replaceText("%EXPDATE%", row[5]);
    body.replaceText("%INV_NUM%", row[6] +=1);
    body.replaceText("%PTOTAL%", row[7]);
    body.replaceText("%DESC1%", row[8]);
    body.replaceText("%FEE1%", row[9]);  
    replaceTextToImage(body, '%IMAGE%', Image);
    doc.saveAndClose();
    var file = DriveApp.getFileById(doc.getId());
    var newfolder = DriveApp.getFolderById("YYYYYYYYYYYYYYYYYYYY");
    var oldfolder = DriveApp.getFolderById("ZZZZZZZZZZZZZZZZZZZZ");
    newfolder.addFile(file);
    oldfolder.removeFile(file);    
    var usernamefordoctitle = sheet.getRange(2, 1, 1, 1).getValues();
    var name = doc.getName();
    doc.setName(newInvNumber + ' - Invoice for ' + usernamefordoctitle);    
    var pdffolder = DriveApp.getFolderById("***************");
    var pdfFILE = DriveApp.getFileById(doc.getId()).getAs('application/pdf');
    pdfFILE.setName(doc.getName() + ".pdf");
    var theFolder = pdffolder;
    var theFile = DriveApp.createFile(pdfFILE);
    theFolder.addFile(theFile);    
    var email_status = sheet.getRange("C4").getValue();
    if (email_status == "YES" ) {
      var pdfEMAIL = DriveApp.getFileById(doc.getId()).getAs('application/pdf').getBytes();
      var emailName = sheet.getRange("A11").getValue()
      var emailToName = emailName;
      var message = "Hi " + emailToName + "!, please find " + usernamefordoctitle + "'s project proposal attached. Reply to this email if you run into any issues.";
      var emailAdd = sheet.getRange("A10").getValue()
      var emailTo = emailAdd;
      var subject = "Proposal for " + usernamefordoctitle + " by" + emailToName + " - Proposal No: " + newInvNumber;      
      var attach = {fileName:"PROPOSAL " + newInvNumber + " " + usernamefordoctitle + '.pdf',content:pdfEMAIL, mimeType:'application/pdf'};
      MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
    } else {
      ss.toast("No email sent");
    }
  }
}

The fact that you aren't a programmer and don't know javascript may require that you hire someone to help you but I can't fix problems that I can't generate and can't move declaration that I don't have access to so you have to do it.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

so I noticed that the code is using the active spreadsheet. Depending on what your trigger is, could it be possible that the active spreadsheet isn't the one you want?

When you run the script from the script editor, you usually open it from the spreadsheet it's associated with, meaning the active spreadsheet is always the right one.

I would suggest opening the spreadsheet directly instead.That way you are certain to always have the right one.

You can do it with the id by changing var ss = SpreadsheetApp.getActiveSpreadsheet(); to var ss = SpreadsheetApp.openById(id); doc

or with the url : var ss = SpreadsheetApp.openByUrl(url); doc

If it changes you could also search for the file like you do later in your code and open the file with : var ss = SpreadsheetApp.open(file); doc

[edit] Another thing I noticed, but that should give you an error while running the script, not just with the installed trigger... is this line here : //sheet.getRange("F7").setValue('=IMAGE("https://s-media-cache-ak0.pinimg.com/564x/58/5d/8f/585d8f802867c25df8f1ecc0cf7cadc8.jpg",1)')

If the cell content of where you get your fileId for Image is similar, that would be a problem. Since you define Image like var Image = row[10].split("=")[1];, it means the ID is what's after '=', but 'IMAGE("https://s-media-cache-ak0.pinimg.com/564x/58/5d/8f/585d8f802867c25df8f1ecc0cf7cadc8.jpg",1)' isn't an ID.

Could you tell us what is the content of row[10] ?

If that wasn't why it was getting the wrong Image id, you'll need to do some debug and check that your variables are what you think they are :

You can use Logger.log(data) to log the variables you want to check. Just make sure it's done after you defined that variable. doc

If you do so, please share the results in your question.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.