Upload Google Spreadsheet Chart to Slack

This post will show you how to automatically send a Google Spreadsheet chart to Slack on a schedule - no programming skills required. 10 minutes from now, you will be able to start sending that sales, growth, or financial report you created in Google Sheets to your team on Slack every week, day, or even every hour, completely automatically.

Let’s go!

From Google Sheets to Slack

There will be three parts of the solution: a Slack Application, the Google Sheets spreadsheet, and a piece of associated AppsScript (a Google script language for adding custom behaviors to Google apps). You will see some code along the way, but don’t worry - no programming skills are required. You will only copy & paste a few lines of it.

Your Google Spreadsheet contains the chart you want to send to Slack. We will associate a piece of a custom script with the spreadsheet and configure it to run regularly, for example daily. The script will get an image of a chart you created in the Google Spreadsheet, and upload it as a file to Slack using Slack APIs and the Slack Application you will register.

Google Sheet With a Chart

If you don’t have one already, create a Google Spreadsheet with a chart you want to send to Slack. For example, it could be a sales report that looks like this:

Take note of the chart title as you will need it later to identify the chart you want to upload to Slack. In the example above, the chart title is Sales.

Create Slack Application

You need to register a new Slack Application in your Slack workspace to be able to upload your chart to Slack using Slack APIs.

Go to https://api.slack.com/apps and log in with your Slack credentials. Click the big green Create New App button and choose the From scratch option:

On the next screen, give your app a name, say GSheet Reports, and select the Slack workspace you want to add it to. Click Create App.

You will end up on a configuration page of your new Slack App. Select the OAuth & Permissions section in the navigation panel on the left, and then scroll down until you find the Scopes section. In the Bot Token Scopes subsection, click the Add an OAuth Scope button, then select and add the files:write scope. Your screen should now look like this:

Scroll back up in the OAuth & Permissions section until you find OAuth Tokens for your Workspace. Click the Install to Workspace button there, and authorize your new Slack App in your selected Slack workspace. When this process is done, take note of the Bot User OAuth Token that was generated. It will be used later when uploading your Google Sheets chart to Slack.

Lastly, in your Slack workspace itself, determine the channel you want to send the charts from Google Sheets to, and invite the Slack App you just created to this channel. Slack Apps can only send messages to channels they were invited to.

You can invite your Slack App to a channel by mentioning its name in a message (e.g. @GSheet Reports), pressing enter, and confirming the invitation of the app to the channel:

Take note of the channel name you invited your Slack App to.

That’s it as far as Slack is concerned - let’s go back to your Google Spreadsheet to configure it.

Use AppsScript to Send GSheet Chart to Slack

Back in the Google Spreadsheet, go to Extensions | Apps Script. In a new tab, the Apps Script editor will open up. Change the name of the project to something familiar, e.g. GSheet Reports, then highlight and delete all the code from the panel showing the myFunction function. You should have a clean slate like this:

Now, copy & paste the code shown below to the Apps Script code panel:

// Title of the chart to send to Slack as it appears in GSheet
const chartTitle = 'Sales';
// Slack Bot Token from the Slack App configuration page
const slackBotToken = 'xoxb-000000000000-0000000000000-SkjhDgDkjhSKJShsSKJ';
// Comma delimited channel IDs or names
const slackChannels = 'sales-report';

function sendChartToSlack() {
  // Get chart with the specified title
  const chart = SpreadsheetApp.getActiveSheet()
    .getCharts()
    .find((chart) => chart.getOptions().get('title') === chartTitle);
  if (!chart) {
    throw new Error(
      `Cannot find chart titled '${chartTitle}' in the current sheet.`
    );
  }

  // Upload chart to Slack as a file
  const options = {
    method: 'post',
    headers: {
      Authorization: `Bearer ${slackBotToken}`,
    },
    payload: {
      title: chartTitle,
      filetype: 'png',
      file: chart.getAs('image/png'),
      channels: slackChannels,
    },
    muteHttpExceptions: true,
  };
  const response = UrlFetchApp.fetch(
    'https://slack.com/api/files.upload',
    options
  );
  if (response.getResponseCode() !== 200) {
    throw new Error(
      `Error uploading Google Sheets image to Slack: HTTP ${response.getResponseCode()}: ${response.getContentText()}`
    );
  }
  const body = JSON.parse(response.getContentText());
  if (!body.ok) {
    throw new Error(
      `Error uploading Google Sheets image to Slack: ${body.error}`
    );
  }
}

Next, modify lines 2, 4, and 6:

• Line 2: replace the Sales string with the title of the chart you want to export from your Google Spreadsheet.
• Line 4: replace the Slack Bot Token (the string starting with xoxb-) with the Slack Bot Token you took note of when creating your Slack App.
• Line 6: replace the Slack channel name with the name of the channel you invited your Slack App to.

Save the script by clicking the disk symbol. Then, manually test your integration by clicking the Run button above the script panel. During the first run of the script, you will be prompted to authorize access of the script to your spreadsheet.

If everything was set up correctly, you should see your Google Sheet chart showing up in the Slack channel you selected:

Congratulations, you can now send your Google Spreadsheet chart to Slack! In the next step, we will automate it so that it will happen on a schedule without any manual involvement.

Send GSheet Charts to Slack Daily

If you have closed the Apps Script panel already, open it again by going to Extensions | Apps Script in your Google Spreadsheet. Then on the left, select the Triggers section:

Click the Add Trigger button. In the dialog that will show up, change the Select event source drop down to Time-driven, and then configure how frequently you want your Google Sheets chart to be sent to Slack using the dropdowns that follow. In the example below, the chart will be sent to Slack every day between midnight and 1 am GMT:

Click Save, and you are all set. Your chart report will be sent to Slack following the schedule you defined in the Apps Script trigger. Congratulations!

Getting Data Into Your Google Spreadsheet

Now that you can create a beautiful chart in a Google Spreadsheet and automatically send it to Slack, the question is where does the data underlying that chart come from? Google Sheet rarely is the source of truth and you need to import the latest data from an external data source like Salesforce or QuickBooks.

Before your go..

To stay updated with our latest content, please subscribe to our email updates or follow us on Twitter at @runmedev! Also, check out Runme (interactive runbooks for VS Code), and Runme Cloud.

Let us know what you think. Bye for now! 👋