Tag Archives: python

Formatting cells with the Google Sheets API

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!

Budou: Automatic Japanese line breaking tool

Today we are pleased to introduce Budou, an automatic line breaking tool for Japanese. What is a line breaking tool and why is it necessary? English uses spacing and hyphenation as cues to allow for beautiful, aka more legible, line breaks. Japanese, which has none of these, is notoriously more difficult. Breaks occur randomly, usually in the middle of a word.

This is a long standing issue in Japanese typography on the web, and results in degradation of readability. We can specify the place which line breaks can occur with CSS coding, but this is a non-trivial manual process which requires Japanese vocabulary and knowledge of grammar.


Budou automatically translates Japanese sentences into organized HTML code with meaningful chunks wrapped in non-breaking markup so as to semantically control line breaks. Budou uses Cloud Natural Language API to analyze the input sentence, and it concatenates proper words in order to produce meaningful chunks utilizing PoS (part-of-speech) tagging and syntactic information. Budou outputs HTML code by wrapping the chunks in a SPAN tag. By specifying their display property as inline-block in CSS, semantic units will no longer be split at the end of a line.

Budou is a simple Python script that runs each sentence through the Cloud Natural Language API. It can easily be extended as a custom filter for template engines, or as a task for runners such as Grunt and Gulp. The latest version also caches the response so no duplicate requests are sent. If you are using Budou for a static website, you can process your HTML code before deployment.

Budou is aimed to be used in relatively short sentences such as titles and headings. Screen readers may read a sentence by splitting the chunks wrapped by SPAN tag or split by WBR tag, so it is discouraged to use Budou for body paragraphs.

As of October 2016, the Cloud Natural Language API supports English, Spanish, and Japanese, and Budou currently only supports Japanese. Support for other Asian languages with line break issues, such as Chinese and Thai, will be added as the API adds support.

Any comments and suggestions are welcome. You can find us on GitHub.

By Shuhei Iitsuka, UX Engineer

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!

Introducing a new developer show "Machine Learning: Recipes for New Developers"

Posted by Josh Gordon, Developer Advocate

To help you get started building applications with machine learning, we’re excited to launch a new developer show, Machine Learning: Recipes for New Developers. In the first few episodes, we’ll teach you the ropes of machine learning without requiring any major prerequisites (like calculus). As the series progresses, we’ll walk you from “Hello World” to solving some real world problems.

Episodes will generally publish bi-weekly, and be only about 5-10 minutes in length to keep the material lightweight. Occasionally, we’ll have guests on the show who work with machine learning on different teams around Google.

Ep #1: Hello World.

  • Six lines of Python is all it takes to write your first machine learning program! In this episode, we'll briefly introduce what machine learning is and why it's important. Then, we'll follow a recipe for supervised learning (a technique to create a classifier from examples) and code it up.

Also: Coffee with a Googler came to NYC! Laurence and Josh talk about the importance of machine learning for developers, and reducing barriers to machine learning education. Check out the video!

‘Decorating’ your Python DFP API applications

Python has tons of cool idioms and features that are often overlooked or underutilized. List comprehensions to cut back on the use of unnecessary loops, decorators to wrap functions with annotations, and generator functions are just some that can be applied to working with the DFP API.

In this post, we'll tackle one of our most asked questions using decorators: "Why am I running into CONCURRENT_MODIFICATION or QUOTA_EXCEEDED errors, and how do I avoid this?".

Addressing these errors using decorators

CONCURRENT_MODIFICATION and QUOTA_EXCEEDED errors are similar in nature - the requests you’re making are failing, but not necessarily because the data you’re sending over is bad. In the first case, one of the objects you’re trying to modify is being updated elsewhere, but you likely want to try again after pulling down the same set of objects. You could certainly write code that retries your operations in all of your services for each object, but it may get a bit hard to maintain (especially with duplicated code). A much cleaner implementation would be to use a decorator!

The Python wiki has an entry under the decorators section that shows how a generic decorator might work for retrying a call. With a few modifications, we can tailor this to capture the two types of errors that might arise:

  import time
from functools import wraps

RESPONSES_TO_RETRY = ['CONCURRENT_MODIFICATION', 'QUOTA_EXCEEDED']

def retry(tries=4, delay=3, backoff=2):
''' Decorator that implements an exponential backoff for retrying on errors.

Args:
tries: int number of times to execute the wrapped function before failing
delay: int time to delay in seconds before the FIRST retry
backoff: int multiplier to extend the initial delay by for each retry
'''
def decorated_function_with_retry(func):
@wraps(func)
def function_to_retry(*args, **kwargs):
local_tries, local_delay = tries, delay
while local_tries > 1:
try:
return func(*args, **kwargs)
except Exception, e:
if [response for response in RESPONSES_TO_RETRY
if response in e.fault['faultstring']]:
print '%s, Retrying in %d seconds...' % (str(e),
local_delay)
time.sleep(local_delay)
local_tries -= 1
local_delay *= backoff
return func(*args, **kwargs)
return function_to_retry
return decorated_function_with_retry

Say you were making a call to update line items - with large networks, it’s not unlikely that someone might be editing the line item at the same time. Since you’d want to pull down the most recent copy of the line item any time the update fails, you would want to abstract out the update method to include the getLineItemsByStatement call, e.g.,

  @retry()
def fetch_and_update_line_item(statement):
# call to get the line item in question
response = line_item_service.getLineItemsByStatement(
statement.ToStatement())

updated_line_items = []
if 'results' in response:
for line_item in response['results']:
# Do something with your line items here and add them to
# updated_line_items.

line_item_service.updateLineItems(updated_line_items)

This would effectively allow you to, in the event of the update failing due to concurrent modification, pull down and update a new copy of the line item. Using the default constructor will retry 4 times with 3, 6, and 12 second delays in between.

To wrap things up, decorators are incredibly useful constructs in Python and are useful for the DFP API for several reasons:

  • Your application will be less flaky and less affected by intermittent application issues.
  • You’re less likely to run into quota errors.
  • This would prevent overwriting other changes (in the case of retrying failed calls on concurrent modification errors).
  • You could also use something like this to log errors on your end, which could help reveal poor code health or inefficient processes.

Make use of decorators in your code, and you'll soon be sitting pretty.