Slack with Google Sheets

Send reports and charts from Google Sheets to Slack

This guide shows you how you can use the Fusebit Add-On For Google Sheets to send messages, including reports with charts, from Google Sheets to Slack.

Send reports and charts from Google Sheets to SlackSend reports and charts from Google Sheets to Slack

Send reports and charts from Google Sheets to Slack

Before you continue, you must complete the setup steps from Getting Started with Google Sheets and have a Google Spreadsheet configured and ready to use with the Fusebit Add-On. Follow this guide in sequence as sections build on one another.

Overview

In this guide, we will create a Node.js function that receives spreadsheet data and charts from your Google Sheets spreadsheet and sends them to Slack.

We will start by adding a Fusebit ConnectorConnector - A connector is the package from Fusebit that manages the relationship between one or more integrations and a specific service. One of the most common types of connector is an OAuth connector, which takes care of the OAuth negotiation between your customers and the service you're integrating, so that you don't have to! for Slack along with SnippetSnippet - A small snippet of code that implements a specific operation on a a target system you want to connect to. A snippet defines one of more JavaScript functions you can call from your core integration logic to cut down on the implementation time.s for sending messages and uploading files to the solution and authorizing access to your Slack team. Then, we will implement a Node.js function that receives spreadsheet data, creates a report in Slack markup, and sends it to Slack as a text message. Lastly, we will enhance the function to also receive an image of a chart from the Google spreadsheet and upload it to Slack as a file next to the text report.

When we are done, you will be able to send a report to Slack consisting of rich text and charts, based on the data and charts in your Google Spreadsheet.

Add Snippets and Authorize Slack

In your Google Spreadsheet, go to Extensions | Fusebit | Open, click Edit, and in the Fusebit Editor that will open in a separate tab, select Snippets. In the Add Snippet dialog that shows up, type slack in the search field, and then choose Slack - Send a message to a channel snippet:

Add "Slack - Send a message to a channel" snippet in the Fusebit EditorAdd "Slack - Send a message to a channel" snippet in the Fusebit Editor

Add "Slack - Send a message to a channel" snippet in the Fusebit Editor

If you scroll down to the bottom of integration.js, you should see a new Node.js function added: slackSendMessage. We will be using it in the next section. You can read more about the Fusebit Connectors and Snippets here.

Back in the Fusebit Editor, click Run and follow the prompts to Authorize access to your Slack team:

Authorize access to SlackAuthorize access to Slack

Authorize access to Slack

You are now set up to start configuring your spreadsheet and writing some Node.js code.

Sending a Text Report to Slack

Set up a sheet in your Google spreadsheet as follows:

Set up spreadsheet for sending reports to Slack fromSet up spreadsheet for sending reports to Slack from

Set up spreadsheet for sending reports to Slack from

Using the Fusebit Add-On, you can send whatever messages you want to Slack. In this guide, we demonstrate this capability by taking hypothetical sales report data from the spreadsheet and sending it as a formatted markup message to Slack. Cells A2:B5 hold the sales data, with row 2 being the header and rows 3-5 specifying the sales volume per region. It does not matter for this example where this data came from so we will enter it directly, but it could as well have been imported using another Fusebit function from, say, Salesforce.

In cell D3 we will add an invocation to the FUSEBIT function provided by the Fusebit Add-On that will send a new report to Slack whenever the sales data in the A3:B5 range changes.

With the spreadsheet set up, let's switch back to the Fusebit Editor tab. (If you closed it, you can always reopen it with Extensions | Fusebit | Open and then Edit). This is where we will implement a Node.js function that will use the Slack - Send a message to a channel snippet you added previously. Locate the declaration of the googleSheetsFunctions object around line 9 in integration.js, and add a new property to it that defines and implements the Node.js function you will be able to call from Google Spreadsheets, and click Save:

sendReport: async (ctx, data, charts) => {
  // format and send text report
  const text = data.map(row => `*${row[0]}*: $${row[1]}M`).join('\n')
  await slackSendMessage(ctx, text);
  // respond with the timestamp the report was sent
  return new Date();
},

The function first formats a report in Slack markup based on the sales data it received from the spreadsheet. It then sends that message to Slack (by default it will send it to your personal channel, but you can change it by specifying a specific channel as the 3rd parameter of the call to slackSendMessage). Lastly, it responds with the current timestamp the message was sent, which will be rendered back in the spreadsheet. You can read more about the programming here.

With the code in place, let's go back to the spreadsheet and call the Node.js function we just implemented by adding =FUSEBIT(A3:B5,"sendReport") in the D3 cell. The function evaluates by invoking the sendReport Node.js function we've added in the Fusebit Editor and passing in the sales data from the A3:B range of the spreadsheet, and then displaying the returned timestamp in cell D3 in the spreadsheet. The function re-evaluates by calling the Node.js function again every time the input parameters change. If you check your Slack, you should see the report has been sent there:

Calling the Node.js function from Goggle Sheets to send a message to SlackCalling the Node.js function from Goggle Sheets to send a message to Slack

Calling the Node.js function from Goggle Sheets to send a message to Slack

Congratulations! You've just implemented a mechanism to send a text report to Slack based on the data in your spreadsheet. Next, we will see how to also upload Google Sheet charts to Slack.

Sending Google Sheets Charts to Slack

Fusebit Add-on For Google Sheets allows you to serialize charts in your spreadsheet to PNG format and include them in the call to your Node.js function. In this section, we will use this functionality to upload charts from your Google spreadsheet as files to Slack.

First, add a simple pie chart to your spreadsheet from the previous section by selecting the A2:B5 range and choosing Insert | Chart. Then, click on the chart title and change to Sales (this is important as the Fusebit Add-On identifies charts by their title). Your spreadsheet will look like similar to this:

Add simple pie chart with sales data. Set "Sales" as title!Add simple pie chart with sales data. Set "Sales" as title!

Add simple pie chart with sales data. Set "Sales" as title!

Next, modify the formula in cell D3 to =FUSEBIT(A3:B5,"sendReport","Sales"). Notice the 3rd parameter in the call to the function: "Sales". It identifies the title of a chart in the current sheet of the spreadsheet that will be serialized as PNG and sent over to the Node.js function along with the tabular data from A3:B5:

Specify the title of the chart to send to Node.js as the 3rd parameter of FUSEBITSpecify the title of the chart to send to Node.js as the 3rd parameter of FUSEBIT

Specify the title of the chart to send to Node.js as the 3rd parameter of FUSEBIT

Next, you will need to add another Slack snippet to your Node.js code. Go back to the Fusebit Editor tab, or if closed already, in your Google Spreadsheet, go to Extensions | Fusebit | Open, click Edit to open it again. In the Fusebit Editor, select Snippets. In the Add Snippet dialog that shows up, type slack in the search field, and then choose Slack - Upload a file to a channel snippet. Click Add. You will notice a new function was added to the bottom of integration.js: slackUploadFile.

Now, go back to the top of intergation.js in the Fusebit Editor where in the previous section you have added your sendReport function, and modify the implementation as follows:

sendReport: async (ctx, data, charts) => {
  // format and send text report
  const text = data.map(row => `*${row[0]}*: $${row[1]}M`).join('\n')
  await slackSendMessage(ctx, text);
  // send chart
  await slackUploadFile(ctx, {
    title: 'Sales',
    file: require('stream').Readable.from(Buffer.from(charts['Sales'], 'base64'))
  });
  // respond with the timestamp the report was sent
  return new Date();
},

Notice the new call to slackUploadFile we've just added. It uploads a file to Slack by taking its content from the charts['Sales'] input parameter, which contains a base64-encoded PNG image of the Sales chart in the spreadsheet. Note that the charts hash only includes those charts you have chosen to make available by specifying them as the 3rd parameter of the call to the FUSEBIT function in your spreadsheet, =FUSEBIT(A3:B5,"sendReport","Sales") in this example.

Click Save in the Fusebit Editor.

Finally, back in the spreadsheet, make a change in the input data (say change the sales numbers for AU) to cause the FUSEBIT function to re-run. The textual report along with the chart from Google Sheets should show up in your Slack channel:

Sending charts from Google Sheets to SlackSending charts from Google Sheets to Slack

Sending charts from Google Sheets to Slack

Congratulations! You now can send both text reports as well as charts from your Google Sheet spreadsheet to your Slack workspace!


Did this page help you?