Posted by Wesley Chun (@wescpy), Developer Advocate, Google Cloud
Google Cloud Platform (GCP) provides infrastructure, serverless products, and APIs that help you build, innovate, and scale. G Suite provides a collection of productivity tools, developer APIs, extensibility frameworks and low-code platforms that let you integrate with G Suite applications, data, and users. While each solution is compelling on its own, users can get more power and flexibility by leveraging both together.
In the latest episode of the G Suite Dev Show, I'll show you one example of how you can take advantage of powerful GCP tools right from G Suite applications. BigQuery, for example, can help you surface valuable insight from massive amounts of data. However, regardless of "the tech" you use, you still have to justify and present your findings to management, right? You've already completed the big data analysis part, so why not go that final mile and tap into G Suite for its strengths? In the sample app covered in the video, we show you how to go from big data analysis all the way to an "exec-ready" presentation.
The sample application is meant to give you an idea of what's possible. While the video walks through the code a bit more, let's give all of you a high-level overview here. Google Apps Script is a G Suite serverless development platform that provides straightforward access to G Suite APIs as well as some GCP tools such as BigQuery. The first part of our app, the runQuery() function, issues a query to BigQuery from Apps Script then connects to Google Sheets to store the results into a new Sheet (note we left out CONSTANT variable definitions for brevity):
runQuery()
CONSTANT
function runQuery() { // make BigQuery request var request = {query: BQ_QUERY}; var queryResults = BigQuery.Jobs.query(request, PROJECT_ID); var jobId = queryResults.jobReference.jobId; queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId); var rows = queryResults.rows; // put results into a 2D array var data = new Array(rows.length); for (var i = 0; i < rows.length; i++) { var cols = rows[i].f; data[i] = new Array(cols.length); for (var j = 0; j < cols.length; j++) { data[i][j] = cols[j].v; } } // put array data into new Sheet var spreadsheet = SpreadsheetApp.create(QUERY_NAME); var sheet = spreadsheet.getActiveSheet(); var headers = queryResults.schema.fields; sheet.appendRow(headers); // header row sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data); // return Sheet object for later use return spreadsheet; }
It returns a handle to the new Google Sheet which we can then pass on to the next component: using Google Sheets to generate a Chart from the BigQuery data. Again leaving out the CONSTANTs, we have the 2nd part of our app, the createColumnChart() function:
CONSTANTs
createColumnChart()
function createColumnChart(spreadsheet) { // create & put chart on 1st Sheet var sheet = spreadsheet.getSheets()[0]; var chart = sheet.newChart() .setChartType(Charts.ChartType.COLUMN) .addRange(sheet.getRange(START_CELL + ':' + END_CELL)) .setPosition(START_ROW, START_COL, OFFSET, OFFSET) .build(); sheet.insertChart(chart); // return Chart object for later use return chart; }
The chart is returned by createColumnChart() so we can use that plus the Sheets object to build the desired slide presentation from Apps Script with Google Slides in the 3rd part of our app, the createSlidePresentation() function:
createSlidePresentation()
function createSlidePresentation(spreadsheet, chart) { // create new deck & add title+subtitle var deck = SlidesApp.create(QUERY_NAME); var [title, subtitle] = deck.getSlides()[0].getPageElements(); title.asShape().getText().setText(QUERY_NAME); subtitle.asShape().getText().setText('via GCP and G Suite APIs:\n' + 'Google Apps Script, BigQuery, Sheets, Slides'); // add new slide and insert empty table var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); var sheetValues = spreadsheet.getSheets()[0].getRange( START_CELL + ':' + END_CELL).getValues(); var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length); // populate table with data in Sheets for (var i = 0; i < sheetValues.length; i++) { for (var j = 0; j < sheetValues[0].length; j++) { table.getCell(i, j).getText().setText(String(sheetValues[i][j])); } } // add new slide and add Sheets chart to it var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); chartSlide.insertSheetsChart(chart); // return Presentation object for later use return deck; }
Finally, we need a driver application that calls all three one after another, the createColumnChart() function:
function createBigQueryPresentation() { var spreadsheet = runQuery(); var chart = createColumnChart(spreadsheet); var deck = createSlidePresentation(spreadsheet, chart); }
We left out some detail in the code above but hope this pseudocode helps kickstart your own project. Seeking a guided tutorial to building this app one step-at-a-time? Do our codelab at g.co/codelabs/bigquery-sheets-slides. Alternatively, go see all the code by hitting our GitHub repo at github.com/googlecodelabs/bigquery-sheets-slides. After executing the app successfully, you'll see the fruits of your big data analysis captured in a presentable way in a Google Slides deck:
This isn't the end of the story as this is just one example of how you can leverage both platforms from Google Cloud. In fact, this was one of two sample apps featured in our Cloud NEXT '18 session this summer exploring interoperability between GCP & G Suite which you can watch here:
Stay tuned as more examples are coming. We hope these videos plus the codelab inspire you to build on your own ideas.
Google Cloud Next '18 is only a few days away, and this year, there are over 500 sessions covering all aspects of cloud computing, from G Suite to the Google Cloud Platform. This is your chance to learn first-hand how to build custom solutions in G Suite alongside other developers from Independent Software Vendors (ISVs), systems integrators (SIs), and industry enterprises.
G Suite's intelligent productivity apps are secure, smart, and simple to use, so why not integrate your apps with them? If you're planning to attend the event and are wondering which sessions you should check out, here are some sessions to consider:
I look forward to meeting you in person at Next '18. In the meantime, check out the entire session schedule to find out everything it has to offer. Don't forget to swing by our "Meet the Experts" office hours (Tue-Thu), G Suite "Collaboration & Productivity" showcase demos (Tue-Thu), the G Suite Birds-of-a-Feather meetup (Wed), and the Google Apps Script & G Suite Add-ons meetup (just after the BoF on Wed). I'm excited at how we can use "all the tech" to change the world. See you soon!
Posted by Mertcan Mermerkaya, Software Engineer
We have great news for web developers that use Firebase Cloud Messaging to send notifications to clients! The FCM v1 REST API has integrated fully with the Web Notifications API. This integration allows you to set icons, images, actions and more for your Web notifications from your server! Better yet, as the Web Notifications API continues to grow and change, these options will be immediately available to you. You won't have to wait for an update to FCM to support them!
Below is a sample payload you can send to your web clients on Push API supported browsers. This notification would be useful for a web app that supports image posting. It can encourage users to engage with the app.
{ "message": { "webpush": { "notification": { "title": "Fish Photos 🐟", "body": "Thanks for signing up for Fish Photos! You now will receive fun daily photos of fish!", "icon": "firebase-logo.png", "image": "guppies.jpg", "data": { "notificationType": "fishPhoto", "photoId": "123456" }, "click_action": "https://example.com/fish_photos", "actions": [ { "title": "Like", "action": "like", "icon": "icons/heart.png" }, { "title": "Unsubscribe", "action": "unsubscribe", "icon": "icons/cross.png" } ] } }, "token": "<APP_INSTANCE_REGISTRATION_TOKEN>" } }
Notice that you are able to set new parameters, such as actions, which gives the user different ways to interact with the notification. In the example below, users have the option to choose from actions to like the photo or to unsubscribe.
To handle action clicks in your app, you need to add an event listener in the default firebase-messaging-sw.js file (or your custom service worker). If an action button was clicked, event.action will contain the string that identifies the clicked action. Here's how to handle the "like" and "unsubscribe" events on the client:
like
unsubscribe
// Retrieve an instance of Firebase Messaging so that it can handle background messages. const messaging = firebase.messaging(); // Add an event listener to handle notification clicks self.addEventListener('notificationclick', function(event) { if (event.action === 'like') { // Like button was clicked const photoId = event.notification.data.photoId; like(photoId); } else if (event.action === 'unsubscribe') { // Unsubscribe button was clicked const notificationType = event.notification.data.notificationType; unsubscribe(notificationType); } event.notification.close(); });
The SDK will still handle regular notification clicks and redirect the user to your click_action link if provided. To see more on how to handle click actions on the client, check out the guide.
Since different browsers support different parameters in different platforms, it's important to check out the browser compatibility documentation to ensure your notifications work as intended. Want to learn more about what the Send API can do? Check out the FCM Send API documentation and the Web Notifications API documentation. If you're using the FCM Send API and you incorporate the Web Notifications API in a cool way, then let us know! Find Firebase on Twitter at @Firebase, and Facebook and Google+ by searching "Firebase".
Email remains at the heart of how companies operate. That's why earlier this year, we previewed Gmail Add-ons—a way to help businesses speed up workflows. Since then, we've seen partners build awesome applications, and beginning today, we're extending the Gmail add-on preview to include all developers. Now anyone can start building a Gmail add-on.
Gmail Add-ons let you integrate your app into Gmail and extend Gmail to handle quick actions.
They are built using native UI context cards that can include simple text dialogs, images, links, buttons and forms. The add-on appears when relevant, and the user is just a click away from your app's rich and integrated functionality.
Gmail Add-ons are easy to create. You only have to write code once for your add-on to work on both web and mobile, and you can choose from a rich palette of widgets to craft a custom UI. Create an add-on that contextually surfaces cards based on the content of a message. Check out this video to see how we created an add-on to collate email receipts and expedite expense reporting.
Per the video, you can see that there are three components to the app's core functionality. The first component is getContextualAddOn()—this is the entry point for all Gmail Add-ons where data is compiled to build the card and render it within the Gmail UI. Since the add-on is processing expense reports from email receipts in your inbox, the createExpensesCard() parses the relevant data from the message and presents them in a form so your users can confirm or update values before submitting. Finally, submitForm()takes the data and writes a new row in an "expenses" spreadsheet in Google Sheets, which you can edit and tweak, and submit for approval to your boss.
getContextualAddOn()
createExpensesCard()
submitForm()
Check out the documentation to get started with Gmail Add-ons, or if you want to see what it's like to build an add-on, go to the codelab to build ExpenseIt step-by-step. While you can't publish your add-on just yet, you can fill out this form to get notified when publishing is opened. We can't wait to see what Gmail Add-ons you build!
The Closure Compiler was originally released, in Java, back in 2009. Today, we're announcing the very same Closure Compiler is now available in pure JavaScript, for use without Java. It's designed to run under NodeJS with support for some popular build tools.
If you've not heard of the Closure Compiler, it's a JavaScript optimizer, transpiler and type checker, which compiles your code into a high-performance, minified version. Nearly every web frontend at Google uses it to serve the smallest, fastest code possible.
It supports new features in ES2015, such as let, const, arrow functions, and provides polyfills for ES2015 methods not supported everywhere. To help you write better, maintainable and scalable code, the compiler also checks syntax, correct use of types, and provides warnings for many JavaScript gotchas. To find out more about the compiler itself, including tutorials, head to Google Developers.
This isn't a rewrite of Closure in JavaScript. Instead, we compile the Java source to JS to run under Node, or even inside a plain old browser. Every post or resource you see about Closure Compiler will also apply to this version.
To find out more about Closure Compiler's internals, be sure to check out this post by Dimitris (who works on the Closure team at Google), other posts on the Closure Tools blog, or read an exploratory post about Closure and how it can help your project in 2016.
Note that the JS version is experimental. It may not perform in the same way as the native Java version, but we believe it's an interesting new addition to the compiler landscape, and the Closure team will be working to improve and support it over time.
To include the JS version of Closure Compiler in your project, you should add it as a dependency of your project via NPM-
npm install --save-dev google-closure-compiler-js
To then use the compiler with Gulp, you can add a task like this-
const compiler = require('google-closure-compiler-js').gulp(); gulp.task('script', function() { // select your JS code here return gulp.src('./src/**/*.js', {base: './'}) .pipe(compiler({ compilation_level: 'SIMPLE', warning_level: 'VERBOSE', output_wrapper: '(function(){\n%output%\n}).call(this)', js_output_file: 'output.min.js', // outputs single file create_source_map: true })) .pipe(gulp.dest('./dist')); });
If you'd like to migrate from google-closure-compiler (which requires Java), you'll have to use gulp.src() or equivalents to load your JavaScript before it can be compiled. As this compiler runs in pure JavaScript, the compiler cannot load or save files from your filesystem directly.
For more information, check out Usage, supported Flags, or a demo project. Not all flags supported in the Java release are currently available in this experimental version. However, the compiler will let you know via exception if you've hit any missing ones.
Sarah Clark, Program Manager, Google Developer Training
Front-end web developers face challenges when using common “asynchronous” requests. These requests, such as fetching a URL or reading a file, often lead to complicated code, especially when performing multiple actions in a row. How can we make this easier for developers?
Javascript Promises are a new tool that simplifies asynchronous code, converting a tangle of callbacks and event handlers into simple, straightforward code such as: fetch(url).then(decodeJSON).then(addToPage)...
fetch(url).then(decodeJSON).then(addToPage)...
Promises are used by many new web standards, including Service Worker, the Fetch API, Quota Management, Font Load Events,Web MIDI, and Streams.
We’ve just opened up a online course on Promises, built in collaboration with Udacity. This brief course, which you can finish in about a day, walks you through building an “Exoplanet Explorer” app that reads and displays live data using Promises. You’ll also learn to use the Fetch API and finally kiss XMLHttpRequest goodbye!
This short course is a prerequisite for most of the Senior Web Developer Nanodegree. Whether you are in the paid Nanodegree program or taking the course for free, won’t you come learn to make your code simpler and more reliable today?
Originally posted on Google Apps Developers blog
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:
Originally posted to the Google Apps Developer blog
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!
<script src="https://apis.google.com/js/client.js?onload=CALLBACK"></script>
gapi.client.load('API_NAME', 'API_VERSION', CALLBACK);// Returns a request object which can then be executed.// METHOD_NAME is only available once CALLBACK runs.var request = gapi.client.METHOD_NAME(PARAMETERS_OBJECT); request.execute(callback);
plus.activities.search
<!DOCTYPE html><html> <head> </head> <body> <script type="text/javascript">function init() { // Load your API key from the Developer Console gapi.client.setApiKey('YOUR_API_KEY'); // Load the API gapi.client.load('plus', 'v1', function() { var request = gapi.client.plus.activities.search({ 'query': 'Google+', 'orderby': 'best' }); request.execute(function(resp) { // Output title var heading = document.createElement('h4'); heading.appendChild(document.createTextNode( resp.title)); var content = document.getElementById('content'); content.appendChild(heading); // Output content of the response if (!resp.items) { content.appendChild(document.createTextNode( 'No results found.')); } else { for (var i = 0; i < resp.items.length; i++) { var entry = document.createElement('p'); entry.appendChild(document.createTextNode( resp.items[i].title)); content.appendChild(entry); } } }); }); } </script> <script src="https://apis.google.com/js/client.js?onload=init"></script> <div id="content"></div> </body></html>
