1. Cheat Sheet Markdown
2. Markdown To Google Doc

There are many tools out there for data scientists to perform big data analyses, but at the end of the day, don't you still have to justify those results to management? Lots of numbers on paper or in a database is hardly presentable to key stakeholders. This intermediate Google Apps Script codelab leverages a pair of Google's developer platforms, G Suite and Google Cloud Platform (GCP), to help you complete that final mile.

1. I want to embed Google docs - and possibly Google sheets - in a GitLab markdown file. GitLab's markdown guide lists the following steps to do this. Open your Google document; click File- Publish to the web; choose Embed; click on Publish and copy the; go to your markdown file and wrap the iframe into a tag with the responsive videocontainer class like in this example.
2. Table to Markdown is a simple Markdown table generator that converts tables from spreadsheet applications and websites into well-formatted Markdown tables. Just paste a table from a spreadsheet or webpage and click 'Convert'. Learn how to: Convert Excel to Markdown; Convert HTML to Markdown; Convert Google Sheets to Markdown.

Google Cloud's developer tools let you perform the deep data analysis then take those results, put them into a spreadsheet, and generate a slide presentation with that data, providing a more suitable stage for the results to be delivered to management. This codelab covers the GCP's BigQuery API (as an Apps Script advanced service) and the built-in Apps Script services for Google Sheets and Google Slides.

Table to Markdown makes it easy to convert cells from Microsoft Excel, Google Sheet, LibreOffice Calc, or any modern spreadsheet application, into Markdown. Need to convert from Excel to Markdown? When copying cells from Excel and other spreadsheet applications, this data is stored in your clipboard as text and as an HTML table. This is how you can copy cells from Microsoft Excel, paste. This excellent Excel or Google Sheets to Markdown conversion tool is the brainchild of software developer Dave Johnson, and is hosted on his own blog.

• Node.js app vs. our Apps Script app
• Uses REST APIs while we use Apps Script services
• Uses Google Drive but not Google Sheets whereas this app uses Sheets but not Drive

For this codelab, we wanted to bring as much of the technologies together into a single app while showcasing features and APIs from across Google Cloud in a way that better resembles a real-life use case. The goal is to inspire you to use your imagination and consider leveraging both GCP & G Suuite to solve challenging problems for your organization or your customers.

• How to use Google Apps Script with multiple Google (GCP & G Suite) services
• How to use Google BigQuery to perform a big data analysis
• How to create a Google Sheet and populate data into it
• How to create a new chart in Sheets
• How to transfer charts and data from Sheets into a Google Slides presentation

• Access to the internet and a web browser
• A Google account (G Suite accounts may require administrator approval)
• Basic JavaScript skills
• Knowledge of Apps Script development may be helpful but not required

How will you use this codelab/tutorial?

Read through it for informational purposes, possibly passing it on to technical colleaguesGo through it as far as I can and try as many of the exercises as I canCome hell or high water, I'm going to complete the entire codelab

How would rate your experience with G Suite developer tools & APIs?

NoviceIntermediateProficient

How would rate your experience with Apps Script specifically?

NoviceIntermediateProficient

How would rate your experience with GCP developer tools & APIs?

NoviceIntermediateProficient

Now that you know what this codelab is about, what are you going to do exactly?

1. Take an existing Apps Script-BigQuery sample and get it working
2. From that sample, learn how to send a query to BigQuery and get its results
3. Create a Google Sheet and populate the results from BigQuery into it
4. Modify the code a bit to slightly alter the data returned and added to the Sheet
5. Use the Sheets service in Apps Script to create a chart for the data from BigQuery
6. Use the Slides service to create a new slide presentation
7. Add a title and subtitle to the default title slide auto-created for all new slide decks
8. Create a new slide with a data table, then import the Sheet's data cells into it
9. Add another new slide and add the spreadsheet chart to it

Let's get started with some background information on Apps Script, BigQuery, Sheets, and Slides.

It is server-side JavaScript, similar to Node.js, but focuses on tight integration with G Suite and other Google services rather than fast asynchronous event-driven application hosting. It also features a development environment that may be completely different from what you're used to. With Apps Script, you:

• Develop in a browser-based code editor but can choose to develop locally if using clasp, the command-line deployment tool for Apps Script
• Code in a specialized version of JavaScript customized to access G Suite, and other Google or external services (via the Apps Script URLfetch or Jdbc services)
• Can avoid writing authorization code because Apps Script handles it for you
• Do not have to host your app—it lives and runs on Google servers in the cloud

NOTE: It is, for the most part, outside of the scope of this codelab to teach you Apps Script. There are plenty of online resources to help you with this. The official documentation features an overview with quickstarts, tutorials, and videos as well. And finally, don't forget about the Apps Script introductory codelab, which should be completed before starting this one.

Apps Script interfaces with other Google technologies in two different ways:

• built-in/native service
• advanced service

A built-in service provides high-level methods that you can use to access to G Suite or Google product data, or other useful utility methods. An advanced service is merely a thin wrapper around a G Suite or Google REST API. Advanced services provide full coverage of the REST API and can often do more than the built-in services, but require more code complexity (while still being easier to use than the REST API itself). Advanced services must also be enabled for a script project prior to using them.

When possible, developers should prefer a built-in service because they're easier to use and do more heavy-lifting than advanced services do. However, some Google APIs don't have built-in services, so an advanced service may be the only option. Google BigQuery is one example of this... no built-in service is available, but a BigQuery advanced servicedoes exist. (Better than no service, right?) If you're new to BigQuery, it's a GCP service that lets you perform simple (or complex) queries on very large data corpuses, say on the order of multiple terabytes, yet still can provide results in seconds.

• Start a new Google Apps Script project
• Enable access to the BigQuery advanced service
• Go to the development editor and enter the application source code
• Go through the app authorization process (OAuth2)
• Run the application which sends a request to BigQuery
• View a brand new Google Sheet created with the results from BigQuery

a) Create a new Apps Script project by going to script.google.com . There are different G Suite product lines, and how you create a new project may differ depending on which version you're using. If you're just using your Gmail account and are new to developing projects, you'll see a blank screen along with a button to create your first project:

b) Otherwise you may see all your projects and a big +New button in the upper-left, so click it.

c) If neither of the above, your screen may look like the below. If so, look for a hamburger menu icon in the upper-left corner and select +New script.

d) For those of you who prefer the command-line. Your tool is clasp, specifically, you'll run the clasp create command.

e) The final way to create a new script project is to simply go to the shortcut link: https://script.google.com/create.

2. Regardless of which technique you used to start a new project, the bottom line is that you should be dropped into the Apps Script code editor, a screen that looks like this:

3. Click File > Save and give your project a name.
4. Next you'll need to create a Google Cloud Console project in order to run BigQuery queries.

Note: If you are running this codelab from a kiosk at a Google-hosted event, then a Google Cloud Console project is already created for you. Open that project and skip to item #7 below.

5. Create a new project, give it a name, select a Billing Account, and click CREATE.

Note: BigQuery is a paid service, with costs associated with both storing and querying data. This codelab is only querying data (and only a small amount at that) and the usage should fall into the free tier. For more information see the BigQuery pricing guide.

6. When the project creation is complete a notification appears in the upper-right of the page. Click on the Create Project: <Project Name> entry to open the project.
7. Click on the menu icon in the upper left and navigate to APIs & Services > Credentials. Click on the OAuth consent screen tab ( direct link).
8. In the field Application name enter 'Big Data Codelab' and click the Save button at the bottom.
9. Click the three dots icon in the upper-right to expand the menu and select Project settings ( direct link).
10. Copy the value listed under Project number. (There is a separate field Product ID that we'll use later on in the codelab.)
11. Back in the App Script Editor, click Resources > Cloud Platform project.
12. Enter the project number into the text box and click Set Project. When prompted, click Confirm.
13. When complete, click the Close button to dismiss the dialog.
14. Now that you have a new project setup, you need to enable the BigQuery advanced service, so pull down Resources -> Advanced Google Services and flip on the bit for the BigQuery API.

15. A note at the bottom states, 'These services must also be enabled in the 'Google Cloud Platform API Dashboard', so click on that link which opens another browser tab to the developer console or 'devconsole' for short.
16. At the devconsole, click the +Enable APIs and Services button at the top, search for 'bigquery', select the BigQuery API (not the BigQuery Data Transfer API), and click enable to turn it on. Leave this browser tab open.

NOTE: after the API has been enabled, you may see a note on this page saying something like, 'To use this API, you need to create credentials...,' but don't worry about that for now—Apps Script will take care of this step for you.

17. Back in your code editor browser tab, you're still on the Advanced Google Services menu, so click OK to close the dialog, leaving you in the code editor. Click the project name at the top, and name it whatever you like, 'BigQuery demo' or similar—we named ours 'final mile'.

Now you're ready to enter the application code, go through the authorization process, and get the first incarnation of this application working.

Copy the code in the box below and paste over everything in the code editor with it:

Now save the file you just created but rename it from Code.gs to bq-sheets-slides.js. So what does this code do? We already told you that it queries BigQuery and writes the results into a new Google Sheet, but what is this query? You can see it near the top of runQuery():

This query looks through Shakespeare's works, part of BigQuery's public data set, and produces the top 10 most frequently-appearing words in all his works, sorted in descending order of popularity. Imagine how (not) fun it would be to do this by hand, and you should have an inkling of an idea of how useful BigQuery is.

2. We're almost but not quite ready to give this a try. As you see near the top of this code snippet, a valid project ID is required, so we need to add yours to the application code. To get this, go back to the browser window or tab that has the developer console page. (We told you to leave it open, remember?)
3. Up at the top to the left of your Google account avatar is the pulldown menu selector ( ). Click it and select Project settings. You'll see the project name, ID, and number. Copy the project ID and set the PROJECT_ID variable at the top of bq-sheets-slides.js to the value you got from the developer console. NOTE: If the menu selector gets sticky and inoperable, reload the page.
4. The if statement is there to prevent the application from going any further without a project ID in place. Once you've added yours, save the file, and run your code by going up to the menubar and selecting Run > Run function > runQuery, click Review Permissions dialog, This app is unverified, and below is an animated GIF (for another app) illustrating the next few steps:
5. Once you request to review the permissions, you'll be presented with a new dialog, as shown above. Choose the correct Google account who will be running the script, select Advanced, scroll down, then click 'Go to <YOUR PROJECT NAME> (unsafe)' to arrive at the OAuth2 application authorization screen. (Read more about the verification process to learn about why this screen is sitting in between you and the OAuth2 authorization dialog below.)

NOTE: Once you authorize the app, once you don't need to repeat this process with each execution. It isn't until you get to Task 3 further on in this tutorial that you see this dialog screen again, asking for user permission to create and manage Google Slides presentations. 6. Once you click Allow on the OAuth2 dialog window, the script starts running... you'll see a pastel yellow dialog at the top. It runs fairly quickly, so you may not notice that it's running or that execution has completed.

7. That dialog disappears once it's done, so if you don't see it, it's probably finished, so go to your Google Drive ( drive.google.com) and look for a new Google Sheet named 'Most common words in all of Shakespeare's works' or whatever you assigned to the QUERY_NAME variable:

8. Open the spreadsheet, and you should see 10 rows of words and their total counts sorted in descending order:

The code for bq-sheets-slides.js (our chosen filename) you pasted in above (aside from PROJECT_ID which should have a real project ID) can also be found in the step1 folder in this codelab's GitHub repo at github.com/googlecodelabs/bigquery-sheets-slides. The code was inspired big the original example in the BigQuery advanced services page which ran a slightly different query... what are the most popular words used by Shakespeare with 10 or more characters. You can also see that sample in its GitHub repo.

If you're interested in other queries you can try against Shakespeare's works or other public data tables, check out this web page as well as this one. Regardless of what query you use, you can always just test the query in the BigQuery console before running it in Apps Script. BigQuery's user interface is available to developers at bigquery.cloud.google.com. For example, here's what our query looks like using the BigQuery UI:

While the steps above leveraged Apps Script's code editor, you can also choose to develop locally via command-line. If you prefer, create a script named bq-sheets-slides.js, paste the code above into it, then upload to Google with the clasp push command. (If you missed it earlier, here again is the link to clasp and how to use it.)

The purpose of runQuery() is to talk to BigQuery and send its results into a Sheet. Now we need to make a chart with the data. Let's make a new function called createColumnChart() that calls the Sheet's newChart() method to do this.

1. Create chart. Add the body of createColumnChart() featured below to bq-sheets-slides.js right after runQuery(). It gets the sheet with data and requests a columnar chart with all the data. The data range starts at cell A2 because the first row contains the column headers, not data.

2. Return spreadsheet. Above, createColumnChart() needs the spreadsheet object, so we need to update runQuery() to return spreadsheet object so we can pass it to createColumnChart(). After logging the successful creation of the Google Sheet, return the spreadsheet object at the end of runQuery(), right after the log line:

3. Driving createBigQueryPresentation()function. Logically segregating the BigQuery and chart-creation functionality is a great idea. Now let's create a createBigQueryPresentation() function to drive the app, calling both runQuery() and createColumnChart(). The code you add should look something like this:

4. Make code more reusable. You took 2 important steps above: returning the spreadsheet object and creating a driving function. What if a colleague wanted to reuse runQuery() and doesn't want the URL logged? To make runQuery() more digestible for general usage, we need to move that log line. The best place to move it? If you guessed createBigQueryPresentation(), you'd be correct! After moving the log line, it should look like this:

With these changes above (again except for PROJECT_ID), your bq-sheets-slides.js should now look like the following (and also found in the step2 folder of the GitHub repo):

Save the file, then go up at the top of the code editor and switch to execute createBigQueryPresentation() instead of runQuery(). After running it, you'll get another Google Sheet, but this time, a chart shows up in the Sheet next to the data:

The final part of the codelab involves creating a new Google Slides presentation, filling the title and subtitle on the title slide, then adding 2 new slides, one for each of the data cells and another for the chart.

1. Create slide deck. All of the work on the slide deck will take place in createSlidePresentation() which we're going to add to bq-sheets-slides.js right after createColumnChart(). Let's start with the creation of a new slide deck, then add a title and subtitle to the default title slide we get with all new presentations.

2. Add data table. The next step in createSlidePresentation() is to import the cell data from the Google Sheet into our new slide deck. This code snippet, so add it to the function:

3. Import chart. The final step in createSlidePresentation() is to create one more slide, import the chart from our spreadsheet, and return the Presentation object. Add this final snippet to the function:

4. Return chart. Now that our final function is now complete, take another look at its signature. Yes, createSlidePresentation() requires both a spreadsheet and a chart object. We've already adjusted runQuery() to return the Spreadsheet object but now we need to make a similar change to createColumnChart() to return the chart (EmbeddedChart) object. Go back in your application to code to add one last line at the end of createColumnChart() to do that:

5. Update createBigQueryPresentation(). Since createColumnChart() returns the chart, we need to save that chart to a variable then pass both the spreadsheet and the chart to createSlidePresentation(). Since we log the URL of the newly-created spreadsheet, let's also log the URL of the new slide presentation. Update your createBigQueryPresentation() so it looks like this:

6. Save and run createBigQueryPresentation() again. Before it executes though, recognize your app now needs one more set of permissions from your user to view and manage your Google Slides presentations. Once you allow this permission, it'll run as before.
7. Now in addition to the Sheet that's created, you should also get a new Slides presentation with 3 slides (title, data table, data chart), as shown below:

Congratulations! You've now created an application that leverages the both sides of Google Cloud by performing a Google BigQuery request that queries one of its public data sets, creates a new Google Sheet to store the results, adds a chart based on that just-retrieved data, and finally creates a Google Slides presentation featuring the results as well as chart in the spreadsheet.

That's what you did technically. Broadly speaking, you went from a big data analysis to something you can present to stakeholders, all in code, all automated. We hope this sample inspires you to take this and customize it for your own projects. At the conclusion of this codelab, we'll provide some suggestions as to how you can further enhance this sample app.

With changes above from the final task (again except for PROJECT_ID), your bq-sheets-slides.js should now look like the following (and also found in the final folder in the GitHub repo):

appsscript.json. You can access it by going to the code editor browser tab and selecting View > Show manifest file from the menu at the top. The contents should look something like this:

Resources for this application

• This codelab link: g.co/codelabs/bigquery-sheets-slides
• Source code repo
• Developer video (and) blog post
• Google Cloud NEXT ‘18 session

Related and general videos

• Another Google (Apps) secret? (Apps Script intro video)
• Accessing Google Maps from a spreadsheet?!? ( video)
• Google Apps Script video library
• Launchpad Online video series (predecessor to...)
• G Suite Dev Show video series

Other codelabs

Intermediate

• [Apps Script] CLASP Apps Script command-line tool
• [Apps Script] Gmail Add-ons
• [Apps Script] Docs Add-on & GCP Natural Language API
• [Apps Script] Hangouts Chat bot framework
• [REST APIs] Custom reporting tool (Sheets API)
• [REST APIs] Custom slide generator for Github license BigQuery analyzer (Slides+BigQuery APIs)

Cheat Sheet Markdown

Below are various 'code challenges,' different ways you can enhance or augment the sample we built in this codelab. This list certainly isn't exhaustive but should provide some inspirational ideas of where you can take the next step.

Markdown To Google Doc

• Application. Don't want to be limited by using JavaScript or restrictions imposed by Apps Script? Port this application to your favorite programming language that uses the REST APIs for Google BigQuery, Sheets, and Slides.
• BigQuery. Experiment with a different query for the Shakespeare dataset... perhaps find a query that interests you. Another sample query can be found in the original Apps Script BigQuery sample app.
• BigQuery. Experiment with some of BigQuery's other public data sets... perhaps find a data set that may be more meaningful for you.
• BigQuery. Earlier, we mentioned other queries you can try against Shakespeare's works or other public data tables, and wanted to reshare this web page as well as this one again.
• Sheets. Experiment with other Chart types.
• Sheets & BigQuery. Turn the tables around... perhaps you have a large data set in a spreadsheet somewhere. In 2016, the BigQuery team introduced the feature allowing developers to use a Sheet as the data source (see blog post one and two for more information).
• Slides. Add other slides to the generated presentation, such as images or other assets tied to your big data analysis. Here's a guide to the Slides built-in service to get you started.
• G Suite. Find use for other G Suite or Google built-in services from Apps Script, i.e., Gmail, Google Drive, Calendar, Docs, Maps, Analytics, YouTube, etc., as well as other advanced services. See the reference overview for both built-in and advanced services for more information.