Tag Archives: Google Spreadsheets API

Mail merge with the Google Docs API

Posted by Wesley Chun, Developer Advocate, Google Cloud

Students and working professionals use Google Docs every day to help enhance their productivity and collaboration. The ability to easily share a document and simultaneously edit it together are some of our users' favorite product features. However, many small businesses, corporations, and educational institutions often find themselves needing to automatically generate a wide variety of documents, ranging from form letters to customer invoices, legal paperwork, news feeds, data processing error logs, and internally-generated documents for the corporate CMS (content management system).

Mail merge is the process of taking a master template document along with a data source and "merging" them together. This process makes multiple copies of the master template file and customizes each copy with corresponding data of distinct records from the source. These copies can then be "mailed," whether by postal service or electronically. Using mail merge to produce these copies at volume without human labor has long been a killer app since word processors and databases were invented, and now, you can do it in the cloud with G Suite APIs!

While the Document Service in Google Apps Script has enabled the creation of Google Docs scripts and Docs Add-ons like GFormit (for Google Forms automation), use of Document Service requires developers to operate within the Apps Script ecosystem, possibly a non-starter for more custom development environments. Programmatic access to Google Docs via an HTTP-based REST API wasn't possible until the launch of the Google Docs API earlier this year. This release has now made building custom mail merge applications easier than ever!

Today's technical overview video walks developers through the concept and flow of mail merge operations using the Docs, Sheets, Drive, and Gmail APIs. Armed with this knowledge, developers can dig deeper and access a fully-working sample application (Python), or just skip it and go straight to its open source repo. We invite you to check out the Docs API documentation as well as the API overview page for more information including Quickstart samples in a variety of languages. We hope these resources enable you to develop your own custom mail merge solution in no time!

Formatting cells with the Google Sheets API

Originally posted on G Suite Developers Blog
Posted by Wesley Chun (@wescpy), Developer Advocate, G Suite
At Google I/O earlier this year, we launched a new Google Sheets API (click here to watch the entire announcement). The updated API includes many new features that weren't available in previous versions, including access to more functionality found in the Sheets desktop and mobile user interfaces. Formatting cells in Sheets is one example of something that wasn't possible with previous versions of the API and is the subject of today's DevByte video.
In our previous Sheets API video, we demonstrated how to get data into and out of a Google Sheet programmatically, walking through a simple script that reads rows out of a relational database and transferring the data to a new Google Sheet. The Sheet created using the code from that video is where we pick up today.

Formatting spreadsheets is accomplished by creating a set of request commands in the form of JSON payloads, and sending them to the API. Here is a sample JavaScript Object made up of an array of requests (only one this time) to bold the first row of the default Sheet automatically created for you (whose ID is 0): 

{"requests": [
    {"repeatCell": {
        "range": {
            "sheetId": 0,
            "startRowIndex": 0,
            "endRowIndex": 1
        },
        "cell": {
            "userEnteredFormat": {
                "textFormat": {
                    "bold": true
                }
            }
        },
        "fields": "userEnteredFormat.textFormat.bold"
    }}
]}
With at least one request, say in a variable named requests and the ID of the sheet as SHEET_ID, you send them to the API via an HTTP POST to https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}:batchUpdate, which in Python, would be a single call that looks like this: 
SHEETS.spreadsheets().batchUpdate(spreadsheetId=SHEET_ID,
        body=requests).execute()

For more details on the code in the video, check out the deepdive blog post. As you can probably guess, the key challenge is in constructing the JSON payload to send to API calls—the common operations samples can really help you with this. You can also check out our JavaScript codelab where we guide you through writing a Node.js app that manages customer orders for a toy company, featuring the toy orders data we looked at today but in a relational database. While the resulting equivalent Sheet is featured prominently in today's video, we will revisit it again in an upcoming episode showing you how to generate slides with spreadsheet data using the new Google Slides API, so stay tuned for that!

We hope all these resources help developers enhance their next app using G Suite APIs! Please subscribe to our channel and tell us what topics you would like to see in other episodes of the G Suite Dev Show!

Introducing the Google Sheets API v4: Transferring data from a SQL database to a Sheet

Posted by Wesley Chun (@wescpy), Developer Advocate, Google Apps

At Google I/O 2016, we launched a new Google Sheets API—click hereto watch the entire announcement. The updated API includes many new features that weren’t available in previous versions, including access to functionality found in the Sheets desktop and mobile user interfaces. My latest DevBytevideo shows developers how to get data into and out of a Google Sheet programmatically, walking through a simple script that reads rows out of a relational database and transferring the data to a brand new Google Sheet.

Let’s take a sneak peek of the code covered in the video. Assuming that SHEETS has been established as the API service endpoint, SHEET_ID is the ID of the Sheet to write to, and datais an array with all the database rows, this is the only call developers need to make to write that raw data into the Sheet:


SHEETS.spreadsheets().values().update(spreadsheetId=SHEET_ID,
    range='A1', body=data, valueInputOption='RAW').execute()
Reading rows out of a Sheet is even easier. With SHEETS and SHEET_ID again, this is all you need to read and display those rows: 
rows = SHEETS.spreadsheets().values().get(spreadsheetId=SHEET_ID,
    range='Sheet1').execute().get('values', [])
for row in rows:
    print(row)

If you’re ready to get started, take a look at the Python or other quickstarts in a variety of languages before checking out the DevByte. If you want a deeper dive into the code covered in the video, check out the post at my Python blog. Once you get going with the API, one of the challenges developers face is in constructing the JSON payload to send in API calls—the common operations samples can really help you with this. Finally, if you’re ready to get going with a meatier example, check out our JavaScript codelab where you’ll write a sample Node.js app that manages customer orders for a toy company, the database of which is used in this DevByte, preparing you for the codelab.

We hope all these resources help developers create amazing applications and awesome tools with the new Google Sheets API! Please subscribe to our channel, give us your feedback below, and tell us what topics you would like to see in future episodes!

Introducing the Google Sheets API v4: Transferring data from a SQL database to a Sheet

Posted by Wesley Chun (@wescpy), Developer Advocate, Google Apps

At Google I/O 2016, we launched a new Google Sheets API—click hereto watch the entire announcement. The updated API includes many new features that weren’t available in previous versions, including access to functionality found in the Sheets desktop and mobile user interfaces. My latest DevBytevideo shows developers how to get data into and out of a Google Sheet programmatically, walking through a simple script that reads rows out of a relational database and transferring the data to a brand new Google Sheet.

Let’s take a sneak peek of the code covered in the video. Assuming that SHEETS has been established as the API service endpoint, SHEET_ID is the ID of the Sheet to write to, and datais an array with all the database rows, this is the only call developers need to make to write that raw data into the Sheet:


SHEETS.spreadsheets().values().update(spreadsheetId=SHEET_ID,
    range='A1', body=data, valueInputOption='RAW').execute()
Reading rows out of a Sheet is even easier. With SHEETS and SHEET_ID again, this is all you need to read and display those rows: 
rows = SHEETS.spreadsheets().values().get(spreadsheetId=SHEET_ID,
    range='Sheet1').execute().get('values', [])
for row in rows:
    print(row)

If you’re ready to get started, take a look at the Python or other quickstarts in a variety of languages before checking out the DevByte. If you want a deeper dive into the code covered in the video, check out the post at my Python blog. Once you get going with the API, one of the challenges developers face is in constructing the JSON payload to send in API calls—the common operations samples can really help you with this. Finally, if you’re ready to get going with a meatier example, check out our JavaScript codelab where you’ll write a sample Node.js app that manages customer orders for a toy company, the database of which is used in this DevByte, preparing you for the codelab.

We hope all these resources help developers create amazing applications and awesome tools with the new Google Sheets API! Please subscribe to our channel, give us your feedback below, and tell us what topics you would like to see in future episodes!

Build add-ons for Google Docs and Sheets

We've just announced Google Docs and Sheets add-ons — new tools created by developers like you that give Google users even more features in their documents and spreadsheets. Joining the launch are more than 60 add-ons that partners have built using Apps Script. Now, we're opening up the platform in a developer-preview phase. If you have a cool idea for Docs and Sheets users, we'd love to publish your code in the add-on store and get it in front of millions of users.


To browse through add-ons for Docs and Sheets, select Get add-ons in the Add-ons menu of any document or spreadsheet. (Add-ons for spreadsheets are only available in the new Google Sheets).


Under the hood

Docs and Sheets add-ons are powered by Google Apps Script, a server-side JavaScript platform that requires zero setup. Even though add-ons are in developer preview right now, the tools and APIs are available to everyone. The only restriction is on final publication to the store.

Once you have a great working prototype in Docs or Sheets, please apply to publish. Scripts that are distributed as add-ons gain a host of benefits:

  • Better discovery: Apps Script has long been popular among programmers and other power users, but difficult for non-technical users to find and install. Add-ons let you distribute your code through a polished storefront—as well as direct links and even Google search results.
  • Sharing: When two people collaborate on a document and one of them uses an add-on, it appears in the Add-ons menu for both to see. Similarly, once you get an add-on from the store, it appears in the menu in every document you create or open, although your collaborators will only see it in documents where you use it. For more info on this sharing model, see the guide to the add-on authorization lifecycle.
  • Automatic updates: When you republish an add-on, the update pushes out automatically to all your users. There's no more hounding people to switch to the latest version.
  • Share functionality without sharing code: Unlike regular Apps Script projects, add-ons don't expose your source code for all to see. That's reassuring both to less-technical users and to the keepers of your codebase's secrets.
  • Enterprise features: If your company has its own Google Apps domain, you can publish add-ons restricted just to your employees. This private distribution channel is a great way for organizations that run on Google Apps to solve their own unique problems.

Beautiful, professional appearance

Thanks to hard work from our developer partners, the add-ons in the store look and feel just like native features of Google Docs and Sheets. We're providing a couple of new resources to help all developers achieve the same visual quality: a CSS package that applies standard Google styling to typography, buttons, and other form elements, and a UI style guide that provides great guidance on designing a Googley user experience.


A replacement for the script gallery

Add-ons are available in the new version of Google Sheets as a replacement for the older version's script gallery. If you have a popular script in the old gallery, now's a great time to upgrade it to newer technology.

We can't wait to see the new uses you'll dream up for add-ons, and we're looking forward to your feedback on Google+ and questions on Stack Overflow. Better yet, if you're free at noon Eastern time this Friday, join us live on YouTube for a special add-on-centric episode of Apps Unscripted.


Dan Lazin   profile | twitter

Dan is a technical writer on the Developer Relations team for Google Apps Script. Before joining Google, he worked as video-game designer and newspaper reporter. He has bicycled through 17 countries.