CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsslackslack-api
oki_oki
oki_oki

Reputation: 89

Sending table charts from google spreadsheet to slack using apps script

I'm trying to send table charts on a daily basis to my slack channel from my google spreadsheet. The automation works when I am creating some normal charts - e.g. column charts or line charts - but it shows an error if I try the same thing with a table chart. Should I change any of my code to have this running?

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);
}

Error message:

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

This code works perfectly and sends a chart to my slack channel.

My slack app has these token scopes authorized: (bot) chat:write chat:write.public files:write (User) chat:write files:write

My table chart looks something like this. It shows perfectly on my spreadsheet file. enter image description here

Upvotes: 1

Views: 3475

Answers (1)

Tanaike
Tanaike

Reputation: 201378

Issue and workaround:

I have had the same situation with you. In the current stage, it seems that the table chart cannot be directly retrieved as the image blob. This might be the current specification. So as a current workaround, I would like to propose the following flow. In this workaround, Google Slides is used as a wrapper. By the way, in your script, getAs('image/jpeg') is used. But it seems that setName("graph.png") is used. So in this modification, the mimeType uses image/png from the filename.

  1. Retrieve the table chart from Google Spreadsheet.
  2. Create new Google Slides as a temporal file.
  3. Insert the table chart from Google Spreadsheet to Google Slides.
  4. Retrieve the inserted chart on Google Slides as an image blob.
  5. Remove the temporal Google Slides.
  6. Use the blob to the Slack API.

By this flow, the table chart can be retrieved as the image blob. When your script is modified, it becomes as follows.

Modified script:

Please modify myFunction() as follows.

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();
  
  // --- I added below script.
  var s = SlidesApp.create("temporalSlides");
  var chartImage2 = s.getSlides()[0].insertSheetsChartAsImage(charts2[0]).getBlob().setName("graph.png");
  DriveApp.getFileById(s.getId()).setTrashed(true);
  // ---

  sendSlack(chartImage2);
}

Note:

  • In this case, how about reporting it to Google issue tracker as the future request? Ref

Reference:

Upvotes: 2

Related Questions