Saving Chart as Image from Google Sheets and send to Dropbox - Saved Image seems broken - 0 bytes

Question

I hope you are well.

I have the following code in apps script:

function uploadToDropbox(chartImage) {
    var url="https://content.dropboxapi.com";
    var options={
      "hostname": url,
      "method": "POST",
      "encoding": "utf8",
      "followRedirect": true,
      "headers": {"Authorization": "Bearer DropboxAppTokenHere", 
                  "Content-Type": "application/octet-stream",
                  "Dropbox-API-Arg": '{"path": "/chart.png","mode": "overwrite","autorename": false,"mute": false,"strict_conflict": false}'
                 },
      'muteHttpExceptions': true,
      'body': chartImage
    };
    var response=UrlFetchApp.fetch(url + "/2/files/upload", options);
    var responseCode=response.getResponseCode();
    if(responseCode != 200) {throw 'Error: ' + responseCode + " - " + response}
    var jsonResponse=JSON.parse(response.getContentText());
  }


function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[1];

  var chartSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Score');
  var charts = chartSheet.getCharts();
  var chart = chartSheet.getCharts()[0];
  var chartImage = chart.getAs("image/png");

  //Logger.log(sheet.getSheetName());
  if(sheet.getSheetName()=='Results Sample') {
  uploadToDropbox(chartImage);
  }
}

So, everything works good when I run the onEdit() function from the apps script editor and file is being sent to my desired DropBox folder. However, when I go and open the file on the folder I see the following:

Any ideas guys? Any parts that I am missing here? It seems that the connection between Google Sheets and DropBox works but I am doing something wrong on the saving of the file as an image/png.

Thanks in advance,

George!

Answer 1

In your script, how about the following modification?

From:

var options={
  "hostname": url,
  "method": "POST",
  "encoding": "utf8",
  "followRedirect": true,
  "headers": {"Authorization": "Bearer DropboxAppTokenHere", 
              "Content-Type": "application/octet-stream",
              "Dropbox-API-Arg": '{"path": "/chart.png","mode": "overwrite","autorename": false,"mute": false,"strict_conflict": false}'
             },
  'muteHttpExceptions': true,
  'body': chartImage
};

To:

var options = {
  "method": "POST",
  "headers": {
    "Authorization": "Bearer DropboxAppTokenHere",
    "Content-Type": "application/octet-stream",
    "Dropbox-API-Arg": '{"path": "/chart.png","mode": "overwrite","autorename": false,"mute": false,"strict_conflict": false}'
  },
  'muteHttpExceptions': true,
  'payload': chartImage
};

Note:

• About So, everything works good when I run the onEdit() function from the apps script editor and file is being sent to my desired DropBox folder. However, when I go and open the file on the folder I see the following:, onEdit function is automatically run when the cell is edited. And, in the current stage, UrlFetchApp.fetch cannot be run by the simple trigger of OnEdit trigger. So, when the cell is edited, an error occurs by executing onEdit function. Please be careful about this.

• As the additional information, in your script, chartImage is Blob. When I tested uploading a binary file using Google Apps Script, in this API, it seems that both 'payload': Blob and 'payload': ByteArray can be used.

• And, as the additional information, in this case, var chartImage = chart; can be also used. Namely, it seems that 'payload': chart can be used. If this is used in your showing script, the modified script is as follows.

function uploadToDropbox(chart) {
  var url = "https://content.dropboxapi.com";
  var options = {
    "method": "POST",
    "headers": {
      "Authorization": "Bearer DropboxAppTokenHere",
      "Content-Type": "application/octet-stream",
      "Dropbox-API-Arg": '{"path": "/chart.png","mode": "overwrite","autorename": false,"mute": false,"strict_conflict": false}'
    },
    'muteHttpExceptions': true,
    'payload': chart,
  };
  var response = UrlFetchApp.fetch(url + "/2/files/upload", options);
  var responseCode = response.getResponseCode();
  if (responseCode != 200) { throw 'Error: ' + responseCode + " - " + response }
  var jsonResponse = JSON.parse(response.getContentText());
}

function sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[1];
  var chartSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Score');
  var chart = chartSheet.getCharts()[0];
  if (sheet.getSheetName() == 'Results Sample') {
    uploadToDropbox(chart);
  }
}

• As one more additional information, in this case, when a binary file is uploaded with payload of UrlFetchApp, it was found that both Int8Array and Uint8Array can be used.

• When binary data is sent using payload of UrlFetchApp, it seems that the following situations can be used.

  Number	Methods	Result
  1	payload: DriveApp.getFileById("### fileId ###")	success
  2	payload: DriveApp.getFileById("### fileId ###").getBlob()	success
  3	payload: DriveApp.getFileById("### fileId ###").getBlob().getBytes()	success
  4	payload: Int8Array.from(DriveApp.getFileById("### fileId ###").getBlob().getBytes())	success
  5	payload: Uint8Array.from(DriveApp.getFileById("### fileId ###").getBlob().getBytes())	success
  6	payload: DriveApp.getFileById("### fileId ###").getBlob().getDataAsString()	failure

  • About number 1, in this case, payload: SpreadsheetApp.getActiveSheet().getCharts()[0] instead of payload: DriveApp.getFileById("### fileId ###") can be also used.
  • About number 4, each number of DriveApp.getFileById("### fileId ###").getBlob().getBytes() and Int8Array.from(DriveApp.getFileById("### fileId ###").getBlob().getBytes()) are the same.
  • When I tested the numbers 4 and 5 to UrlFetchApp before, an error occurs. But, it was found that when I tested them again now, no error occurs.

Reference:

• fetch(url, params)