Tag Archives: spreadsheets

Generating slides from spreadsheet data

Posted by Wesley Chun (@wescpy), Developer Advocate, G Suite

The G Suite team recently launched the very first Google Slides API, opening up a whole new set of possibilities, including leveraging data already sitting in a spreadsheet or database, and programmatically generating slide decks or slide content based on that data. Why is this a big deal? One of the key advantages of slide decks is that they can take database or spreadsheet data and make it more presentable for human consumption. This is useful when the need arises to communicate the information reflected by that data to management or potential customers.

Walking developers through a short application demonstrating both the Sheets and Slides APIs to make this happen is the topic of today's DevByte video. The sample app starts by reading all the necessary data from the spreadsheet using the Sheets API. The Slides API takes over from there, creating new slides for the data, then populating those slides with the Sheets data.

Developers interact with Slides by sending API requests. Similar to the Google Sheets API, these requests come in the form of JSON payloads. You create an array like in the JavaScript pseudocode below featuring requests to create a cell table on a slide and import a chart from a Sheet:


var requests = [
   {"createTable": {
       "elementProperties":
           {"pageObjectId": slideID},
       "rows": 8,
       "columns": 4
   }},
   {"createSheetsChart": {
       "spreadsheetId": sheetID,
       "chartId": chartID,
       "linkingMode": "LINKED",
       "elementProperties": {
           "pageObjectId": slideID,
           "size": {
               "height": { ... },
               "width": { ... }
           },
           "transform": { ... }
       }
   }}
];
If you've got at least one request, say in a variable named requests (as above), including the Sheet's sheetID and chartID plus the presentation page's slideID. You'd then pass it to the API with just one call to the presentations().batchUpdate() command, which in Python looks like the below if SLIDES is your API service endpoint:
SLIDES.presentations().batchUpdate(presentationId=slideID,
       body=requests).execute()

Creating tables is fairly straightforward. Creating charts has some magical features, one of those being the linkingMode. A value of "LINKED" means that if the Sheet data changes (altering the chart in the Sheet), the same chart in a slide presentation can be refreshed to match the latest image, either by the API or in the Slides user interface! You can also request a plain old static image that doesn't change with the data by selecting a value of "NOT_LINKED_IMAGE" for linkingMode. More on this can be found in the documentationon creating charts, and check out the video where you'll see both those API requests in action.

For a detailed look at the complete code sample featured in the video, check out the deep dive post. We look forward to seeing the interesting integrations you build with the power of both APIs!

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!

Enhanced third-party access protection for Google Sheets

Posted by Tom Holman, Product Manager, Google Sheets and Josh Danziger, Software Engineer, Google Sheets

At Google, we are always working to keep our users' information safe. As part of these ongoing efforts, we will begin requiring explicit authorization when third-party sites request access to Google Sheets content via the Google Visualization API or Google Query Language.

For many developers, this change will be transparent, but others may need to make changes in order to continue reading spreadsheet data. For more details on the technical changes required, please visit the Google Charts API Documentation. We will begin enforcing these requirements on September 14, 2016.

If you have any questions or concerns about this change, please follow up in the Google Docs forum or on Stack Overflow.