Here’s the scenario: you create a form, you have a script that triggers onFormSubmit and all is well... until it gets popular. Occasionally you start having interlacing modifications from separate invocations of your script to the spreadsheet. Clearly, this kind of interlacing is not what you intended for the script to do. Up until now, there was no good solution to this problem -- except to remain unpopular or just be lucky. Neither are great solutions.
onFormSubmit
Now, my friend, you are in luck! We’ve just launched the LockService to deal with exactly this problem. The LockService allows you to have only one invocation of the script or portions thereof run at a time. Others that would’ve run at the same time can now be made to wait nicely in line for their turn. Just like the line at the checkout counter.
The LockService can provide two different kinds of locks-- one that locks for any invocation of your script, called a public lock, and another that locks only invocations by the same user on your script, called a private lock. If you’re not sure, using a public lock is the safest bet.
For example, in the scenario in the previous paragraph you would want something like this:
function onFormSubmit() { // we want a public lock, one that locks for all invocations var lock = LockService.getPublicLock(); lock.waitLock(30000); // wait 30 seconds before conceding defeat. // got the lock, you may now proceed ...whatever it used to do here.... lock.releaseLock(); }
It’s best to release the lock at the end, but if you don’t, any locks you hold will be released at the end of script execution. How long should you wait? It depends on two things mainly: how long the thing you’re going to do while holding the lock takes, and how many concurrent executions you expect. Multiply those two and you’ll get your timeout. A number like 30 seconds should handle a good number of cases. Another way to pick the number is frankly to take an educated guess and if you guess too short, the script will occasionally fail.
If you want to avoid total failure if you can’t get the lock, you also have the option trying to get the lock and doing something else in the event of not being able to get it:
function someFunction() { var lock = LockService.getPublicLock(); if (lock.tryLock(30000)) { // I got the lock! Wo000t!!!11 Do whatever I was going to do! } else { // I couldn’t get the lock, now for plan B :( GmailApp.sendEmail(“admin@example.com”, “epic fail”, “lock acquisition fail!”); } }
So now your scripts can be as popular as they can get with no worries about messing up shared resources due to concurrent edits! Check out the LockService documentation for more information.
We are currently rolling out a change to the organization of existing resources in collections in Google Docs. This change is completely transparent to users of the Google Docs web user interface, but it is technically visible when using the Google Documents List API to make requests with the showroot=true query parameter or specifically querying the contents of the root collection. In order to understand this change, first read how Google Docs organizes resources.
showroot=true
The change involves Google removing those resources from a user’s root collection that already exist within another collection accessible to the given user. That is, if “My Presentation” is currently in root and in the “My Talks” collection, after this change it will only exist in the “My Talks” collection.
We are making this change in order to make the organization of resources less confusing for API developers. This change allows clients to know that a resource either exists in root or in some collection under root. Clients can still retrieve all resources, regardless of which collections they’re in, using the resources feed.
The change is rolling out gradually to all Google Docs users over the next few months.
Developers with further questions about this change should post in the Google Documents List API forum.
Update (August 2014): Try the Yet Another Mail Merge add-on for Google Sheets.
Editor’s Note: This blog post is co-authored by James, Steve and Romain who are Google Apps Script top contributors. -- Ryan Boyd
The Google Apps Script team is on a roll and has implemented a ton of new features in the last few months. Some of us “Top Contributors” thought it will be a useful exercise to revisit the Mail Merge use case and discuss various ways in which we can do Mail Merge using Apps Script. Below are several techniques that tap into the power of Google Apps Script by utilizing Gmail, Documents and Sites to give your mailings some zing. Mail Merge is easy and here is how it can be done.
The Simple Mail Merge tutorial shows an easy way to collect information from people in a Spreadsheet using Google Forms then generate and distribute personalized emails. In this tutorial we learn about using “keys,” like ${"First Name"}, in a template text document that is replaced by values from the spreadsheet. This Mail Merge uses HTML saved in the “template” cell of the spreadsheet as the content source.
${"First Name"}
The Gmail Service is now available in Google Apps Script, allowing you to create your template in Gmail where it is saved as a draft. This gives us the advantage of making Mail Merge more friendly to the typical user who may not know or care much about learning to write HTML for their template. The mail merge script will replace the draft and template keys with names and other information from the spreadsheet and automatically send the email.
To use this mail merge, create a new spreadsheet, and click on Tools > Script Gallery. Search for “Yet another Mail Merge” and you will be able to locate the script. Then, click Install. You’ll get two authorization dialogs, click OK through them. Add your contact list to the spreadsheet, with a header for each column. Then compose a new mail in Gmail. Follow this syntax for the “keys” in your template: $%column header% (see above). Click Save now to save your draft. Go back to your spreadsheet and click on the menu Mail Merge. A dialog pops up. Select your draft to start sending your emails.
$%column header%
You can add CCs, include attachments and format your text just as you would any email. People enjoy “Inserting” images in the body of their emails, so we made sure to keep this feature in our updated mail merge. To automate this process we will use a new advanced parameter of the method sendEmail, inlineImages. When the script runs it looks in the email template for images and make sure they appear as inline images and not as attachments. Now your emails will look just as you intended and the whole process of mail merge got a whole lot simpler.
sendEmail
inlineImages
The next Mail Merge will use a template that is written in a Google Document and sent as an attachment. Monthly reports, vacation requests and other business forms can use this technique. Even very complex documents like a newsletter or brochure can utilize the automation of Google Apps Script to add the personal touch of having your patron’s name appear as a salutation.
Like in the Mail Merge for Gmail, the Google Docs template will use “keys” as placeholders for names, addresses or any other information that needs to be merged. Google Apps Script can add dynamic elements as well. For example you may want to include a current stock quote using the Financial Service, a chart from the Charts Service, or a meeting agenda automatically fetched for you by the Calendar Service.
As the code sample below demonstrates, the Google Apps Script gets the document template, copies it in a new temporary document, opens the temp document, replaces the key placeholders with the form values, converts it to PDF format, composes the email, sends the email with the attached PDF and deletes the temp document.
Here is a code snippet example to get you started. To use this mail merge, create a new spreadsheet, and click on Tools > Script Gallery. Search for “Employee of the Week Award” and you will be able to locate the script.
// Global variables docTemplate = “enter document ID here”; docName = “enter document name here”; function sendDocument() { // Full name and email address values come from the spreadsheet form var full_name = from-spreadsheet-form var email_address = from-spreadsheet-form // Get document template, copy it as a new temp doc, and save the Doc’s id var copyId = DocsList.getFileById(docTemplate) .makeCopy(docName+' for '+full_name) .getId(); var copyDoc = DocumentApp.openById(copyId); var copyBody = copyDoc.getActiveSection(); // Replace place holder keys, copyBody.replaceText('keyFullName', full_name); var todaysDate = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy"); copyBody.replaceText('keyTodaysDate', todaysDate); // Save and close the temporary document copyDoc.saveAndClose(); // Convert temporary document to PDF by using the getAs blob conversion var pdf = DocsList.getFileById(copyId).getAs("application/pdf"); // Attach PDF and send the email MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf}); // Delete temp file DocsList.getFileById(copyId).setTrashed(true); }
For the last example let’s assume you have a great Google Site where you create new letters for your followers. However, you have had some feedback suggest that while many users don’t mind visiting your site, some would prefer to have the newsletter emailed to them. Normally this would require copying and pasting into an email or doc. Why not simply automate this with Google Apps Script?
The body section of a site, the part you edit, can be captured as HTML by the Sites Service and placed in the body of an email. Because the return value is HTML, the pictures and text formatting come through in the email.
Here is a simple example for you to try out:
function emailSiteBody() { var site = SitesApp.getPageByUrl('YourPageURL'); var body = site.getHtmlContent(); MailApp.sendEmail('you@example.com', 'Site Template', 'no html :( ', {htmlBody: body}); }
It really is that simple. Add a for loop with email values from a spreadsheet and this project is done.
for
Happy merging!
Updated 10/28: fixed instructions for accessing the complete script source for solution 3.
Editor's note: This post by Google Senior Product Manager Justin Smith has been cross-posted from the Google Code blog because we think it'll be of great interest to Google Apps developers. -- Ryan Boyd
In the coming weeks we will be making three changes to the experimental OAuth 2.0 endpoint. We expect the impact to be minimal, and we’re emailing developers who are most likely to be affected.
https://www.example.com/back?error=access_denied.
https://www.example.com/back?error=access_denied
https://www.example.com/back#error=access_denied
approval_prompt=force
access_type=offline
https://accounts.google.com/o/oauth2/auth? client_id=21302922996.apps.googleusercontent.com& redirect_uri=https://www.example.com/back& scope=https://www.google.com/m8/feeds/& response_type=code
https://accounts.google.com/o/oauth2/auth? client_id=21302922996.apps.googleusercontent.com& redirect_uri=https://www.example.com/back& scope=https://www.google.com/m8/feeds/& response_type=code& access_type=offline& approval_prompt=force
Just a few weeks ago, several members of our Google Apps Developer Relations team returned from Buenos Aires, Sao Paulo, Hyderabad and Bangalore where they met with many enthusiastic developers as part of Google Developer Day and DevFest events. We're now headed to the skies again and looking forward to talking with amazing Russian, Polish, Czech and French developers.
Whether you're building integrations with Google Apps into your products to connect users with their data, helping customers integrate Google Apps with other parts of their Enterprise IT systems, or are simply customizing your own Google Apps environment-- we want to meet you. Drop us a line on Google+ or Twitter and let us know where you'll be.
Here's our schedule:
Here's who's visiting:
“@FOOD”
“@WEIGH”
