Modify SaveAsPDF script to automatically run through a range of names within a drop-down

[Steve]

Hello Google Script Gurus,

I have a come seeking your great wisdom. I am responsible for sending out monthly invoices to a small group of partners, 5 to be exact. These invoices are made in Google Sheets. I grew tired of having to send out the invoices manually, so I found a way of automating this process.

I found/modified the code at the bottom (mostly from here) that works perfectly so far. It will:

• Verify that the user does in fact want to send the invoice
• Composes an email with a custom subject line and body
• Export the invoice (sheet called 'Invoice_Display') as a PDF and attach it to the email
• Save a copy of the invoice in Google Drive
• Display a 'Success' message

In cell A9 of sheet 'Invoice_Display', I have a drop-down box with the partners' names. As I select the different names, the formulas on that sheet will pull the partners' corresponding data/info/charges for their invoice.

Current process: Right now, I select a partner's name, and run the script. Then select the next partner's name, run the script, and so on.

My question is - is there a way to essentially "loop" through the names within the drop-down box so that all invoices are sent out automatically with a single click/run of this script?

If it helps, the drop-down of the partners' names is done via Data Validation that references a named range called 'pilots'.

My apologies for not including a link to the spreadsheet as it contains some sensitive data. I have also removed some info in the code below (redactions in red). If this is really needed, I will make another copy of the sheets and provide that link via PM. I do hope you understand and I sincerely appreciate everyone's help with this.

Thank-you very much in advance for any and all assistance you may be able to give.

function emailInvoiceAsPDF() {

  DocumentApp.getActiveDocument();

  DriveApp.getFiles();

  // Confirm that the user wishes to send the invoice.

  var result = SpreadsheetApp.getUi().alert(

 'Please Confirm',

 'Email a copy of this invoice to the partner?',

  SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);

    //If user confirms, send out emails

  if(result === SpreadsheetApp.getUi().Button.OK) {

  // This is the link to my spreadsheet with the Form responses and the Invoice Template sheets

  // Add the link to your spreadsheet here 

  // or you can just replace the text in the link between "d/" and "/edit"

  // In my case is the text: 17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI

  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/spreadsheetlink/edit");

  // We are going to get the email address from the cell "D9" from the "Background_Original" sheet

  // Change the reference of the cell or the name of the sheet if it is different

  const value = ss.getSheetByName("Background_Original").getRange("E9").getValue();

  const email = value.toString();

  // Subject of the email message

  const subject = 'Invoice: ' + ss.getSheetByName("Invoice_Display").getRange("C5").getValue().toString() + " " + ss.getSheetByName("Invoice_Display").getRange("E5").getValue().toString();

  // Email Text. You can add HTML code here - see ctrlq.org/html-mail

  const body = "Hi "

  + ss.getSheetByName("Background_Original").getRange("C9").getValue().toString() +

  ",<br/><br/>Please see attached.<br/><br/>Your invoice amount is $"

  + ss.getSheetByName("Background_Original").getRange("F9").getValue().toString()

  + ".<br/><br/>Please remit to email address via Interac E-Transfer, and include the invoice number in the memo line.<br/><br/>Thank-you.<br/><br/>Kind Regards,<br/>Name<br/>Title.<br/>Number";

  // Again, the URL to your spreadsheet but now with "/export" at the end

  // Change it to the link of your spreadsheet, but leave the "/export"

  const url = 'https://docs.google.com/spreadsheets/spreadsheetlink/export?';

  const exportOptions =

    'exportFormat=pdf&format=pdf' + // export as pdf

    '&size=letter' + // paper size letter / You can use A4 or legal

    '&portrait=true' + // orientation portal, use false for landscape

    '&fitw=true' + // fit to page width false, to get the actual size

    '&sheetnames=false&printtitle=false' + // hide optional headers and footers

    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines

    '&fzr=false' + // do not repeat row headers (frozen rows) on each page

    '&gid=SpreadsheetID'; // the sheet's Id. Change it to your sheet ID.

  // You can find the sheet ID in the link bar. 

  // Select the sheet that you want to print and check the link,

  // the gid number of the sheet is on the end of your link.

  

  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};

  

  // Generate the PDF file

  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();

  

  // Send the PDF file as an attachement 

    GmailApp.sendEmail(email, subject, body, {

      name: 'Name',

      htmlBody: body,

      attachments: [{

            fileName: "Name Invoice for " + ss.getSheetByName("Background_Original").getRange("D9").getValue().toString() + " " + ss.getSheetByName("Invoice_Display").getRange("O9").getValue().toString() + ".pdf",

            content: response.getBytes(),

            mimeType: "application/pdf"

        }]

    });

  // Save the PDF to Drive in the Invoices folder.

  const nameFile = "Name Invoice for " + ss.getSheetByName("Background_Original").getRange("D9").getValue().toString() + " " + ss.getSheetByName("Invoice_Display").getRange("O9").getValue().toString() + ".pdf"

  var folder = DriveApp.getFolderById('folderID');// get the folder

  folder.createFile(response.setName(nameFile));//create the file directly in the folder

  //Let the user know that the emails were sent successfully or not.

  var emailsent = SpreadsheetApp.getUi().alert(

   'Success',

   'Invoice sent.',

   SpreadsheetApp.getUi().ButtonSet.OK);

  } else {

  }

}

[CBMServices Web]

Hi Steve,

You definitely can automate this. You can't use the pull down field. But if you reference where the data validation information comes from, you can use that info to loop through your software and process one invoice at a time.

So just create a new function, it should pull the source of the data validation to get the list of persons you want to send to. Then do a loop (for loop works fine) going through one person at a time and calling you existing function that processes the invoice.

You may want to comment out all the UI responses from your code so that you don't need to clear all those prompts.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3b12dd90-47b9-411c-a4c4-a5de9000a4f9n%40googlegroups.com.

[Steve]

Hi George,

Yes! That's exactly what I want to do. A loop.

I read your mind regarding commenting out the user interaction and have been working on a duplicate script with the user interaction removed in preparation for this.

Forgive me, but my knowledge in Scripts is limited. Any chance you could point me in the right direction or have a demo loop sample I could work from, to pull the list of names from the named range?

Sorry for my basic question.

Thanks again!

Steve

[cbmserv...@gmail.com]

I can help you with it. Create a sample spreadsheet without any private data and share it with me. I will then update the script for you to do what you want.

 

Add my email address as an editor to the sample spreadsheet so that I can modify the script for you.

 

cbmserv...@gmail.com

 

Thanks

  George

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9da885dc-aa42-4977-ac2a-45a0ccb77d38n%40googlegroups.com.

[Steve]

That would be awesome! I shall do that and send it your way. You should have it a bit later tonight. Thanks so much!

[Steve]

Hey George, just sent you the invite link. You'll notice its coming from a different email. Thanks again!

On Friday, April 8, 2022 at 3:52:41 PM UTC-4 George wrote:

[CBMServices Web]

Got it thanks. Will update this aft and send you an email back when done.

George 

On Fri., Apr. 8, 2022, 1:32 p.m. Buzzard Aviators, <buzzard...@gmail.com> wrote:

Hey George, just sent you the invite link. Thanks again!

On Friday, April 8, 2022 at 3:52:41 PM UTC-4 George wrote:

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/27801531-f22c-469a-84c4-84a4ad9ca6a5n%40googlegroups.com.

[Steve]

George absolutely knocked it out of the park! Thank-you very much! If you see George is helping you on your next project, be grateful, and know you're in good hands.

Steve

[CBMServices Web]

You are welcome. Glad to be of help.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7298aafa-41d1-4028-b8cb-f842fb9b0eb2n%40googlegroups.com.