Slack To Google Sheets

• Slack To Email
• Connect Slack To Google Sheets
• Google Slack Download
• Download Slack
• Slack Google Integration

What is the pure value of Google Analytics? It is a know-it-all (or wise-ass :D) tool to learn about your website’s audience. With GA, you can get a detailed report on who, when, and how they navigated through your pages and, hence, understand their online behavior. So, the answer to the question “What do I need Google Analytics for?” is clear.

GA provides you with actionable data, which you can process using different tools such as Google Sheets, Excel, or even Data Studio. But first, you need to export the data. This is what we’re going to focus on below.

I am trying to build a slack dialog that will accept responses from team members about what they are working on and store that information in google sheets. I have the dialog working, but I'm not sure how to capture the information in google sheets using Google App Script. Set up the Google Sheets trigger, and make magic happen automatically in Slack. Zapier's automation tools make it easy to connect Google Sheets and Slack.

How to export Google Analytics to Google Sheets

The native export function in Google Analytics is quite simple. Choose the report you need, click the Export button and select Google Sheets as the file format. Additionally, you can export GA reports as CSV, Excel, and PDF files.

Once you have selected Google Sheets, you will be asked whether you want to import data to Google Sheets.

Click Import the data and welcome your Google Analytics report in a new spreadsheet created on your Google Drive. For example, this is what the Audience Overview report looks like in Google Sheets:

Which reports from Google Analytics you can import to Google Sheets

You can export you custom reports, as well as the following ones:

Flow reports (Users Flow, Behavior Flow, etc.) can be exported as PDF only; Multi-Channel Funnels reports can be exported as PDF and CSV files.

You can’t export any realtime reports, or Experiments (Behavior) or Lifetime Value (Audience). You can easily calculate the latter yourself – here is the customer lifetime value calculation guide.

How to automate Google Analytics reports importing into Google Sheets

The option above is good, but it’s manual. At the same time, you can automate import of Google Analytics to Google Sheets on a schedule without any coding or advanced manipulation. For this, you’ll need to complete three steps:

• Install the Google Analytics add-on from Google Workspace Marketplace.
• Create a report according to the parameters you want.
• Enable your report to run automatically.

Slack To Email

Add-on to pull Google Analytics data into Google Sheets

On the Google Workspace Marketplace, you can find the Google Analytics Sheets Add-on. It’s a free tool to connect Google Sheets to GA and query specific report data automatically on a schedule. Install it and then open the spreadsheet you want to export Google Analytics data to.

How to create a Google Sheets report based on Google Analytics data

In your spreadsheet, go to the Add-ons menu, select Google Analytics and click Create new report.

The dashboard for creating a new GA report will open on the right of your spreadsheet. It contains fields you’ll need to fill out to configure your Google Analytics report.

1) Name your report

Enter the name of your report

2) Select a view

Select your Google Analytics account, property and view. If you are unsure if you know them, go back to your Google Analytics dashboard and click All accounts. You’ll see the list of these parameters in a single window:

3) Choose configuration options

Google Analytics reports consist of dimensions and metrics.

• Metrics include specific measurements, such as New Users, Unique Events, Page Load Time, Sessions, etc.
• Dimensions break down metrics by specific criteria. For example, the New Users metric can include the User Type dimension; the Sessions metric can include the Session duration dimension; and so on.

The third field to configure is Segments. A segment is a subset of data based on dimensions and metrics. If you don’t want to segment users, leave this field blank, and the Google Analytics add-on will import All users.

Otherwise, you’ll have to specify a segment just like when you add a segment to your report in Google Analytics.

For example, if your metric is Users, you can specify one segment for users from the USA, another segment for users who visited your FAQ page, and so on.

Note: You can create a Google Analytics report with these fields empty and configure them later once the report is created.

Click Create Report and the add-on will add a Report Configuration sheet in your Google Sheets doc.

How to configure a Google Analytics report on the Report Configuration sheet

A Report Configuration sheet is not the report itself, but the sheet where you can change the configuration of your reports. It is used only to change the configuration of the created reports. To create additional reports, you’ll have to repeat the steps described above; or, just fill out the parameters in the respective cells in the next column to the right. The configuration of each new report will be added as a new column on the Report Configuration sheet.

Unlike the add-on dashboard with drop-down parameters, here you’ll have to insert parameters manually. So, if you want to add some metrics or change the time range of your report, the Report Configuration sheet is the only place where you can do this. Let’s check out which parameters you can configure.

Report Name [Required parameter]

You can change the name of your report. This parameter is also the name of the sheet (tab) where the Google Analytics data will be imported to.

View ID [Required parameter]

You can change the Google Analytics view of your report. For this, insert the View ID you want to report. You can find it by clicking All Accounts on the Google Analytics dashboard.

Start Date/End Date [Required parameter]

By default, the start and end dates in a new report have the following values:

• Start Date=30daysAgo
• End Date=yesterday

You can change these values using one of the following options:

• YYYY-MM-DD format, for example 2020-10-23
• Relative date such as today, yesterday, or NdaysAgo (N can be only a positive integer)
• One of the Google Sheets date functions to return the date value automatically. For example, this formula =EDATE(TODAY(), -1) will return the month ago date, and this formula =EOMONTH(TODAY(), -1) will return the last day of the previous month.

Metrics [Required parameter]

Enter the IDs of the metrics to query in your report. Separate them using commas; or enter each metric ID on a new line. For example,

We advise you to use a Google Analytics tool called Dimensions & Metrics Explorer. It lists and describes all possible options, and excludes the dimensions and metrics that cannot be queried together. For example, if you choose the metric 7 Day Active User, such dimensions as User Type or User Bucket, as well as metrics Users and Number of Sessions per User become inactive.

The following parameters are options, i.e. you can leave these fields blank.

Dimensions [Optional parameter]

You can enter the IDs of dimensions to query in your report. Separate them using commas, or enter each dimension ID on a new line. For example,

Dimensions & Metrics Explorer will help you here as well.

Order [Optional parameter]

You can specify the sorting order by column and direction. To sort by column, enter the metric/dimension IDs in the order you want them to return. For example,

ga:sessions, ga:bounces – the Sessions metric goes first

ga:bounces, ga:sessions – the Bounces metric goes first

The sort order direction is ascending by default. To change the direction to descending, use a minus sign (-) prefix on the field you want. For example,

ga:sessions, ga:bounces – the Sessions metric will return in ascending direction

-ga:sessions, ga:bounces – the Sessions metric will return in descending direction

Filters [Optional parameter]

You can specify conditions to filter out a dimension or metric in your Google Analytics report. This will let you exclude the rows that you don’t need in the report. Use the following syntax to tailor a filter query:

• {metric/dimension} – name of a metric or dimension to filter. For example, sessions, userType, etc.
• {operator} – a symbol that defines the type of filter to use. There are six operators for metrics and six operators for dimensions.
• {expression} – a case-insensitive regular expression that can not be longer than 128 characters. For example, ^New* is the regular expression for strings starting with New.

Operators for metrics

Operators for dimensions

Multiple filters

If you need to apply multiple filters using OR logic, separate the filters using a comma (,). For example, to filter out by countries either Germany or France, use

If you need to apply multiple filters using AND logic, separate the filters using a semicolon (;). For example, to filter out by countries Germany and France, use

Segments [Optional parameter]

Connect Slack To Google Sheets

Specify segments (set of users or sessions) to request. For example, the following segment string will select users from Berlin that have visited using the Firefox browser:

For more on segment syntax and usage, read the Google Analytics API documentation.

Limit [Optional parameter]

You can specify the maximum number of rows to return for your GA report. The default value is 1000. This parameter is recommended to use for reports that return thousands or millions of rows. If you leave this field blank, the Google Analytics add-on will make queries until all rows are returned (this can waste your API quota, which is 50,000 requests per project per day).

Spreadsheet URL [Optional parameter]

You can import the GA report to an external Google Sheets document. To do this, specify the spreadsheet URL in this field and make sure that you have edit permissions for it. By default, the report will be imported to the current Google Sheets document.

Skip Report [Optional parameter]

You can skip the import of a certain report or reports if you enter TRUE for this parameter. This option is useful when you have multiple reports and do not need to import all of them.

You can also apply Google Sheets formulas to set up this field. For example, the following expression will return TRUE (skip the report), if the value in B5 cell is less than 10:

Hidden parameters

The following parameters are hidden, so you need to click on the arrows to expand them. These include

• Report Type – if you enter mcf in this field, you can query the Multi-Channel Funnels Reporting API instead of the Analytics Reporting API v4 which is set by default. Why would you do this? MCF Reporting API provides a different set of metrics and dimensions that are not available in Analytics Reporting API v4; for example, mcf:adwordsPlacementUrl (URLs where your ads on the content network were placed), and many others.
• Sampling Level – you can change the sampling level (the number of sessions used to calculate the result) for a reporting query. Available options are:
  • DEFAULT — the default sampling level that uses balanced speed and accuracy to return the response.
  • FASTER — the sampling level that uses a smaller sample size to return a fast response.
  • HIGHER_PRECISION — the sampling level that uses a large sample size to return a more accurate response. This slows down the response.
• Use Resource Quotas – the field to enable the Resource Based Quota system – a feature available for Analytics 360 customers. If you’re one of them, set the parameter to TRUE.
• Exclude Empty Rows – set the parameter to TRUE if you need to exclude empty rows from your report.

That’s it for configuration!

Get data from Google Analytics to Sheets

Once you’ve configured your GA report, you can run it manually. Go to the Add-ons menu => Google Analytics => Run reports

Your Google Analytics report will be imported into a new sheet named accordingly.

How to automate Google Analytics reports into Google Sheets

The best thing about the Google Analytics add-on is that you can automate data importing. For this, navigate to your usual path, go to the Add-ons menu => Google Analytics, and select Schedule Reports.

Then check “Enable reports to run automatically” and choose the schedule.

Click Save and that’s it. All your configured reports will be imported according to the specified schedule!

How to remove reports from the schedule

If you need to ignore some of the created reports, you can either use the Skip Report parameter, or simply delete all the data from the respective columns of your reports. After that they won’t be available.

Google Analytics in Google Sheets: Combine your reports with other imported data

The Google Analytics add-on only imports data from GA to Google Sheets. But you can enhance your spreadsheet with other add-ons and solutions to import a variety of other data.

With Coupler.io, you can import data to Google Sheets from Airtable, WordPress, Clockify, ProfitWell and many other sources. This will let you accumulate data in one place and manipulate it much more efficiently.

Additionally, Coupler.io provides a Google Sheets importer – an advanced alternative to the native IMPORTRANGE function in Sheets. The Google Sheets importer lets you automate import of data from one sheet or spreadsheet to another on a schedule, without any of the errors associated with IMPORTRANGE.

How to use Google Sheets for analytics

Having your GA data in spreadsheets opens wide opportunities for processing. You can create custom dashboards, sales trackers or monitors, as well as visualize data in the way you need. For example, our team uses the Website Traffic Monitor built atop the report imported from Google Analytics. Here is the report configuration we use:

The traffic data from GA is then filtered using the Google Sheets FILTER function. The final view of the monitor is based on a pivot table that lets you see how many views each page had every week historically.

This is only one use case of how you can analyze your GA data in Sheets and there are many more!

Are there other options to connect Sheets to Google Analytics?

Actually, the Google Analytics add-on is not a single option to import data. You can go to Google Workspace Marketplace, enter “Google Analytics” into the search bar and check out the alternatives. Are they good enough? To answer this question, just pay attention to the number of installs compared to the GA add-on.

Moreover, the Google Analytics add-on is free, while its alternatives that provide import from multiple sources, including Google Analytics, are paid options. Anyway, it’s up to you to decide. Good luck with your data!

Over the past few years, there have been several instances where we needed to get information out of our Google spreadsheets and into a Slack message. To do that, we use a few simple Google Apps Script functions, along with the Slack API. In this video, we show you how to do it.

Transcript/instructions.

It’s fair to say, I’m a pretty big fan of Google Sheets and Apps Script. It’s hard not to be. They have the ability to integrate with other Google Services, query external services via Add-Ons and APIs, share data between sheets, import data from around the web and much more. In fact, there are seemingly endless possibilities.

As a result, we run a lot of things through Google Sheets here at August. We also use Slack. Slack works perfectly for both our internal communication (extremely useful during the home working/COVID-19 period) and as a way of connecting and sharing information quickly and transparently with our clients. As a result, over the past few years, there have been several instances where we needed to get information out of our spreadsheets and into a Slack message. To do that, we use a few simple Google Apps Script functions, along with the Slack API.

Here’s how you do it.

How to send Slack Alerts from Google Sheets / Google Apps Script.

Note: You’ll need some basic experience of Google Apps Script and/or JavaScript to understand everything in this article. If you don’t, go and check out the Google Apps Script docs first.

If you’re ready to get started, let’s go through each step in detail.

Step 1: Create a custom app in Slack.

To kick things off, make sure you are logged into your Slack account via your browser. Once logged in, go to https://api.slack.com. Everything you need to know about the Slack API is here.

To set up your new custom app, click on ‘Your Apps’ in the main nav:

The Slack API home screen

Followed by ‘Create New App’:

Your Slack Apps

A modal will load, where you can provide a name for your App. Select your company’s development workspace (if you are only a member of one company, there should only be one option here), and hit ‘Create App’:

Creating a new Slack app

You have just created a Slack App!

It doesn’t do anything just yet though… you need to do one more thing to allow Google Apps Script to communicate with your App, and that’s to set up something called a ‘webhook’. Without going into too much detail, a webhook is a mechanism that allows you to send information from an external service to your Slack App.

Let’s set up our webhook. From the left hand menu, select ‘Incoming Webhooks’:

Webhooks

Toggle ‘Activate Incoming Webhooks’ to on, and then click the ‘Add New Webhook to Workspace’ button:

Adding your webhook

Finally, select the Slack channel that you want the Slack alert to appear in and click ‘Allow’:

Choosing a channel

Tip: While you are testing your Slack alert, I recommend you select a Slack channel that only you have access to. This allows you to play around with how the Slack alert will appear and fix any presentation issues before you release your alert ‘in the wild’. Once you are ready to start sending alerts to a shared channel, set up a different webhook for that channel.

Your Slack App is now ready to start receiving data from an external service. Happy days! Keep this browser tab open though, you’ll need to come back to it later.

Step 2: Fetch data from your spreadsheet using Google Apps Script.

Now that you have set up your Slack App, you are ready to send it some data. For the purposes of this article, I have set up a simple Google spreadsheet with dummy data – feel free to make a copy of this spreadsheet (make sure you are logged into a Google account and then go to File > Make a copy), or alternatively use your own Google spreadsheet (either is fine).

Our source data spreadsheet

Our dummy data is for a fictional charity. The spreadsheet contains a simple table listing fundraising revenue for the past month. You want to get this data into a Slack alert.

Once your spreadsheet is ready, you need to access Google Apps Script. To do that, click Tools > Script editor:

Opening the Script editor

Note: If you copied the template spreadsheet above, all of the required code will already be in place (well, all apart from your webhook URL, which we’ll get to in step 4). If, however, you are starting from scratch with your own spreadsheet, the script editor will be empty and you will need to follow the steps below to set things up.

To get started, give your script a meaningful name, and then replace the empty function at the top with the following:

This is your master function. It is a very simple function which, if you have any experience of Apps Script, you will likely recognise.

This line is responsible for getting the data from cells A1:B6:

If you are using your own spreadsheet, just update the range to reflect your own data.

Once you have your data, the function sends that data to another function called ‘buildAlert’ (which we will talk about in step 3), and then sends the returned data from the buildAlert function to another function called ‘sendAlert’ (which we will talk about in step 4).

That’s it. You now have data, so you’re ready to start transforming that data into the right format.

Step 3: Transform the data into a format understandable by Slack.

The data you just fetched in the previous step is in JavaScript array format. You can’t send it to Slack in this format – Slack won’t understand it. Instead, it needs to be converted into specially-structured JSON that Slack will understand. Slack calls this structure ‘Blocks‘. Slack’s documentation is great, so I won’t repeat it here. Simply take a quick peek at the link above, and then come back here.

When you’re ready, let’s create our ‘buildAlert’ function which takes our data from step 2 as an argument:

This is a slightly longer function than our first, so let’s break it down. You want your Slack alert to look like this:

What we want our alert to look like

Google Slack Download

As you’ll see, our alert has 5 sections:

1. The alert title
2. The horizontal divider below the title
3. The ‘total fundraising revenue’ sentence
4. The sentence introducing our breakdown of revenue
5. The breakdown of revenue itself

Each of the above elements is represented by an object within the “blocks” array of your ‘payload’ variable:

As you can see, building the structure of an alert is fairly simple. Each section within the alert is just an element in the ‘blocks’ array. If you want to add another section, just insert another element into the array!

Tip: Slack’s Block Kit Builder is an awesome place to experiment with the JSON format. Play around with things in there before you set up your ‘payload’ variable in Apps Script.

You might have noticed we skipped a few lines in our function. Let’s cover those off now.

The first line in our function simply gets your ‘total revenue’ figure from your data, which is in the first row and second column of our data array (remembering that JavaScript arrays are zero-based i.e. the ‘0’ = row 1 and the ‘1’ = column 2).

The subsequent code block takes the remaining data in the array (representing range A2:B6 in the spreadsheet) and turns it from an array into a text string, formatted the way we want:

For those of you using your own data, you might need to play around with this section of code to get things looking right.

Download Slack

Step 4: Send the alert to Slack.

Step 3 is probably the hardest part, and once you’ve got that mastered, the rest is a breeze. Our final function is your ‘sendAlert’ function, which – you guessed it – sends the formatted alert to Slack. Here it is:

Slack Google Integration

As you’ll see, sendAlert takes the payload you just created in step 3 as an argument. The ‘options’ object simply makes sure we are making the right type of request, in the right format, then the ‘UrlFetchApp.fetch’ part makes the request. We wrap it in a try/catch block, just in case something goes wrong.

The only part you need to do is paste in your webhook URL that you created in step 1. You can get that from your app’s ‘incoming webhooks’ page that we talked about in step 1:

Getting your webhook URL

Once you’ve pasted that in to the ‘webhook’ variable, you’re done! That’s all the code you need.

The final step is to give it a test. In Apps Script, select the ‘buildReport’ function from the toolbar, and click the Run icon:

Let’s give it a whirl!

Note that the first time you do this, Apps Script will ask you for permission to run the script. If you see a modal that looks like this:

Skipping through the security warning

…just go ahead and click on ‘Advanced’ followed by ‘Go to Super Awesome Slack App (unsafe)’ (or whatever you called your script in step 2). Don’t worry about the annoyingly worded ‘unsafe’ warning – this very simple app is not unsafe! You will also only have to do this once.

Once you’ve accepted all the permissions and the script has run, head on over to Slack, and voila, the Slack alert should be sitting there in the channel you chose in step 1.

Bonus step: Set up scheduled alerts.

Setting up scheduled alerts is incredibly useful. Imagine you want to send an alert once per week. You can set up a ‘trigger’ within Apps Script to make that happen.

To set up a trigger, select ‘Current project’s triggers’ from the Edit menu and then set up a ‘Time Driven’ trigger on the ‘buildReport’ function, setting the frequency to whatever makes sense to you:

Adding a trigger

Obviously this would be pretty pointless with our example spreadsheet because it only contains static data. But for those of you with spreadsheets that are dynamically updating over time, a regular alert in Slack informing you and your team of the latest information could be pretty useful. No more data hidden away in sheets – your whole team has data at their fingertips, when they need it, right there in Slack.

***

That’s it! I hope you found this post useful. Take care, happy coding, and good luck!