Google Sheets Macros are small programs you create inside of Google Sheets without needing to write any code. They allow you to automate repetitive tasks. They work by recording your actions as you do something and saving these actions as a “recipe” that you can re-use again with a single click.
They sure are! Read on to learn how to use them, see some examples, discover their limitations and also see how they’re a great way into the wonderful world of Apps Script coding 😀
The engineers at Google continue to roll out updates to Data Studio at a relentless and impressive pace.
Back in September they released community connectors, which allow you to connect any web service with an API to Data Studio for reporting.
Yesterday, they released an update which gives developers much more control over the connector fields in Data Studio, by allowing developers to define Data Types and Semantic Types with more granularity, in your Apps Script code.
Additionally, developers can now embed calculated fields into the connector’s schema too, so it’s not left up to the user to figure this out.
This is a huge improvement as it obviates the need for the end user to select the correct field settings (for example, which aggregation to use) and should therefore make it easier for users to build accurate reports.
Data Type and Semantic Type features in the connector fields
Data Studio is relatively new dashboard tool from Google, launched in mid-2016. It’s a superb tool for creating professional looking reports, easily and quickly, and it connects seamlessly to other Google data sources (e.g. Analytics, AdSense, Sheets, …).
Do you work with data outside of Google’s ecosystem though?
I’ll go out on a limb here, and say, yes, most likely you do.
Perhaps you’re a digital marketing analyst looking at Facebook, Twitter, Instagram, MailChimp data (etc…) for example.
Many of us work with other web services and you want that data displayed in Data Studio. With the launch of native data Community Connectors, you can connect to your favorite web services and access data that lives outside the Google ecosystem directly!
This post shows you how to connect a Google Sheet to GitHub’s API, with Oauth and Apps Script. The goal is to retrieve data and information from GitHub and show it in your Google Sheet, for further analysis and visualization.
If you manage a development team or you’re a technical project manager, then this could be a really useful way of analyzing and visualizing your team’s or project’s coding statistics against goals, such as number of commits, languages, people involved etc. over time.
Note, this is not a post about integrating your Apps Script environment with GitHub to push/pull your code to GitHub. That’s an entirely different process, covered in detail here by Google Developer Expert Martin Hawksey.
The key to this line graph in Google Sheets is setting up the data table correctly, as this allows you to show an original data series (the grey lines in the animated GIF image), progress series lines (the colored lines in the animated GIF) and current data values (the data label on the series lines in the GIF).
In this example, I have date and times as my row headings, as I’m measuring data across a 4-day period, and sales category figures as column headings, as follows:
The red column, labeled with 1 above, contains historic data from the 2015 sale.
Red column 2 is a copy of the same data but only showing the progress up to a specific point in time.
In red column 3, the following formula will create a copy of the last value in column 2, which is used to add a value label on the chart:
Purple columns 4,5 and 6 are exactly the same but for 2016 data. The formula in this case, in column 6, is:
Data in green columns 7 and 8, is our current year data (2017), so in this case there is no column of historic data. The formula in column 8 for this example is:
Creating the line graph in Google Sheets
Highlight your whole data table (Ctrl + A if you’re on a PC, or Cmd + A if you’re on a Mac) and select Insert > Chart from the menu.
In the Recommendations tab, you’ll see the line graph we’re after in the top-right of the selection. It shows the different lines and data points, so all that’s left to do is some formatting.
Format the series lines as follows:
For the historic data (columns 1 and 4 in the data table), make light grey and 1px thick
For the current data (columns 2, 5 and 7 in the data table), choose colors and make 2px thick
For the “max” values (columns 3, 6 and 8 in the data table), match the current data colors, make the data point 7px and add data label values (see steps 1, 2 and 3 in the image below)
This is the same technique I’ve written about in more detail in this post:
How about creating an animated version of this chart?
Oh, go on then.
When this script runs, it collects the historic data, then adds that data back to each new row after a 10 millisecond delay (achieved with the Utilities.sleep method and the SpreadsheetApp.flush method to apply all pending changes).
I don’t make any changes to the graph or create any fancy script to change it, I leave that up to the Google Chart Tool. It just does its best to keep up with the changing data, although as you can see from the GIF at the top of this post, it’s not silky smooth.
On lines 6 and 7, the script grabs the historic data for 2015 and 2016 respectively. For the contemporary 2017 data, I’ve created an array in my script to hold those values, since they don’t exist in my spreadsheet table.