num_docs_internally_visible, num_docs_externally_visible, num_docs_shared_outside_domain.
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:
requests
sheetID
chartID
slideID
presentations().batchUpdate()
SLIDES
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 documentation on creating charts, and check out the video where you'll see both those API requests in action.
linkingMode
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 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" }} ]}
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!
Posted by Tom Holman, Product Manager, Google Sheets
Originally posted to Google Cloud
When it comes to redefining how people go about their everyday work, Google and Salesforce have shared a remarkably similar path, with our roots planted firmly in the cloud.
That's why we were very excited to share the stage last week at Dreamforce to showcase two integrations that Salesforce built on top of G Suite: Salesforce Lightning for Gmail and Sales Cloud integration with Google Sheets. In addition to existing integrations with Google Calendar, Gmail (for Salesforce IQ), Drive and Contacts, these new offerings will go a long way in helping people work effectively with smarter tools.
At Dreamforce, we showcased the upcoming Lightning for Gmail integration, which brings together our leading CRM and email services.
With this integration, sales reps can now streamline repetitive but important tasks: they can review Salesforce records relevant to their emails, add contacts from their address book into Salesforce, and even create new Salesforce records, all from within Gmail.
A pilot of Lightning for Gmail will be available by the end of this year for free to all Salesforce customers. Interested customers can contact their Salesforce account managers to sign up for the pilot program.
The Sales Cloud integration with Sheets, meanwhile, makes it easy for sales reps to link any Salesforce List View to a Google Sheet. Users can also view, edit, and delete records within Sheets and sync those changes back to Salesforce. Better still, the integration also supports your business logic and validation rules.
We gave a joint preview of the Sales Cloud and Google Sheets integration at Google I/O this summer, and today are happy to announce that it will be available in beta to all Sales Cloud customers by the end of this year.
We had a blast working with the Salesforce team to bring these new solutions to life.
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.
Posted by Wesley Chun (@wescpy), Developer Advocate, Google Apps
At Google I/O 2016, 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 functionality found in the Sheets desktop and mobile user interfaces. My latest DevByte video 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 data is 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
SHEET_ID
data
SHEETS.spreadsheets().values().update(spreadsheetId=SHEET_ID, range='A1', body=data, valueInputOption='RAW').execute()
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!
There was a time when office work used to be all about pushing physical paper. Computing and productivity tools have made things better, but workers still find themselves doing the same tasks over and over across the different apps they use: copying and pasting from a CRM app to a slide presentation, or manually exporting data from a project management app just to turn around and import it back into a spreadsheet. It’s the digital equivalent of pushing paper.
To make it easier to get the job done across multiple apps, without all the copy and paste, we’re announcing three new APIs and a new feature to help workers get to the data they need, when and where they need it.
Build seamless integrations with the new Sheets and Slides APIs
Our new APIs let developers connect their apps—and the data within them—more deeply with Google Sheets and Google Slides.
The new Sheets API gives developers programmatic access to powerful features in the Sheets web and mobile interfaces, including charts and pivot tables. For example, developers can use Sheets as part of a rich workflow that pushes data from their app into Sheets and allows users to collaborate on that data before the updated data is pulled back into the original app, removing altogether the need to copy and paste.
Teams at Anaplan, Asana, Sage, Salesforce, and SAP Anywhere are already building interesting integrations with the new Sheets API. Check out the video below to see an overview of what’s possible as well as several example integrations.
Partner integrations with the new Google Sheets API
The new Sheets API is available today. Find the developer documentation as well as a codelab to help you get started at developers.google.com/sheets.
Similar to the Sheets API, the new Slides API gives developers programmatic access to create and update presentations. For example, developers can use this API to push data and charts into Slides to create a polished report from source data in other application, ready to present.
Conga, ProsperWorks, SalesforceIQ and Trello are all building integrations with Slides using the new API. Several examples of what’s possible are in the video below.
Partner integrations with the new Google Slides API
The Slides API will be launching in the coming months, and these partner integrations will be available soon after. You can sign up for early access to the Slides API at developers.google.com/slides.
Keep your data in sync with the new Classroom API
For developers building tools and workflows for schools, the Classroom API has launched new coursework endpoints to help you build stronger integrations that keep your data in sync. Read the full announcement on the Google for Education blog, here.
Sync assignments & grades programmatically with the Google Classroom API
Say goodbye to stale data with linked charts
Finally, to make sure we can help keep all this data flowing seamlessly from app to app, users can now also embed linked charts from Sheets into Docs or Slides. The result? Once the underlying data in a spreadsheet changes, whether that change comes from an action taken in another app via the API or a collaborator, an updated chart in the corresponding presentation or document is just one click away.
Linked charts allow for easy updates in Docs & Slides
For more information, see how to add a chart to a document or to a presentation.
We can't wait to see what you build.
Posted by Matt Hessinger, Project Specialist, Google Apps Script
Welcome to our 100th blog post on Apps Script! It’s amazing how far we’ve come from our first post back in 2010. We started out highlighting some of the simple ways that you could develop with the Apps platform. Today, we’re sharing tips and best practices for developing more complex Apps Script solutions by pointing out some community contributions.
The Apps Script editor does not allow you to use your own source code management tool, making it a challenge to collaborate with other developers. Managing development, test, and production versions of a project becomes very tedious. What if you could have the best of both worlds — the powerful integration with Google’s platform that Apps Script offers, along with the development tooling and best practices that you use every day? Now, you can.
npm install -g node-google-apps-script
This project, “node-google-apps-script”, is a Node.js based command-line interface (CLI) that uses Google Drive API to update Apps Script project from the command line. You can view the node package on the NPM site, and also view the GitHub repo. Both links have usage instructions. This tool was created by Dan Thareja, with additional features added by Matt Condon.
Before using the tool, take a look at the Apps Script Importing and Exporting Projects page. There are a few things that you should be aware of as you plan out your development process. There are also a few best practices that you can employ to take full advantage of developing in this approach.
There is a sample project that demonstrates some of the practices described in this post: click here to view that code on GitHub. To get all of the Apps Script samples, including this import/export development example:
Your standalone Apps Script projects live in Google Drive. If you use a command-line interface (CLI) tool like the one linked above, you can work in your favorite editor, and commit and sync code to your chosen repository. You can add tasks in your task runner to push code up to one or more Apps Script projects, conditionally including or excluding code for different environments, checking coding style, linting, minifying, etc. You can more easily create and push UI-related files to a file host outside of Apps Script, which could be useful if those same files are used in other apps you are building.
In addition to the information on the Importing and Exporting Projects page, here are a few things to consider:
Over and above the editing experience, the biggest improvements you get by working outside the script editor is that you are no longer locked into working in just one Apps Script project. You can much more easily collaborate as a team, with individual developers having their own working Apps Script projects, while also having more controlled test, user acceptance and production versions, each with more process and security. Beyond just the consistency with other normal project practices, there are a few Apps Script specific ways you can leverage this multi-environment approach.
If you are going to use this approach, here are three best practices to consider:
The provided sample shows a simple example of how a base configuration class could allow a developer to inject their local values for their own debugging and testing. In this case, the developer also added the annotation @NotOnlyCurrentDoc, which tells Apps Script that they need the full scope for Drive API access. In this project, the “production” deployment has the annotation @OnlyCurrentDoc, which leads to the OAuth scope that is limited to the document associated with script running as Sheets, Docs, or Forms add-on. If you add a standard file pattern to the source project’s “ignore” file, these developer-specific files will never get into the actual codebase.
Benefits for your project — Production can have more limited OAuth scopes, while a developer can use broader access during development. Developers can also have their own personal configuration settings to support their individual development efforts.
While there is no current way to trigger tests in an automated way, you still may want to author unit tests that validate specific functions within your projects. You’ll also likely have specific configuration values for testing. Once again, none of these files should make it into a production deployment. You can even use the Apps Script Execution API to drive those tests from a test runner!
Benefits for your project — You can author test functions, and keep them separate from the production Apps Script file. This slims down your production Apps Script project, and keeps the correct OAuth scopes that are needed for production users.
If you are developing an add-on for Sheets or Docs, and you expect to have an “active” item on the SpreadsheetApp. However when you are developing or testing, you may be running your Apps Script without an “active” context. If you need to develop in this mode, you can wrap the call to get the current active item in a method that also can determine what mode you are running in. This would allow your development or test instance to inject the ID of an “active” document to use for testing, while delegating to the getActive* result when running in a real context.
Benefits for your project — You can integrate better unit testing methodologies into your projects, even if the end deployment state dependents on resources that aren’t typically available when debugging.
You now have the option to use your own development and source management tools. While you still do need to use the Apps Script editor in your application’s lifecycle — to publish as a web app or add-on, configure advanced services, etc. — taking this step will help you get the most out of the power of the Apps Script platform. Remember to check out Apps Script on the Google Developers site to get more information and samples for your Apps Script development.
If you happen to use python tools on the command line to facilitate your team’s build process, you can check out Joe Stump's python-gas-cli. You can view the package info here or the GitHub repo where you’ll also find usage instructions.
Here are some additional reference links related to this post:
Posted by Saurabh Gupta, Product Manager, Google Apps Script
Back in December 2014, we announced the IFRAME sandbox mode for HtmlService which has helped improve the speed of an application’s user interface (UI). It also gives users a choice of using a variety of JS libraries on the client. We have been working hard to improve IFRAME sandbox mode and have added many features since then, including: Firefox support, file uploads, top navigation support, and improved Google Picker API support. Since IFRAME sandbox provides faster UIs and has more capabilities than NATIVE and EMULATED modes, developers should only be using IFRAME sandbox mode moving forward.
As of today, both EMULATED and NATIVE modes in HtmlService are deprecated. Over the next few months, we plan on sunsetting both EMULATED and NATIVE modes in stages to give you enough time to migrate your scripts.
We have created a migration guide to help you with this transition. For many scripts, no changes will be needed, unless they use a small set of features described in the migration guide. The guide also describes a few potential breaking changes. It is important that you review all your scripts that use HtmlService to ensure that the switch to IFRAME sandbox mode does not cause them to fail.
Here’s the timeline:
In November 2015, all new scripts will default to IFRAME sandbox mode unless NATIVE mode is explicitly specified. For example, if you make a copy of an existing script, the new script will use IFRAME sandbox mode unless you have explicitly set the sandbox mode to NATIVE.
In December 2015 (see sunset schedule for exact dates), EMULATED mode will be shutdown. Any scripts explicitly using EMULATED mode will default to IFRAME sandbox mode.
On April 28th, 2016, all scripts will default to IFRAME sandbox unless you have explicitly specified NATIVE mode in your script. For example, if your script has not specified any mode, then it will change from using NATIVE mode to IFRAME sandbox mode. Please make sure that your UI works well in IFRAME sandbox mode.
On June 30th 2016, NATIVE mode will be shutdown. All scripts explicitly using NATIVE mode will default to IFRAME sandbox mode.
While deprecations may at times seem inconvenient, this staged deprecation should ease in the migration process. Our goal is to provide a modern and secure environment enabling developers to create great apps for their users with Google Apps Script.
Posted by Edward Jones, Software Engineer, Google Apps Script and Wesley Chun, Developer Advocate, Google Apps
Have you ever wanted a server API that modifies cells in a Google Sheet, to execute a Google Apps Script app from outside of Google Apps, or a way to use Apps Script as an API platform? Today, we’re excited to announce you can do all that and more with the Google Apps Script Execution API.
The Execution API allows developers to execute scripts from any client (browser, server, mobile, or any device). You provide the authorization, and the Execution API will run your script. If you’re new to Apps Script, it’s simply JavaScript code hosted in the cloud that can access authorized Google Apps data using the same technology that powers add-ons. The Execution API extends the ability to execute Apps Script code and unlocks the power of Docs, Sheets, Forms, and other supported services for developers.
One of our launch partners, Pear Deck, used the new API to create an interactive presentation tool that connects students to teachers by converting slide decks into interactive experiences. Their app calls the Execution API to automatically generate a Google Doc customized for each student, so everyone gets a personalized set of notes from the presentation. Without the use of Apps Script, their app would be limited to using PDFs and other static file types. Check out the video below to see how it works.
Bruce McPherson, a Google Developer Expert (GDE) for Google Apps, says: “The Execution API is a great tool for enabling what I call ‘incremental transition’ from Microsoft Office (and VBA) to Apps (and Apps Script). A mature Office workflow may involve a number of processes currently orchestrated by VBA, with data in various formats and locations. It can be a challenge to move an entire workload in one step, especially an automated process with many moving parts. This new capability enables the migration of data and process in manageable chunks.” You can find some of Bruce’s sample migration code using the Execution API here.
The Google Apps Script Execution API is live and ready for you to use today. To get started, check out the developer documentation and quickstarts. We invite you to show us what you build with the Execution API!
