使用Google Apps Script自动推送Google表格表格图表至Slack失败的技术咨询

Problem Description #

I'm trying to automate daily pushes of a Table Chart from Google Sheets to a Slack channel using Google Apps Script. While regular charts (bar, line) work perfectly, sending a Table Chart throws an error:

Exception: Service Spreadsheets failed while accessing document with id xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx. myFunction @ DailyChart02.gs:7

Here's my current code:

function myFunction(){ 
  var sheetUrl2 = "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxx/edit#gid=xxxxx" 
  var ss2 = SpreadsheetApp.openByUrl(sheetUrl2); 
  var sheet2 = ss2.getSheetByName("MyDailySheet"); 
  var charts2 = sheet2.getCharts(); 
  var chartImage2 = charts2[0].getBlob().getAs('image/jpeg').setName("graph.png"); 
  sendSlack(chartImage2); 
} 
function sendSlack(chart){ 
  var url = 'https://slack.com/api/files.upload'; 
  var token = 'xoxp-xxxxxxxxxxxxxxxxxxxxxxxxxx'; 
  var channel = '#general'; 
  var payload = { 
    'token' : token, 
    'channels' : channel, 
    'file' : chart, 
    'filename' : 'DailyUsers - Last 30 Days' 
  }; 
  var params = { 
    'method' : 'post', 
    'payload' : payload 
  }; 
  var response = UrlFetchApp.fetch(url, params); 
}

My Slack app has the necessary permissions:

• Bot: chat:write, chat:write.public, files:write
• User: chat:write, files:write

The Table Chart displays correctly in Google Sheets itself.

Solution #

The root issue is that Table Charts are not bitmap-based like bar/line charts—they're interactive components tied directly to spreadsheet cells. The getBlob() method doesn't support converting Table Charts to image blobs directly. Instead, you can capture the underlying cell range as an image, which will replicate the Table Chart's appearance.

Here are two robust fixes:

Fix 1: Directly Capture the Table's Cell Range (Simplest)

Replace the chart-based blob fetch with a snapshot of the cell range that powers your Table Chart:

function myFunction(){ 
  var sheetUrl2 = "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxx/edit#gid=xxxxx" 
  var ss2 = SpreadsheetApp.openByUrl(sheetUrl2); 
  var sheet2 = ss2.getSheetByName("MyDailySheet"); 
  
  // Replace with your actual table range (e.g., A1:E10)
  var tableRange = sheet2.getRange("A1:E10"); 
  var chartImage2 = tableRange.getAs('image/png').setName("daily-table.png"); 
  
  sendSlack(chartImage2); 
}

Fix 2: Auto-Detect Chart Type (Flexible for Mixed Charts)

If your sheet has both regular charts and Table Charts, add logic to handle both cases:

function myFunction(){ 
  var sheetUrl2 = "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxx/edit#gid=xxxxx" 
  var ss2 = SpreadsheetApp.openByUrl(sheetUrl2); 
  var sheet2 = ss2.getSheetByName("MyDailySheet"); 
  var charts2 = sheet2.getCharts(); 
  var chartImage2;
  
  // Check if the first chart is a Table Chart
  if (charts2[0].getType() === SpreadsheetApp.ChartType.TABLE) {
    // Fetch the exact data range used by the Table Chart
    var tableDataRange = charts2[0].getRange();
    chartImage2 = tableDataRange.getAs('image/png').setName("daily-table.png");
  } else {
    // Handle regular charts as before
    chartImage2 = charts2[0].getBlob().getAs('image/jpeg').setName("daily-graph.png"); 
  }
  
  sendSlack(chartImage2); 
}

Why This Works #

• Table Charts don't generate a static image blob internally, so getBlob() fails. Capturing the cell range directly creates a pixel-perfect image of your table, including all formatting from the sheet.
• Using charts2[0].getRange() automatically pulls the exact data range linked to your Table Chart, so you don't have to hardcode the range manually.

内容的提问来源于stack exchange，提问作者oki_oki