How to Create Personalized Documents from a Google Spreadsheet in a Easy Way

Introducing Document Studio, a powerful Google add-on that lets you effortlessly generate documents and reports using merge data stored inside Google Sheets. It can also create documents with live data from Google Forms submissions. The generated documents can be automatically sent inside personalized email messages using built-in Mail Merge.

There’s zero learning curve and you can work inside the familiar Google Docs environment. The documents can be produced in PDF, Word, Excel, PowerPoint and several other formats. The generated files are uploaded to your Google Drive, they can be instantly shared and there’s also an option to send files directly to the printer via Google Cloud Print.

The uses cases are endless. Businesses can streamline workflows and send professional looking invoices to customers. A school teacher can print individual exam certificates of every student in her classs. An instructor can generate personalized training handouts for attendees.

Getting Started with Document Studio

It takes 3 easy steps to get started.

1.) Create a template inside Google Docs, Google Sheets or Google Slides and add placeholders, such as {{Name}} or {{Address}}, for merge fields.

2.) Create a Google Sheet with the source data. The column headings will correspond to the placeholders in the template while the rows represent the records.

3.) Install the document merge add-on and follow the wizard to produce the documents. Document Studio will create one document per row in the sheet.

Document Studio includes a WYSIWYG email editor to help you send the merged documents to one or more recipients inside personalized email messages.

If your template is a Google Spreadsheet and it contains any formulas, they are re-calculated and evaluated in the generated document. You can also include unique images, Google Maps, QR Code images and PayPal payment links in generated documents with the help of simple spreadsheet functions.

In addition to document merge, the add-on can also be used for creating documents in real-time from Google Forms submissions.

Thanks for landing on this post, If you like my post then give your opinion and feedback in comment box.

TAGS:- #How to Create Personalized Documents from a Google Spreadsheet in Minutes, #Create Personalized Documents from a Google Spreadsheet in Minutes, #How to Create Personalized Documents from a Google Spreadsheet, #Create Personalized Documents from a Google Spreadsheet, #How to Create Personalized Documents from a Google Spreadsheet in a Easy Way, #Create Personalized Documents from a Google Spreadsheet in a Easy Way, #Personalized Documents from a Google Spreadsheet, #google sheets script examples, #google spreadsheet script tutorial, #google sheets custom functions, #google sheets script editor, #google sheets script functions, #google apps script call function, #google sheets macro button, #google script examples

Read more »

Build a Web Page Monitor with Google Docs using ImportXML

RSS feeds have completely changed the way we consume information on the web. You no longer have to visit The New York Times or CNN every hour of the day to check news headlines because the feed reader is doing that for you behind the scenes.

The only problem is that not all web content is available via feeds. For instance, Amazon, eBay and Google Product Search (Froggle) are good places to find discount deals on books and electronic gadgets but unfortunately, none of these shopping sites publish feeds.

MONITOR HTML WEB PAGES WITH GOOGLE DOCS

Problem: Let’s say you are looking for some discount deals on iPod Nano. An option here is that you open Google Products page and search for iPod Nano. If you don’t find the right price, repeat the same cycle next day. This may sound easy but imagine doing this for ten other products on five different shopping sites. Tedious, right?

Solution: What we can do here is build a simple spreadsheet in Google Docs that will monitor prices across all these search pages and will present them in a table so you don’t just track prices but also compare them at the same time.

To get started, you need access to Google Docs and some basic knowledge of XPath. Don’t let this scare you – XPath is a simple way to access information contained inside HTML web pages. For instance, if you want to know about all URLs that are mentioned on any web page, the XPath expression would be //a[@href]. Some more examples:

//strong means all the items in the web page with strong html tags

//@href means all the items in the web page with href element, i.e., the URLs in that page.

If you feel that writing XPath expressions is a tricky job, get the XPath checker add-on for Firefox that will help you easily determine the XPath of any element on a web page.

SCRAP WEB PAGES WITH GOOGLE DOCS USING IMPORTXML AND XPATH

This is the search page for ‘ipod nano’ inside Google Products. As you may noticed already, the result title is formatted with CSS class “ps-large-t” while the the product price using the class “ps-larger-t” – you can easily find these class names via Firebug or   from the HTML source.

Now we’ll create a table inside Google spreadsheet that will have the name, price and URL that will link to that product listing in Google Docs. You can use the same approach to get product data from other sites like Amazon, eBay, Buy.com, etc.

Here’s how the final spreadsheet looks like – all this is live data and will update automatically if the corresponding information is updated on Google Products.

GET EXTERNAL DATA IN GOOGLE DOCS WITH IMPORTXML

There’re built-in spreadsheet functions to help you easily import external data into Google Docs. One such useful function is ImportXML that, like ImportHTML, can be used for screen-scrapping.

The syntax is =ImportXML("web page URL", "XPath Expression")

Coming back to the spreadsheet, in order to fetch the price of ‘ipod nano’, we type the following formula:

=ImportXML("google.com/products?q=<strong>ipod+nano</strong>",
                "//b[@class='ps-larger-t']")

You may replace ‘ipod nano’ with any other product name like ‘harry+potter’, ‘nikon+d60’, etc.

To enter this function into Google Docs, click an empty cell, press F2 and paste. See this Google Docs movie:

Similarly, for the product name, we use this formula:

=ImportXML("www.google.com/products?q=ipod+nano",
                "//a[@class='ps-large-t']")

And for the URL (product hyperlink), the formula is:

=ImportXML("http://www.google.com/products?q=ipod+nano",
                "//a[@class='ps-large-t']//@href")

You need to concatenate this with ‘http://www.google.com’ since Google Products uses relative URLs. This can be easily fixed by adding another column with the formula

=HYPERLINK("http://www.google.com/"&B3,"click here")

SUBSCRIBE TO WEB PAGE CHANGES VIA FEEDS

You don’t have to check this Google Docs Spreadsheet manually to see if prices have since yesterday – just select publish     followed by “Automatically re-publish when changes are made” and subscribe to the document in your favorite RSS reader.

Read more »

Scan and Upload Paper Documents to Google Docs

Scan Drop is a free document scanning utility that will help you scan and upload paper documents directly to Google Docs.It works something like this. You download and install the app and then sign-in using your Google Account.

Assuming that the scanner /multifunction printer is connected to your computer, you press the scan button inside Scan Drop and it will scan the papers in the queue and will convert then all into  one PDF.

You can make some basic changes to this scanned PDF like you can re-order pages or even rotate them in case the orientation was wrong at the time of scanning. Now press upload and this PDF will be copied from the computer to any of the selected folders in your Google account.

ScanDrop is compatible with almost all types of scanners and you can use it on any Windows 7, XP or Vista computer. The only downside is that your paper documents are uploaded as scanned images (no OCR) and therefore they won’t be searchable inside Google Docs.

Read more »

Add Stock Photos to your Google Documents

Google Docs is integrated with Google Image Search to help you quickly find and insert web photographs in your documents. The results are filtered to only show images that have been licensed under Creative Commons and labeled for commercial reuse so  you are legally allowed to insert these images in your documents.

Other than Creative Commons, there’s a new image search option available in Google Docs that will help you find professionally shot photographs for your documents. Google has partnered with stock photography services like Photos.com to offer royalty-free stock images inside Google Docs, just like Microsoft PowerPoint.

HOW TO INSERT STOCK IMAGES IN GOOGLE DOCS

While you are inside the edit view of a document, go to Insert – > Images – > Stock Photos and use the search function. The collection of stock photographs available inside Google Docs isn’t as big as what is offered by Microsoft Office but that should possibly change once Google signs up with more partners.

Other than Google Documents, the option to insert stock photographs is available in Presentations and Google Spreadsheets as well.

Read more »

How to Use Google Docs with Microsoft Office

While there’s plenty of overlap between desktop-based Microsoft Office and web-based Google Docs, each tool has its own strong points.

For instance, Microsoft Office offers excellent tools for you to create and edit documents, presentations and spreadsheets in an offline environment. Google Docs lets can easily view and edit your Office documents using the browser itself – this comes handy when you aren’t carrying your laptop computer (that has all the documents) or are using a device that doesn’t have Microsoft Office (like your mobile phone).

One tool is good for creating documents while the other helps you access documents on the go and thus it is makes good sense to use both these tools together.

INTEGRATING MICROSOFT OFFICE WITH GOOGLE DOCS

An idle workflow would be that you create your document in Microsoft Office and it instantly becomes available in Google Docs (and vice-versa). Also, if you make any edits in the cloud, they should sync with the copy on the desktop.

Now there are quite a few tools that let you integrate Microsoft Office with Google Docs though, because of certain limitations in the Google Docs API, two-way synchronization between the two Office products is still far from perfect. Here are some tools worth a try:

1. Google Cloud Connect – This is a free plugin for Microsoft Office that will let you save your Office documents to your Google Docs account with a   click.

Compose a new document inside Word, Excel or PowerPoint and hit the Sync button to upload it to your Google account. This is a one-way street – if you make any edits to your documents in the cloud, those changes won’t be available locally.

2. Syncplicity – Syncplicity is a Dropbox-like online file storage and synchronization service that also supports Google Docs .

Link your Gmail or Google Apps account with Syncplicity and then designate a local desktop folder that should be kept in sync with Google Docs. Now when you save a new document, spreadsheet or presentation to this local folder, a copy of it will automatically get stored to your Google Docs account. Similarly, if you create a new document inside Google Docs, it will become available inside your local folder automatically.

3. Gladinet Starter – With the help of Gladinet Desktop, you can quickly and easily ‘mount’ your Google Docs account as a virtual drive (say Z:) and access your online Google documents as if they were residing on the local hard drive.

You can then double-click any document in this virtual folder to edit with the corresponding Office program. Any files saved to this folder are uploaded to Google Docs. You can also drag-n-drop files to this virtual folder and they’ll get uploaded in a batch to your online Google Docs account. Similarly, dragging files out of this folder will create a local backup of your Google Docs.

4. Insync – This is relatively new service that, like Syncplicity, provides offline access to your Google Docs files and can also sync your local document folder with the cloud. When you add or edit a file on the local desktop, it will automatically sync to Google Docs and vice-versa.

The desktop client for Insync is available for both Mac and Windows. The software has promising features but the version I tested for this comparison was buggy and did not work as advertised. Google Apps users may jump to Insync right away while there’s a waiting list for Gmail users.

Final thoughts – If you can live with that ugly-looking toolbar, Google Cloud Connect is neat because it saves Office documents to Google Docs in their native format. However, if you also need access to your old Google Docs files from within Microsoft Office, Offisync or Gladinet Cloud Desktop are good choices.

That said, when you save a document created in Microsoft Office to Google Docs, the original formatting is less likely to be preserved. That’s one area where the Office Web Apps and SkyDrive duo seems to enjoy an upper hand.

Read more »

Add the Same File to Multiple Folders in Google Drive without Copying

Gmail works around the concept of tags (or labels) and any email message can belong to one or more tags. Google Drive has folders instead of tags and thus any file or folder in Drive can have a single parent folder. For instance, if you have uploaded a presentation file in Folder-A, it can’t simultaneously exist in Folder-B. Right?

Well, you’ll will be surprised to know that Google Drive does allow you to place any file inside one or more folders without you having to create multiple copies of that file. This makes Drive organization easier and if you edit the file inside one folder, all the other instances are updated as well since they are essentially pointing to the same file.

How to Add a File to Multiple Folders in Google Drive

Here’s how you can place existing files or folders inside different multiple folders on Google Drive without making copies of the file.

 

Step-1. Open the Google Drive website in your desktop’s web browser and select one or more files or folders. You can use the Control key on Windows, or Command key on Mac, to select non-consecutive files and folders. 

Step-2. Now press Shift + Z and you’ll see an “Add to Folder” pop-up (see screenshot).  

Step-3. Next select the folder where you wish to add the selected files and click OK.

That’s it.

You have neither copied nor moved the files to the destination folder, you’ve merely created references or aliases to files inside the other folder. You can use the Shift+Z keyboard shortcut again to add the selected files to any other folders in your Google Drive.

This little feature will come handy in several cases. For instance, if you have a folder of pictures inside Google Drive, you can use Shift+Z to place some of these pictures into another shared folder. You need not create duplicate files in your Drive (saving storage space) and if you remove a picture from the parent folder, the file is gone from other folders too.

Remove Files Placed in Multiple Folders

Let’s say you have a Folder B that contains references to a file placed in Folder A. If you move the Folder B to trash or if you remove the file from Folder B, the original file is deleted from the original Folder A as well. In such cases you may need to remove the placed file from Folder B before deleting the folder.

In Google Drive, select the file that is placed in multiple folders and open the activity sidebar. Here you’ll see a list of all folder that the file belongs to. All you need to do is click the little [x] symbol to remove that file from any folder in the list.

Add Files to Multiple Folders with Code

If you know Google Scripts, you can place a file or folder current folder into multiple folders using the Drive API as show below. [H/t David Scotts]

function organizeFolders() {
 
  // Parent Folders
  var parentA = DriveApp.createFolder("Dad"); 
  var parentB = DriveApp.createFolder("Mom");
 
  // Child folder inside Parent Folder A
  var child   = parentA.createFolder("Child");
 
  // Place Child Folder inside another Parent Folder B 
  parentB.addFolder(child);
 
}

Read more »

How to Insert Images in your Google Spreadsheet Cells

While it has always been possible to add images to any sheet inside of your Google Spreadsheets, you can also insert images and icons inside individual spreadsheet cells as well.

To add an image to a cell, highlight the cell and click F2 to enter the formula mode and then type =image(“URL”) where URL is the public web address of that image. For instance, the following formula will insert a free image from Unsplash in your Google Spreadsheet.

=IMAGE("https://img.labnol.org/di/high-quality-photo.jpg")

Google Spreadsheets, by default, will scale the image to fit inside the area of the selected cell but you can easily change the default settings by adding another parameter to the image function.

For instance, a formula like =image(“URL”, 2) will stretch the image to fit inside the selected cell while =image(“URL”, 3) will insert the image into the cell at its original size.

You can also specify the existing height and width of the image in pixels by setting the mode to 4. The technique should be very handy for embedding social sharing icons in your spreadsheets.

=IMAGE("URL", 4, 300, 250)

If you are getting parsing errors, you are either using a non-existent image or you may have missing adding quotes around the image URL inside the image() formula.

Read more »

How to Write an Add-on for Google Docs

You have seen examples of some really useful add-ons for Google Docs but wouldn’t it be great if you could write your own add-on, one that adds new features to your Google Docs, one that makes you a rock star among the millions of Google Docs users.

Well, it ain’t that difficult. If you know some HTML, CSS and JavaScript, you can create a Google Docs add-on.

CREATE A GOOGLE ADD-ON FOR DOCS & SHEETS

This step-by-step tutorial will walk you through the process of creating your own add-on for Google Docs. The add-on used in the demo lets you insert a image of any address on Google Maps inside a Google Document without requiring any screen capture software.

Ok, lets’s get going.

Step 1. Open a new document inside Google Drive and choose Tools -> Script Editor. This is the Apps Script IDE where we’ll write the code for the add-on.

Step 2. Choose File -> New HTML to create a new HTML file inside the Script Editor and name your file as googlemaps.html (or anything you like).

Step 3. Copy-paste the following code in the HTML file and save your changes. This is the code that will be used to render the sidebar in your Google Documents.

<!-- Use this CSS stylesheet to ensure that add-ons styling 
     matches the default Google Docs styles -->
<link href="https://ssl.gstatic.com/docs/script/css/add-ons.css"
     rel="stylesheet"> 

<!-- The sidebar will have a input box and the search button -->
<div class="sidebar">
    
    <!-- The search box for Google Maps -->
    <div class="block form-group">
        <input type="text" id="search" placeholder="Enter address.. " />
        <button class="blue" id="load_maps">Search Google Maps</button>
    </div>
    
    <!-- The container for the Google Maps static image -->
    <div id='maps'></div>

</div>

<!-- Load the jQuery library from the Google CDN -->
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js">
</script>

<script>
 // Attach click handlers after the Sidebar has loaded in Google Docs
 $(function() {

   // Use Static Maps to generate an image of the address entered by the user 
   $('#load_maps').click(function() {
     var mapURL = 'https://maps.googleapis.com/maps/api/staticmap?center=' 
                +  encodeURIComponent($('#search').val()) 
                +  '&zoom=14&size=200x400&sensor=false';
     $('#maps').html('<img src=" ' + mapURL + ' "/>');
   });
   
   // If the user presses the Enter key in the search box, perform a search
   $('#search').keyup(function(e) {
      if (e.keyCode === 13) {
         $('#load_maps').click();
      }
   });

   // When a user clicks the thumbnail image in the sidebar, call 
   // insertGoogleMap to insert the maps image in the current document
   $('#maps').click(function() {
     google.script.run.insertGoogleMap($('#search').val());
   });
   
 });

Step 4. Next we will write the server side JavaScript (Google Script) that will actually render the sidebar and insert Google Maps images in the document.

/* What should the add-on do after it is installed */
function onInstall() {
  onOpen();
}

/* What should the add-on do when a document is opened */
function onOpen() {
  DocumentApp.getUi()
  .createAddonMenu() // Add a new option in the Google Docs Add-ons Menu
  .addItem("Google Maps", "showSidebar")
  .addToUi();  // Run the showSidebar function when someone clicks the menu
}

/* Show a 300px sidebar with the HTML from googlemaps.html */
function showSidebar() {
  var html = HtmlService.createTemplateFromFile("googlemaps")
    .evaluate()
    .setTitle("Google Maps - Search"); // The title shows in the sidebar
  DocumentApp.getUi().showSidebar(html);
}

/* This Google Script function does all the magic. */
function insertGoogleMap(e) {  
  var map = Maps.newStaticMap()
    .setSize(800, 600) // Insert a Google Map 800x600 px
    .setZoom(15)
    .setCenter(e); // e contains the address entered by the user
  
  DocumentApp.getActiveDocument()
    .getCursor() // Find the location of the cursor in the document
    .insertInlineImage(map.getBlob()); // insert the image at the cursor
}

Save your changes and then choose onOpen from the Run menu inside the Script editor. Authorize the script and switch to your Google Document.

You’ll see a new Google Maps option under the Add-ons menu. Select the menu item and you’ll be able to insert maps images inside your Google Documents without using any screen capture software.

SHARE YOUR GOOGLE ADD-ONS WITH OTHER GOOGLE DOCS USERS

Now that your first Google add-on is ready, you may want to distribute it to other users of Google Docs. The easiest option would be that you share your document with public and set the permission as Anyone can view. Now anyone can create a copy of your document in their own Google Drive and use your add-on.

Google Add-ons can also be published to the Chrome store, the process is similar to publishing Chrome extensions, but this isn’t available to all Google developers yet.

Read more »

How to Turn Google Docs into an RSS Reader

This tutorial shows how to use a spreadsheet in Google Docs as an RSS Feed reader. You can fetch RSS news feeds from different sources into one spreadsheet – much like a simpler version of Feedly – and then publish your feeds as a public web page. No coding required.

If you have a website, you can use the same trick to embed RSS feeds in web pages. The Google Docs approach is preferred over Flash or Javascript widgets because here you have complete control over the layout and the embedded content will show up on mobile devices too.

IMPORTFEED – FETCH RSS FEEDS IN GOOGLE DRIVE

Here are the complete step involved for putting RSS feeds into Google Docs. We are using a single RSS feed in the following example but your spreadsheet can have dozens of RSS feeds.

1. Open a new Google spreadsheet and type the RSS feed URL in cell A1.

2. In cell A2, type =ImportFeed(A1, “items title”, FALSE, 10) – This formula will fill column A with the titles of the latest 10 stories in that feed. Make sure that the second parameter – items title – is in lower case.

3. In cell B2, type =ImportFeed(A1, “items url”, FALSE, 10) – This will fill column B with the URL (permalink) of the corresponding stories in column A.

4. In cell C2, type =HyperLink(B2, A2) – This will combine the title and URL to make a clickable hyperlink in the spreadsheet.

5. Copy the same formula that you entered into Cell C2 into cells C3 until C11.

EMBED RSS FEEDS INTO WEB PAGES WITH GOOGLE DOCS

Since we are using a regular spreadsheet, you can change the fonts, colors and other formatting. To embed the feed in web page, go to to Publish – > More publishing options in the Google Spreadsheets menu, select C2:C13 as the range and get the HTML embed code.

Google Docs will automatically refresh content when the underlying RSS feeds are changed

Read more »

How to Color Alternate Rows in Google Sheets

Google Sheets do not support zebra stripes (yet) but you can use conditional formatting combined with a simple Google Formula to create a formatted table. You can apply alternating colors to both rows and columns in Google Sheets easily.

Here’s the trick.

Open a Google Sheet and choose Conditional formatting from the Format menu. Select Custom Formula from the dropdown and put this formula in the input box.

Select a Background color for the rule and set the range in A1 notation. For instance, if you wish to apply alternating colors to rows 1 to 100 for columns A to Z, set the range as A1:Z100.

Click the “Add another rule” link and repeat the steps but set =ISODD(ROW()) as the custom formula and choose a different background color. Save the rules and the zebra stripes would be automatically applied to the specified range of cells.

Tip: If you wish to extend this technique to format columns with different colors, use the =ISEVEN(COLUMN()) formula. Simple!

Read more »