Tag Archives: Apps Script

Get ready to up your Apps Script!

Posted by Charles Maxson, Developer Advocate, Google Cloud

Illustration of settings gear with icons inside

It’s been a little over a decade since Apps Script was introduced as the development platform to automate and extend Google Workspace. Since its inception, tens of millions of solution builders ranging from professional developers, business users, and hobbyists have adopted Apps Script because of its tight integration with Google Workspace, coupled with its relative ease of use, makes building solutions fast and accessible.

Over the course of its history, Apps Script has constantly evolved to keep up with the ever-changing Google Workspace applications themselves, as new features are introduced and existing ones enhanced. Changes to the platform and the development environment itself have been more deliberate, allowing the wide-ranging Apps Script developer audience to rely on a predictable and proven development experience.

Recently, there have been some notable updates. Earlier this year the Apps Script runtime engine went through a major update from the original Rhino runtime to the new V8 version, allowing you to leverage modern JavaScript features in your Apps Script projects. Another milestone launch was the introduction of the Apps Script Dashboard, the ‘homepage’ of Apps Script, where you have access to all your projects and Apps Script platform settings by simply navigating to script.google.com.

But as far as the overall developer experience with Apps Script, the core components of the Apps Script IDE (Integrated Development Environment) where developers spend most of their time writing and debugging code, managing versions and exceptions, deploying projects, etc.; that has been relatively unchanged over App Script’s long storied history—that is until now—and as an Apps Script developer, you are about to get more productive!

Welcome to the new Apps Script IDE

The new Apps Script IDE features the same rich integration with Google Workspace as it did before, allowing you to get started building solutions without having to install or configure anything. If you are working on a standalone script project application, you can use the Apps Script Dashboard to launch your project directly, or if you are working on a container bound project in Sheets, Slides or Docs, you can do so from selecting Tools > Script editor from their top menus.

Apps Script Project Dashboard

Apps Script Project Dashboard

If you launch your project using the Apps Script Dashboard, you will still start off in the Project Details Overview page. The contents of the Project Details page are relatively unchanged with just a few cosmetic updates where you can still get project info on the numbers of executions and users for your projects, errors, and OAuth scopes in use. On closer inspection, however, the seemingly subtle change to the left hand navigation is actually the first big enhancement of the new Apps Script IDE. Previously, when you launched into a project, you still had the Application Dashboard menus which let you navigate your projects, view all your executions and triggers as well as Apps Script features.

Apps Script Project Details Overview

Apps Script Project Details Overview

With the new IDE experience, the prior Apps Script Dashboard menu gives way to a new project-specific menu that lets you focus on your active project. This offers developers a more unified experience of moving between project settings and the code editor without having to navigate menus or bounce back to the applications dashboard. So while it's a subtle change at first glance, it's actually a significant boost for productivity.

If you launch the new IDE as a container bound project, you will immediately enter into the new Apps Script code editor, but the new project menu and developer flow is identical.

A Modern, Material Design

One of the more striking updates of the new Apps Script IDE was the work done on the code editor to modernize its look and feel, while also unifying the design with the overall developer experience. More than just aesthetic changes, the new code editor was designed to help developers focus on the most common essential tasks. This includes moving away from the traditional menu elements across the top of the original code editor, to a streamlined set of commands that focus on developer productivity. For example, the new code editor offers a simplified menu system optimized for running and debugging code, while all the other ‘project-related’ functions have been reorganized outside the code editor to the left-hand project navigation bar. This will simplify and set the focus on the core task of writing code, which will assist both new and seasoned Apps Script developers.

Apps Script Code Editor

Apps Script Code Editor

Boosting Developer Productivity

Behind the fresh new look of the Apps Script code editor, there is a long list of new productivity enhancements that will make any Apps Script developer happy. Some are subtle, some are major. Many are simply delightful. Any combination of them will make you more productive in writing Apps Script code. Here are just some of the highlights:

  • Code Editor enhancements - The actual code editor itself has been updated to make the code writing (and reading) experience better. AutoComplete helps you produce error free code easier than before with intelligent features like adding parentheses and apostrophes automatically as you code. Visual Indicators help you quickly see where issues lie as you navigate within your project. Inline documentation provides interactive definitions of Apps Script objects and methods right at the source while you are editing. Function Folding lets you collapse and expand code blocks to focus on what you are working on, while allowing you to hide what isn’t currently in scope. AutoFormating instantly makes your code consistently readable, which is a great boost for team development projects too.

Code Editor Enhancements

Code Editor Enhancements
  • Context Menu - The new code editor now includes a contextual menu accessed with a simple right-click. This context menu will be every developer's companion as it hosts some practical functionality like copy and paste, but also introduces some new powerhouse productivity features like locating variables, renaming symbols and reformatting your code document, and more.

Context Menu Image

Context Menu Image
  • Command Palette - While the new IDE’s UI focuses on simplicity, the command palette unlocks an unimaginable collection of features that does nearly anything a developer could want to do. The command palette is easily accessed by pressing {F1} from within the code editor.

Commend Palette Menu Image

Commend Palette Menu Image
  • Debugger - The debugging experience has been improved to allow developers to examine their code easier. Setting breakpoints works similarly but offers better visual queues. The new design of the debugger tools makes it cleaner and clearer with a dedicated UI optimization debugging steps and viewing the call stack and watch variables. This enhancement should help new developers get their bearings on debugging easier, while seasoned developers will appreciate the simplified, decluttered approach.

Debugger Image

Debugger
  • Real-time Logs - Managing the executions (and occasional errors) of your projects get even easier with Real-time Logs, which display log results directly in the editor as they are invoked, offering a real time saving convenience to developer productivity.

Logging Image

Logging image

Write your best code

The best way to explore all that awaits is you in the new Apps Script IDE and code editor is by simply diving in and writing some code by visiting script.new. Then you will truly see how the new code editor really does help you be more productive, enabling you to better write code faster, with less errors and with greater readability. The previous version of the Apps Script IDE and code editor served us well, but the jump in productivity and an overall better developer experience awaits you with the new Apps Script IDE.

Go write your best code starting at: script.google.com

To learn more: developers.google.com/apps-script

Get ready to up your Apps Script!

Posted by Charles Maxson, Developer Advocate, Google Cloud

Illustration of settings gear with icons inside

It’s been a little over a decade since Apps Script was introduced as the development platform to automate and extend Google Workspace. Since its inception, tens of millions of solution builders ranging from professional developers, business users, and hobbyists have adopted Apps Script because of its tight integration with Google Workspace, coupled with its relative ease of use, makes building solutions fast and accessible.

Over the course of its history, Apps Script has constantly evolved to keep up with the ever-changing Google Workspace applications themselves, as new features are introduced and existing ones enhanced. Changes to the platform and the development environment itself have been more deliberate, allowing the wide-ranging Apps Script developer audience to rely on a predictable and proven development experience.

Recently, there have been some notable updates. Earlier this year the Apps Script runtime engine went through a major update from the original Rhino runtime to the new V8 version, allowing you to leverage modern JavaScript features in your Apps Script projects. Another milestone launch was the introduction of the Apps Script Dashboard, the ‘homepage’ of Apps Script, where you have access to all your projects and Apps Script platform settings by simply navigating to script.google.com.

But as far as the overall developer experience with Apps Script, the core components of the Apps Script IDE (Integrated Development Environment) where developers spend most of their time writing and debugging code, managing versions and exceptions, deploying projects, etc.; that has been relatively unchanged over App Script’s long storied history—that is until now—and as an Apps Script developer, you are about to get more productive!

Welcome to the new Apps Script IDE

The new Apps Script IDE features the same rich integration with Google Workspace as it did before, allowing you to get started building solutions without having to install or configure anything. If you are working on a standalone script project application, you can use the Apps Script Dashboard to launch your project directly, or if you are working on a container bound project in Sheets, Slides or Docs, you can do so from selecting Tools > Script editor from their top menus.

Apps Script Project Dashboard

Apps Script Project Dashboard

If you launch your project using the Apps Script Dashboard, you will still start off in the Project Details Overview page. The contents of the Project Details page are relatively unchanged with just a few cosmetic updates where you can still get project info on the numbers of executions and users for your projects, errors, and OAuth scopes in use. On closer inspection, however, the seemingly subtle change to the left hand navigation is actually the first big enhancement of the new Apps Script IDE. Previously, when you launched into a project, you still had the Application Dashboard menus which let you navigate your projects, view all your executions and triggers as well as Apps Script features.

Apps Script Project Details Overview

Apps Script Project Details Overview

With the new IDE experience, the prior Apps Script Dashboard menu gives way to a new project-specific menu that lets you focus on your active project. This offers developers a more unified experience of moving between project settings and the code editor without having to navigate menus or bounce back to the applications dashboard. So while it's a subtle change at first glance, it's actually a significant boost for productivity.

If you launch the new IDE as a container bound project, you will immediately enter into the new Apps Script code editor, but the new project menu and developer flow is identical.

A Modern, Material Design

One of the more striking updates of the new Apps Script IDE was the work done on the code editor to modernize its look and feel, while also unifying the design with the overall developer experience. More than just aesthetic changes, the new code editor was designed to help developers focus on the most common essential tasks. This includes moving away from the traditional menu elements across the top of the original code editor, to a streamlined set of commands that focus on developer productivity. For example, the new code editor offers a simplified menu system optimized for running and debugging code, while all the other ‘project-related’ functions have been reorganized outside the code editor to the left-hand project navigation bar. This will simplify and set the focus on the core task of writing code, which will assist both new and seasoned Apps Script developers.

Apps Script Code Editor

Apps Script Code Editor

Boosting Developer Productivity

Behind the fresh new look of the Apps Script code editor, there is a long list of new productivity enhancements that will make any Apps Script developer happy. Some are subtle, some are major. Many are simply delightful. Any combination of them will make you more productive in writing Apps Script code. Here are just some of the highlights:

  • Code Editor enhancements - The actual code editor itself has been updated to make the code writing (and reading) experience better. AutoComplete helps you produce error free code easier than before with intelligent features like adding parentheses and apostrophes automatically as you code. Visual Indicators help you quickly see where issues lie as you navigate within your project. Inline documentation provides interactive definitions of Apps Script objects and methods right at the source while you are editing. Function Folding lets you collapse and expand code blocks to focus on what you are working on, while allowing you to hide what isn’t currently in scope. AutoFormating instantly makes your code consistently readable, which is a great boost for team development projects too.

Code Editor Enhancements

Code Editor Enhancements
  • Context Menu - The new code editor now includes a contextual menu accessed with a simple right-click. This context menu will be every developer's companion as it hosts some practical functionality like copy and paste, but also introduces some new powerhouse productivity features like locating variables, renaming symbols and reformatting your code document, and more.

Context Menu Image

Context Menu Image
  • Command Palette - While the new IDE’s UI focuses on simplicity, the command palette unlocks an unimaginable collection of features that does nearly anything a developer could want to do. The command palette is easily accessed by pressing {F1} from within the code editor.

Commend Palette Menu Image

Commend Palette Menu Image
  • Debugger - The debugging experience has been improved to allow developers to examine their code easier. Setting breakpoints works similarly but offers better visual queues. The new design of the debugger tools makes it cleaner and clearer with a dedicated UI optimization debugging steps and viewing the call stack and watch variables. This enhancement should help new developers get their bearings on debugging easier, while seasoned developers will appreciate the simplified, decluttered approach.

Debugger Image

Debugger
  • Real-time Logs - Managing the executions (and occasional errors) of your projects get even easier with Real-time Logs, which display log results directly in the editor as they are invoked, offering a real time saving convenience to developer productivity.

Logging Image

Logging image

Write your best code

The best way to explore all that awaits is you in the new Apps Script IDE and code editor is by simply diving in and writing some code by visiting script.new. Then you will truly see how the new code editor really does help you be more productive, enabling you to better write code faster, with less errors and with greater readability. The previous version of the Apps Script IDE and code editor served us well, but the jump in productivity and an overall better developer experience awaits you with the new Apps Script IDE.

Go write your best code starting at: script.google.com

To learn more: developers.google.com/apps-script

Automate & Extend with Apps Script (Google Cloud for Student Developers)

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


In the previous episode of our new Google Cloud for Student Developers video series, we introduced G Suite REST APIs, showing how to enhance your applications by integrating with Gmail, Drive, Calendar, Docs, Sheets, and Slides. However, not all developers prefer the lower-level style of programming requiring the use of HTTP, OAuth2, and processing the request-response cycle of API usage. Building apps that access Google technologies is open to everyone at any level, not just advanced software engineers.

Enhancing career readiness of non-engineering majors helps make our services more inclusive and helps democratize API functionality to a broader audience. For the budding data scientist, business analyst, DevOps staff, or other technical professionals who don't code every day as part of their profession, Google Apps Script was made just for you. Rather than thinking about development stacks, HTTP, or authorization, you access Google APIs with objects.

This video blends a standard "Hello World" example with various use cases where Apps Script shines, including cases of automation, add-ons that extend the functionality of G Suite editors like Docs, Sheets, and Slides, accessing other Google or online services, and custom functions for Google Sheets—the ability to add new spreadsheet functions.

One featured example demonstrates the power to reach multiple Google technologies in an expressive way: lots of work, not much code. What may surprise readers is that this entire app, written by a colleague years ago, is comprised of just 4 lines of code:

function sendMap() {
var sheet = SpreadsheetApp.getActiveSheet();
var address = sheet.getRange('A1').getValue();
var map = Maps.newStaticMap().addMarker(address);
GmailApp.sendEmail('[email protected]',
'Map', 'See below.', {attachments:[map]});
}

Apps Script shields its users from the complexities of authorization and "API service endpoints." Developers only need an object to interface with a service; in this case, SpreadsheetApp to access Google Sheets, and similarly, Maps for Google Maps plus GmailApp for Gmail. Viewers can build this sample line-by-line with its corresponding codelab (a self-paced, hands-on tutorial). This example helps student (and professional) developers...

  1. Build something useful that can be extended into much more
  2. Learn how to accomplish several tasks without a lot of code
  3. Imagine what else is possible with G Suite developer tools

For further exploration, check out this video as well as this one which introduces Apps Script and presents the same code sample with more details. (Note the second video emails the map's link, but the app has been updated to attach it instead; the code has been updated everywhere else.) You may also access the code at its open source repository. If that's not enough, learn about other ways you can use Apps Script from its video library. Finally, stay tuned for the next pair of episodes which will cover full sample apps, one with G Suite REST APIs, and another with Apps Script.

We look forward to seeing what you build with Google Cloud.

Evolving automations into applications using Apps Script

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

Editor’s Note: Guest authors Diego Moreno and Sophia Deng (@sophdeng) are from Gigster, a firm that builds dynamic teams made of top global talent who create industry-changing custom software.

Prelude: Data input & management … three general choices

Google Cloud provides multiple services for gathering and managing data. Google Forms paired with Google Sheets are quite popular as they require no engineering resources while being incredibly powerful, providing storage of up to 5 million rows of data and built-in analytics for small team projects.

At the other end of the spectrum, to support a high volume of users or data, Google Cloud provides advanced serverless platforms like Google App Engine (web app-hosting) and Google Cloud Functions (function/service-hosting) that can use Google Cloud Firestore for fast and scalable data storage. These are perfect for professional engineering teams that need autoscaling to respond to any level of user traffic and data input. Such apps can also be packaged into a container and deployed serverlessly on Google Cloud Run.

However, it's quite possible your needs are right in-between. Today, we're happy to present the Gigster story and their innovative use of Google Apps Script—a highly-accessible service conventionally relegated to simple macro and add-on development, but which Gigster used to its advantage, building robust systems to transform their internal operations. Apps Script is also serverless, meaning Gigster didn't have to manage any servers for their application nor did they need to find a place to host its source code.

The Gigster story

Gigster enables distributed teams of software engineers, product managers and designers to build software applications for enterprise clients. Over the past five years, Gigster has delivered thousands of projects, all with distributed software teams. Our group, the Gigster Staffing Operations Team, is responsible for assembling these teams from Gigster’s network of over 1,000 freelancers.

Two years ago, our team began building custom software to automate the multi-stage and highly manual team staffing process. Building internal software has allowed the same-size Staffing Operations Team (3 members!) to enjoy a 60x reduction in time spent staffing each role.

The Apps Script ecosystem has emerged as the most critical component in our toolkit for building this internal software, due to its versatility and ease of deployment. We want to share how one piece of the staffing process has evolved to become more powerful over time thanks to Apps Script. Ultimately, we hope that sharing this journey enables all types of teams to build their own tools and unlock new possibilities.

End-to-end automation in Google Sheets

Staffing is an operationally intensive procedure. Just finding willing and able candidates requires numerous steps:

  1. Gathering and formatting customer requirements.
  2. Communicating with candidates through multiple channels.
  3. Logging candidate responses.
  4. Processing paperwork for placement

To add complexity, many of these steps require working with different third-party applications. For awhile, we performed every step manually, tracking every piece of data generated in one central Sheet (the “Staffing Broadcast Google Sheet”). At a certain point, this back-and-forth work to log data from numerous applications became unsustainable. Although we leveraged Google Sheets features like Data Validation rules and filters, the Staffing Broadcast Sheet could not alleviate the high degree of manual processes that were required of the team.

centralized Staffing Broadcast Google Sheet

The centralized Staffing Broadcast Google Sheet provided organization, but required a high degree of manual entry for tracking candidate decisions.

The key transformation was integrating Sheets data with third-party APIs via Apps Script. This enabled us to cut out the most time-consuming operations. We no longer had to flip between applications to message candidates, wait for their replies, and then manually track responses.

To interact with these APIs, we built a user interface directly into the Staffing Broadcast Google Sheet. By introducing an information module, as well as drop-down lists and buttons, we were able to define a small set of manual actions versus the much wider list of tasks the tool would perform automatically across multiple applications.

integrating Apps Script with third-party APIs

By integrating Google Apps Script with third-party APIs and creating a user interface, we evolved the Staffing Broadcast Tool to centralize and automate almost every step of the staffing process.

doPost() is the key function in our staffing tool that facilitates third-party services triggering our Apps Script automations. Below is a snippet of how we listened to candidates' responses from a third-party messaging application. In this case, queueing the third-party message in a Google Sheet so it can be processed with improved error-handling.

/**
* Receive POST requests and record to queue.
*/
doPost(e) {
var payload = e.postData.contents;
SpreadsheetApp.openById(SPREADSHEET_ID)
.getSheetByName("Unprocessed")
.appendRow([payload]);
return ContentService.createTextOutput(""); // Return 200
}

Almost all manual work associated with finding candidates was automated through the combination of integrations with third-party APIs and having a user interface to perform a small, defined set of actions. Our team’s day-to-day became shockingly simple: select candidates to receive messages within the Staffing Broadcast Tool, then click the “Send Broadcast” button. That’s it. The tool handled everything else.

Leveraging Sheets as our foundation, we fundamentally transformed our spreadsheet into a custom software application. The spreadsheet went from a partially automated datastore to a tool that provided an end-to-end automated solution, requiring only the click of a few buttons to execute.

Evolution into a standalone application

While satisfied, we understood that having our application live in Google Sheets had its limitations, namely, it was difficult for multiple team members to simultaneously use the tool. Using doGet(), the sibling to doPost(), we began building an HTML frontend to the Staffing Broadcast Tool. In addition to resolving difficulties related to multiple users being in a spreadsheet, it also allowed us to build an easier-to-use and more responsive tool by leveraging Bootstrap & jQuery.

Having multiple users in a single Google Sheet can create conflicts, but Apps Script allowed us to build a responsive web app leveraging common libraries like Bootstrap & jQuery that eliminated those problems while providing an improved user experience.

When other teams at Gigster got wind of what we built, it was easy to grant access to others beyond the Staffing Operations Team. Since Apps Script is part of the G Suite developer ecosystem, we relied on Google’s security policies to help deploy our tools to larger audiences.

While this can be done through Google’s conventional sharing tools, it can also be done with built-in Apps Script functions like Session.getActiveUser() that allow us to restrict access to specific Google users. In our case, those within our organization plus a few select users.

To this day, we continue to use this third version of the Staffing Broadcast Tool in our daily operations as it supports 100% of all client projects at Gigster.

Conclusion

By fundamentally transforming the Staffing Broadcast Tool with Apps Script, Gigster’s Staffing Operations Team increased its efficiency while supporting the growth of our company. Inspired by these business benefits, we applied this application-building approach using Apps Script for multiple tools, including candidate searching, new user onboarding, and countless automations.

Our team’s psychological shift about how we view what we are capable of, especially as a non-engineering team, has been the most valuable part of this journey. By leveraging an already familiar ecosystem to build our own software, we have freed team members to become more self-sufficient and valuable to our customers.

To get started on your Apps Script journey, we recommend you check out the Apps Script Fundamentals playlist and the official documentation. And if you're a freelancer looking to build software applications for clients, we’re always looking for talented software engineers, product managers or designers to join Gigster’s Talent Network.

Thank you to Sandrine Bitton, the third member of the Staffing Operations Team, for all her help in the development of the Staffing Broadcast Tool.

Code that final mile: from big data analysis to slide presentation

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):

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:

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:

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.

10 must-see G Suite developer sessions at Google Cloud Next ‘18

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

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:

  • "Power Your Apps with Gmail, Google Drive, Calendar, Sheets, Slides, and More!" on Tuesday, July 24th. Join me as I lead this session that provides a high-level technical overview of the various ways you can build with G Suite. This is a great place to start before attending deeper technical sessions.
  • "Power your apps with Gmail, Google Drive, Calendar, Sheets, Slides and more" on Monday, July 23rd and Friday, July 27th. Join me for one of our half-day bootcamps! Both are identical and bookend the conference—one on Monday and another on Friday, meaning you can do either one and still make it to all the other conference sessions. While named the same as the technical overview above, the bootcamps dive a bit deeper and feature more detailed tech talks on Google Apps Script, the G Suite REST APIs, and App Maker. The three (or more!) hands-on codelabs will leave you with working code that you can start customizing for your own apps on the job! Register today to ensure you get a seat.
  • "Automating G Suite: Apps Script & Sheets Macro Recorder" and "Enhancing the Google Apps Script Developer Experience" both on Tuesday, July 24th. Interested in Google Apps Script, our customized serverless JavaScript runtime used to automate, integrate, and extend G Suite? The first session introduces developers and ITDMs to new features as well as real business use cases while the other dives into recent features that make Apps Script more friendly for the professional developer.
  • "G Suite + GCP: Building Serverless Applications with All of Google Cloud" on Wednesday, July 25th. This session is your chance to attend one of the few hybrid talks that look at how to you can build applications on both the GCP and G Suite platforms. Learn about serverless—a topic that's become more and more popular over the past year—and see examples on both platforms with a pair of demos that showcase how you can take advantage of GCP tools from a G Suite serverless app, and how you can process G Suite data driven by GCP serverless functions. I'm also leading this session and eager to show how you can leverage the strengths of each platform together in the same applications.
  • "Build apps your business needs, with App Maker" and "How to Build Enterprise Workflows with App Maker" on Tuesday, July 24th and Thursday, July 26th, respectively. Google App Maker is a new low-code, development environment that makes it easy to build custom apps for work. It's great for business analysts, technical managers, or data scientists who may not have software engineering resources. With a drag & drop UI, built-in templates, and point-and-click data modeling, App Maker lets you go from idea to app in minutes! Learn all about it with our pair of App Maker talks featuring our Developer Advocate, Chris Schalk.
  • "The Google Docs, Sheets & Slides Ecosystem: Stronger than ever, and growing" and "Building on the Docs Editors: APIs and Apps Script" on Wednesday, July 25th and Thursday, July 26th, respectively. Check out these pair of talks to learn more about how to write apps that integrate with the Google Docs editors (Docs, Sheets, Slides, Forms). The first describes the G Suite productivity tools' growing interoperability in the enterprise with while the second focuses on the different integration options available to developers, either using Google Apps Script or the REST APIs.
  • "Get Productive with Gmail Add-ons" on Tuesday, July 24th. We launched Gmail Add-ons less than a year ago to help developers integrate their apps alongside Gmail. Check out this video I made to help you get up-to-speed on Gmail Add-ons! This session is for developers either new to Gmail Add-ons or want to hear the latest from the Gmail Add-ons and API team.

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!

10 must-see G Suite developer sessions at Google Cloud Next ‘18



Google Cloud Next '18 is less than a week away and this year, there are over 500 sessions, covering all aspects of cloud computing—IaaS, PaaS, and SaaS. This is your chance to hear from experts in artificial intelligence, as well as learn first-hand how to build custom solutions in G Suite alongside developers other Independent Software Vendors (ISVs), systems integrators (SIs) or 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 to enhance your skill set, here are some sessions to consider:

  • Power Your Apps with Gmail, Google Drive, Calendar, Sheets, Slides, and More!" on Tuesday, July 24th. Join me as I lead this session that provides a high-level technical overview of the various ways you can build with G Suite. This is a great place to start before attending deeper technical sessions. 
  • “Power your apps with Gmail, Google Drive, Calendar, Sheets, Slides and more” on Monday, July 23rd and Friday, July 27th. If you're already up-to-speed and want to leave NEXT with actual, working code you can use at school or on the job, join us for one of our bootcamps! Both are identical and bookend the conference—one on Monday and another on Friday. While named the same as the technical overview talk above, these dive a bit deeper, show more API usage examples and feature hands-on codelabs. Register today to ensure you get a seat.
  • Automating G Suite: Apps Script & Sheets Macro Recorder” or “Enhancing the Google Apps Script Developer Experience” on Tuesday, July 24th. Interested in Google Apps Script, our customized serverless JavaScript runtime used to automate, integrate, and extend G Suite apps and data? The first session introduces developers and ITDMs to new features as well as real business use cases while the other session dives into recent features that make Apps Script more friendly for the professional developer. 
  • G Suite + GCP: Building Serverless Applications with All of Google Cloud” on Wednesday, July 25th. This session is your chance to attend one of the few hybrid talks that look at how to you can build applications on both GCP and G Suite platforms. Learn about GCP and G Suite serverless products— a topic that’s become more and more popular over the past year—and see how it works firsthand with demos. I’m also leading this session and eager to show how you can leverage both platforms in the same application. 
  • Build apps your business needs, with App Maker” or “How to Build Enterprise Workflows with App Maker” on Tuesday, July 24th and Thursday, July 26th respectively. Google App Maker is a new low-code, development environment that makes it easy to build custom apps for work. It’s great for business analysts, technical managers or data scientists who may not have software engineering resources. With a drag & drop UI, built-in templates, and point-and-click data modeling, App Maker lets you go from idea to app in minutes! Learn all about it with our pair of App Maker talks featuring our Developer Advocate, Chris Schalk. 
  • The Google Docs, Sheets & Slides Ecosystem: Stronger than ever, and growing” or “Building on the Docs Editors: APIs and Apps Script” on Wednesday, July 25th and Thursday, July 26th respectively. Check out these pair of talks to learn more about how to write apps that integrate with Google Docs, Sheets, Slides and Forms. The first describes the G Suite productivity tools' growing interoperability in the enterprise with while the second focuses on the different options available to developers for integrating with the G Suite "editor" applications. 
  • Get Productive with Gmail Add-ons” on Tuesday, July 24th. We launched Gmail Add-ons less than a year ago (You can check out this video to learn more.) to help developers integrate their apps alongside Gmail. Come to this session to learn the latest from the Gmail Add-ons and API team.
I look forward to meeting you in person at Next '18. In the meantime, you can check out the entire session schedule to find out everything NEXT has to offer or this video where I talk about how I think technology will change the world. See you soon!

Hangouts Chat alerts & notifications… with asynchronous messages

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

While most chatbots respond to user requests in a synchronous way, there are scenarios when bots don't perform actions based on an explicit user request, such as for alerts or notifications. In today's DevByte video, I'm going to show you how to send messages asynchronously to rooms or direct messages (DMs) in Hangouts Chat, the team collaboration and communication tool in G Suite.

What comes to mind when you think of a bot in a chat room? Perhaps a user wants the last quarter's European sales numbers, or maybe, they want to look up local weather or the next movie showtime. Assuming there's a bot for whatever the request is, a user will either send a direct message (DM) to that bot or @mention the bot from within a chat room. The bot then fields the request (sent to it by the Hangouts Chat service), performs any necessary magic, and responds back to the user in that "space," the generic nomenclature for a room or DM.

Our previous DevByte video for the Hangouts Chat bot framework shows developers what bots and the framework are all about as well as how to build one of these types of bots, in both Python and JavaScript. However, recognize that these bots are responding synchronously to a user request. This doesn't suffice when users want to be notified when a long-running background job has completed, when a late bus or train will be arriving soon, or when one of their servers has just gone down. Recognize that such alerts can come from a bot but also perhaps a monitoring application. In the latest episode of the G Suite Dev Show, learn how to integrate this functionality in either type of application.

From the video, you can see that alerts and notifications are "out-of-band" messages, meaning they can come in at any time. The Hangouts Chat bot framework provides several ways to send asynchronous messages to a room or DM, generically referred to as a "space." The first is the HTTP-based REST API. The other way is using what are known as "incoming webhooks."

The REST API is used by bots to send messages into a space. Since a bot will never be a human user, a Google service account is required. Once you create a service account for your Hangouts Chat bot in the developers console, you can download its credentials needed to communicate with the API. Below is a short Python sample snippet that uses the API to send a message asynchronously to a space.

from apiclient import discovery
from httplib2 import Http
from oauth2client.service_account import ServiceAccountCredentials

SCOPES = 'https://www.googleapis.com/auth/chat.bot'
creds = ServiceAccountCredentials.from_json_keyfile_name(
'svc_acct.json', SCOPES)
CHAT = discovery.build('chat', 'v1', http=creds.authorize(Http()))

room = 'spaces/<ROOM-or-DM>'
message = {'text': 'Hello world!'}
CHAT.spaces().messages().create(parent=room, body=message).execute()

The alternative to using the API with services accounts is the concept of incoming webhooks. Webhooks are a quick and easy way to send messages into any room or DM without configuring a full bot, i.e., monitoring apps. Webhooks also allow you to integrate your custom workflows, such as when a new customer is added to the corporate CRM (customer relationship management system), as well as others mentioned above. Below is a Python snippet that uses an incoming webhook to communicate into a space asynchronously.

import requests
import json

URL = 'https://chat.googleapis.com/...&thread_key=T12345'
message = {'text': 'Hello world!'}
requests.post(URL, data = json.dumps(message))

Since incoming webhooks are merely endpoints you HTTP POST to, you can even use curl to send a message to a Hangouts Chat space from the command-line:

curl \
-X POST \
-H 'Content-Type: application/json' \
'https://chat.googleapis.com/...&thread_key=T12345' \
-d '{"text": "Hello!"}'

To get started, take a look at the Hangouts Chat developer documentation, especially the specific pages linked to above. We hope this video helps you take your bot development skills to the next level by showing you how to send messages to the Hangouts Chat service asynchronously.

Introducing the Data Studio Community Connector Codelab

Posted by Minhaz Kazi, Developer Advocate, Google Data Studio

Data Studio is Google's free next gen business intelligence and data visualization platform. Community Connectors for Data Studio let you build connectors to any internet-accessible data source using Google Apps Script. You can build Community Connectors for commercial, enterprise, and personal use. Learn how to build Community Connectors using the Data Studio Community Connector Codelab.

Use the Community Connector Codelab

The Community Connector Codelab explains how Community Connectors work and provides a step by step tutorial for creating your first Community Connector. You can get started if you have a basic understanding of Javascript and web APIs. You should be able to build your first connector in 30 mins using the Codelab.

If you have previously imported data into Google Sheets using Apps Script, you can use this Codelab to get familiar with the Community Connectors and quickly port your code to fetch your data directly into Data Studio.

Why create your own Community Connector

Community Connectors can help you to quickly deliver an end-to-end visualization solution that is user-friendly and delivers high user value with low development efforts. Community Connectors can help you build a reporting solution for personal, public, enterprise, or commercial data, and also do explanatory visualizations.

  • If you provide a web based service to customers, you can create template dashboards or even let your users create their own visualization based on the users' data from your service.
  • Within an enterprise, you can create serverless and highly scalable reporting solutions where you have complete control over your data and sharing features.
  • You can create an aggregate view of all your metrics across different commercial platforms and service providers while providing drill down capabilities.
  • You can create connectors to public and open datasets. Sharing these connectors will enable other users to quickly gain access to these datasets and dive into analysis directly without writing any code.

By building a Community Connector, you can go from scratch to a push button customized dashboard solution for your service in a matter of hours.

The following dashboard uses Community Connectors to fetch data from Stack Overflow, GitHub, and Twitter. Try using the date filter to view changes across all sources:

This dashboard uses the following Community Connectors:

You can build your own connector to any preferred service and publish it in the Community Connector gallery. The Community Connector gallery now has over 90 Partner Connectors connecting to more than 450 data sources.

Once you have completed the Codelab, view the Community Connector documentation and sample code on the Data Studio open source repository to build your own connector.

Developing bots for Hangouts Chat

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

We recently introduced Hangouts Chat to general availability. This next-generation messaging platform gives G Suite users a new place to communicate and to collaborate in teams. It features archive & search, tighter G Suite integration, and the ability to create separate, threaded chat rooms. The key new feature for developers is a bot framework and API. Whether it's to automate common tasks, query for information, or perform other heavy-lifting, bots can really transform the way we work.

In addition to plain text replies, Hangouts Chat can also display bot responses with richer user interfaces (UIs) called cards which can render header information, structured data, images, links, buttons, etc. Furthermore, users can interact with these components, potentially updating the displayed information. In this latest episode of the G Suite Dev Show, developers learn how to create a bot that features an updating interactive card.

As you can see in the video, the most important thing when bots receive a message is to determine the event type and take the appropriate action. For example, a bot will perform any desired "paperwork" when it is added to or removed from a room or direct message (DM), generically referred to as a "space" in the vernacular.

Receiving an ordinary message sent by users is the most likely scenario; most bots do "their thing" here in serving the request. The last event type occurs when a user clicks on an interactive card. Similar to receiving a standard message, a bot performs its requisite work, including possibly updating the card itself. Below is some pseudocode summarizing these four event types and represents what a bot would likely do depending on the event type:

function processEvent(req, rsp) {
var event = req.body; // event type received
var message; // JSON response message

if (event.type == 'REMOVED_FROM_SPACE') {
// no response as bot removed from room
return;

} else if (event.type == 'ADDED_TO_SPACE') {
// bot added to room; send welcome message
message = {text: 'Thanks for adding me!'};

} else if (event.type == 'MESSAGE') {
// message received during normal operation
message = responseForMsg(event.message.text);

} else if (event.type == 'CARD_CLICKED') {
// user-click on card UI
var action = event.action;
message = responseForClick(
action.actionMethodName, action.parameters);
}

rsp.send(message);
};

The bot pseudocode as well as the bot featured in the video respond synchronously. Bots performing more time-consuming operations or those issuing out-of-band notifications, can send messages to spaces in an asynchronous way. This includes messages such as job-completed notifications, alerts if a server goes down, and pings to the Sales team when a new lead is added to the CRM (Customer Relationship Management) system.

Hangouts Chat supports more than JavaScript or Python and Google Apps Script or Google App Engine. While using JavaScript running on Apps Script is one of the quickest and simplest ways to get a bot online within your organization, it can easily be ported to Node.js for a wider variety of hosting options. Similarly, App Engine allows for more scalability and supports additional languages (Java, PHP, Go, and more) beyond Python. The bot can also be ported to Flask for more hosting options. One key takeaway is the flexibility of the platform: developers can use any language, any stack, or any cloud to create and host their bot implementations. Bots only need to be able to accept HTTP POST requests coming from the Hangouts Chat service to function.

At Google I/O 2018 last week, the Hangouts Chat team leads and I delivered a longer, higher-level overview of the bot framework. This comprehensive tour of the framework includes numerous live demos of sample bots as well as in a variety of languages and platforms. Check out our ~40-minute session below.

To help you get started, check out the bot framework launch post. Also take a look at this post for a deeper dive into the Python App Engine version of the vote bot featured in the video. To learn more about developing bots for Hangouts Chat, review the concepts guides as well as the "how to" for creating bots. You can build bots for your organization, your customers, or for the world. We look forward to all the exciting bots you're going to build!