Posted by Wesley Chun, Developer Advocate, Google Cloud
File backup isn't the most exciting topic while analyzing images with AI/ML is more interesting, so combining them probably isn't a workflow you think about often. However, by augmenting the former with the latter, you can build a more useful solution than without. Google provides a diverse array of developer tools you can use to realize this ambition, and in fact, you can craft such a workflow with Google Cloud products alone. More compellingly, the basic principle of mixing-and-matching Google technologies can be applied to many other challenges faced by you, your organization, or your customers.
The sample app presented uses Google Drive and Sheets plus Cloud Storage and Vision to make it happen. The use-case: Google Workspace (formerly G Suite) users who work in industries like architecture or advertising, where multimedia files are constantly generated. Every client job results in yet another Drive subfolder and collection of asset files. Successive projects lead to even more files and folders. At some point, your Drive becomes a "hot mess," making users increasingly inefficient, requiring them to scroll endlessly to find what they're looking for.
A user and their Google Drive files
How can Google Cloud help? Like Drive, Cloud Storage provides file (and generic blob) storage in the cloud. (More on the differences between Drive & Cloud Storage can be found in this video.)
Cloud Storage provides several storage classes depending on how often you expect to access your archived files. The less often files are accessed, the "colder" the storage, and the lower the cost. As users progress from one project to another, they're not as likely to need older Drive folders and those make great candidates to backup to Cloud Storage.
First challenge: determine the security model. When working with Google Cloud APIs, you generally select OAuth client IDs to access data owned by users and service accounts for data owned by applications/projects. The former is typically used with Workspace APIs while the latter is the primary way to access Google Cloud APIs. Since we're using APIs from both product groups, we need to make a decision (for now and change later if desired).
Since the goal is a simple proof-of-concept, user auth suffices. OAuth client IDs are standard for Drive & Sheets API access, and the Vision API only needs API keys so the more-secure OAuth client ID is more than enough. The only IAM permissions to acquire are for the user running the script to get write access to the destination Cloud Storage bucket. Lastly, Workspace APIs don't have their own product client libraries (yet), so the lower-level Google APIs "platform" client libraries serve as a "lowest common denominator" to access all four REST APIs. Those who have written Cloud Storage or Vision code using the Cloud client libraries will see something different.
The prototype is a command-line script. In real life, it would likely be an application in the cloud, executing as a Cloud Function or a Cloud Task running as determined by Cloud Scheduler. In that case, it would use a service account with Workspace domain-wide delegation to act on behalf of an employee to backup their files. See this page in the documentation describing when you'd use this type of delegation and when not to.
Our simple prototype targets individual image files, but you can continue to evolve it to support multiple files, movies, folders, and ZIP archives if desired. Each function calls a different API, creating a "service pipeline" with which to process the images. The first pair of functions are drive_get_file() and gcs_blob_upload(). The former queries for the image on Drive, grabs pertinent metadata (filename, ID, MIMEtype, size), downloads the binary "blob" and returns all of that to the caller. The latter uploads the binary along with relevant metadata to Cloud Storage. The script was written in Python for brevity, but the client libraries support most popular languages. Below is the aforementioned function pseudocode:
drive_get_file()
gcs_blob_upload()
def drive_get_file(fname): rsp = DRIVE.files().list(q="name='%s'" % fname).execute().get['files'][0] fileId, fname, mtype = rsp['id'], rsp['name'], rsp['mimeType'] blob = DRIVE.files().get_blob(fileId).execute() return fname, mtype, rsp['modifiedTime'], blob def gcs_blob_upload(fname, folder, bucket, blob, mimetype): body = {'name': folder+'/'+fname, 'uploadType': 'multipart', 'contentType': mimetype} return GCS.objects().insert(bucket, body, blob).execute()
Next, vision_label_img() passes the binary to the Vision API and formats the results. Finally that information along with the file's archived Cloud Storage location are written as a single row of data in a Google Sheet via sheet_append_row().
vision_label_img()
sheet_append_row()
def vision_label_img(img): body = {'requests': [{'image': {'content': img}, 'features': [{'type': 'LABEL_DETECTION'}]}]} rsp = VISION.images().annotate(body=body).execute().get['responses'][0] return ', '.join('(%.2f%%) %s' % (label['score']*100., label['description']) for label in rsp['labelAnnotations']) def sheet_append_row(sheet_id, row): rsp = SHEETS.spreadsheets().values().append(spreadsheetId=sheet_id, range='Sheet1', body={'values': row}).execute() return rsp.get('updates').get('updatedCells')
Finally, a "main" program that drives the workflow is needed. It comes with a pair of utility functions, _k_ize() to turn file sizes into kilobytes and _linkify() to build a valid Cloud Storage hyperlink as a spreadsheet formula. These are featured here:
_k_ize()
_linkify()
def _k_ize(nbytes): # bytes to KBs (not KiBs) as str return '%6.2fK' % (nbytes/1000.) def _linkify(bucket, fname): # make GCS hyperlink to bucket/folder/file tmpl = '=HYPERLINK("storage.cloud.google.com/{0}/{1}/{2}", "{2}")' return tmpl.format(bucket, folder, fname) def main(fname, bucket, SHEET_ID, folder): fname, mtype, ftime, data = drive_get_img(fname) gcs_blob_upload(fname, folder, bucket, data, mtype) info = vision_label_img(data) sheet_append_row(SHEET_ID, [folder, _linkify(bucket, fname), mtype, ftime, _k_ize(data), info])
While this post may feature just pseudocode, a barebones working version can be accomplished with ~80 lines of actual Python. The rest of the code not shown are constants, error-handling, and other auxiliary support. The application gets kicked off with a call to main() passing in a filename, the Cloud Storage bucket to archive it to, a Drive file ID for the Sheet, and a "folder name," e.g., a directory or ZIP archive. Running it several times results in a spreadsheet that looks like this:
main()
Image archive report in Google Sheets
Developers can build this application step-by-step with our "codelab" - codelabs are free, online, self-paced tutorials - which can be found here. As you journey through this tutorial, its corresponding open source repo features separate folders for each step so you know what state your app should be in after every implemented function. (NOTE: Files are not deleted, so your users have to decide when to their cleanse Drive folders.) For backwards-compatibility, the script is implemented using older Python auth client libraries, but the repo has an "alt" folder featuring alternative versions of the final script that use service accounts, Google Cloud client libraries, and the newer Python auth client libraries.
Finally to save you some clicks, here are links to the API documentation pages for Google Drive, Cloud Storage, Cloud Vision, and Google Sheets. While this sample app deals with a constrained resource issue, we hope it inspires you to consider what's possible with Google developer tools so you can build your own solutions to improve users' lives every day!
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('friend@example.com', '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...
SpreadsheetApp
Maps
GmailApp
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.
Recently, we introduced the "Google Cloud for Student Developers" video series to encourage students majoring in STEM fields to gain development experience using industry APIs (application programming interfaces) for career readiness. That first episode provided an overview of the G Suite developer landscape while this episode dives deeper, introducing G Suite's HTTP-based RESTful APIs, starting with Google Drive.
The first code sample has a corresponding codelab (a self-paced, hands-on tutorial) where you can build a simple Python script that displays the first 100 files or folders in your Google Drive. The codelab helps student (and professional) developers...
Students and working professionals use Google Docs every day to help enhance their productivity and collaboration. The ability to easily share a document and simultaneously edit it together are some of our users' favorite product features. However, many small businesses, corporations, and educational institutions often find themselves needing to automatically generate a wide variety of documents, ranging from form letters to customer invoices, legal paperwork, news feeds, data processing error logs, and internally-generated documents for the corporate CMS (content management system).
Mail merge is the process of taking a master template document along with a data source and "merging" them together. This process makes multiple copies of the master template file and customizes each copy with corresponding data of distinct records from the source. These copies can then be "mailed," whether by postal service or electronically. Using mail merge to produce these copies at volume without human labor has long been a killer app since word processors and databases were invented, and now, you can do it in the cloud with G Suite APIs!
While the Document Service in Google Apps Script has enabled the creation of Google Docs scripts and Docs Add-ons like GFormit (for Google Forms automation), use of Document Service requires developers to operate within the Apps Script ecosystem, possibly a non-starter for more custom development environments. Programmatic access to Google Docs via an HTTP-based REST API wasn't possible until the launch of the Google Docs API earlier this year. This release has now made building custom mail merge applications easier than ever!
Today's technical overview video walks developers through the concept and flow of mail merge operations using the Docs, Sheets, Drive, and Gmail APIs. Armed with this knowledge, developers can dig deeper and access a fully-working sample application (Python), or just skip it and go straight to its open source repo. We invite you to check out the Docs API documentation as well as the API overview page for more information including Quickstart samples in a variety of languages. We hope these resources enable you to develop your own custom mail merge solution in no time!
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 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 service 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
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.
cell
fields
{ "repeatCell": { "range": { "endRowIndex": 1 }, "cell": { "userEnteredFormat": { "textFormat": { "bold": true } } }, "fields": "userEnteredFormat/textFormat/bold", } }
userEnteredFormat/textFormat/bold
"fields": "userEnteredFormat/textFormat(bold,italic)"
italic
addresses = GMAIL.users().settings().sendAs().list( userId='me' ).execute().get('sendAs')
{ "sendAs": [{ "sendAsEmail": string, "displayName": string, "replyToAddress": string, "signature": string, "isPrimary": boolean, "isDefault": boolean, "treatAsAlias": boolean, "smtpMsa": { "host": string, "port": integer, "username": string, "password": string, "securityMode": string }, "verificationStatus": string }, ...] }
addresses = GMAIL.users().settings().sendAs().list( userId='me', fields='sendAs(sendAsEmail,isPrimary)' ).execute().get('sendAs')