A lighthearted, casual post.

Summary by AI

This article explains how to visualize data using Google Sheets and Apps Script and post charts to Slack. It also covers how to use ChatGPT to help write the programs and understand API specifications, including writing SQL with ChatGPT.

Background

I wrote a post about this back in 2016 — seven years ago now. At the time it was quite novel, and I remember the BI team at Mercari (where I was working) finding it very useful. The "bear-like man" who reached out to talk more about it became my podcast co-host; the "visibly earnest and serious person" also became a 10X employee. Seven years is something else.

image

Now it's 2023, and I assumed that "quickly visualizing data and pushing it nicely to Slack" was basically a solved problem — there are endless BI tools, surely nobody needs a Google Sheets hack for this anymore.

And for a while, that was true. After 10X launched (around 2017–2020), we used Redash, which handled query creation, visualization, and scheduled Slack posting all in one place. No problems at all. Back when Redash had a hosted web version.

(Redash's hosted service shut down in 2021, making self-hosting mandatory — which broke that assumption.)

After that, 10X cycled through Metabase, Looker, and eventually settled on Tableau. Between Slack and Tableau, we're deep in the Salesforce ecosystem. Please acquire Notion and integrate it (silent wish).

None of these tools have quite the "down-to-earth" feel Redash had. They're all excellent for handling large amounts of data accurately across multiple users with diverse visualizations — all good things. But "readability at a glance" and "ease of sharing in conversation" tend to be weaker. I haven't found a tool that really obsesses over the clarity of a single chart.

Inside 10X, data engineering has matured significantly — data warehouses have layers, access controls and data security are well-managed, and analytics governance has progressed so analysts handle most analysis requests. Great for storing and operating data assets properly.

But there's always the tension of "analysis resources are perpetually maxed out" and "business decisions need 10% precision quickly, not 100% precision slowly." Stailer is in a consumer-focused phase, so we want to be rapidly checking the results of daily actions and experiments. I'm constantly curious about something, generating data questions every few minutes. Online grocery really does have an exceptional number of data points to explore.

That's when I noticed a gap: a simple way to get everyone on the same page with clear charts was missing. So I decided to update my 2016 practice and put it to use again. Long preamble, I know.

Apps Script Program

Slack's API has User Tokens and Bot Tokens. As of 2023, posting images to channels still requires a User Token — so I'm using a User Token with chat:write permission.

Updates from the 2016 article:

  • Multiple spreadsheets can be operated simultaneously
  • Multiple sheets within a Google Spreadsheet can be specified
  • Multiple charts within sheets can all be fetched and posted together
  • No need to specify which individual charts to retrieve
  • Each array variable can target a different Slack channel
  • Much cleaner code

Here's the program. Set spreadsheetId, sheetNames, token, and channelId in the spreadsheetsInfo array, deploy it, and it should run immediately. I use a daily trigger pointed at postChartsToSlack.

function postChartsToSlack() {
  var token = "xoxp-"; // Slack API token

  // Array of objects containing spreadsheet ID, sheet names, and Slack channel ID
  var spreadsheetsInfo = [
    {
      spreadsheetId: "your-id",
      sheetNames: ["Sheet1","Sheet2"],
      channelId: "channel-id"
    }
    ,{
      spreadsheetId: "your-id",
      sheetNames: ["Sheet1","Sheet2"],
      channelId: "channel-id"
    }
    ,{
      spreadsheetId: "your-id",
      sheetNames: ["Sheet1","Sheet2"],
      channelId: "channel-id"
    }
    ,{
      spreadsheetId: "your-id",
      sheetNames: ["Sheet1","Sheet2"],
      channelId: "channel-id"
    }
  ];

  for (var k = 0; k < spreadsheetsInfo.length; k++) {
    var spreadsheetId = spreadsheetsInfo[k].spreadsheetId;
    var sheetNames = spreadsheetsInfo[k].sheetNames;
    var channelId = spreadsheetsInfo[k].channelId;

    try {
      var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
      var spreadsheetUrl = spreadsheet.getUrl();

      for (var i = 0; i < sheetNames.length; i++) {
        var sheetName = sheetNames[i];
        var sheet = spreadsheet.getSheetByName(sheetName);
        var charts = sheet.getCharts();

        for (var j = 0; j < charts.length; j++) {
          try {
            var chart = charts[j];
            var imageData = chart.getBlob();
            var fileName = chart.getOptions().get('title') + ".png";

            var payload = {
              "token": token,
              "channels": channelId,
              "initial_comment": fileName.replace('.png','') + ": " + "<" + spreadsheetUrl + "|" + "URL" + ">",
              "filename": fileName,
              "filetype": "png",
              "file": imageData
            };

            var options = {
              "method": "post",
              "payload": payload
            };

            var response = UrlFetchApp.fetch("https://slack.com/api/files.upload", options);
            Logger.log(response.getContentText());
          } catch(err) {
            Logger.log('Error uploading file: ' + err.message);
          }
        }
      }
    } catch(err) {
      Logger.log('Error opening spreadsheet: ' + err.message);
    }
  }
}

Since I'm using a User Token, I'm personally acting as a bot.

image

A slight downside: when threads or reactions happen on those chart posts, notifications come back to me. But that's kind of fine — it means conversations are starting around the data.

Using ChatGPT to Write the Code

Recently I've been using Python to call the Notion API and auto-build relations between tables, writing Apps Script for SaaS integrations Zapier can't handle, organizing data I need for management, and occasionally writing SQL to visualize things myself.

The problem is that for someone like me who's completely out of practice with actual coding: half a day on environment setup, two days re-learning syntax, then N days debugging. Terrible time-to-value. The result is it becomes a weekend hobby or something I ask a colleague to handle.

Then ChatGPT arrived. My experience was: natural language in → natural language out was not that impressive. But natural language in → code out was extraordinary.

Even this simple script has real pitfalls if you're building it yourself — understanding Slack API specs, knowing which version to use, what methods to call, and basic syntax. ChatGPT handles all of this surprisingly well. You can give it the schema from BigQuery, describe the data you want in plain language, and it'll write pretty accurate SQL. That's prompt engineering in practice.

I had a podcast conversation with Kameike-san, CTO of Timee, about how much of throwaway code like this can already be offloaded to AI. Linking that here too.