Programmatic Way to Transfer Google Sheet CSV to Dropbox

Question

I am trying to put a Google Sheet on a trigger every hour or so to export the contents of a sheet and upload them to Dropbox using Google Apps Script. The below code is successfully getting all of the sheets content and creating a new file in Dropbox, however that file is always empty. I think it has to do with how I'm sending the data, right now I think it is just sending the csv data and not the actual file. I have been searching around trying to figure out how to turn this into a file before sending it to Dropbox but I am stuck :/. Do I need to convert this csv content into a file in Google Drive first or is there a way to do it in real time?

function uploadToDropbox() {
  try{
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var sheet = ss.getSheetByName('sheet5');
    var csvFile = convertRangeToCsvFile('testfile.csv', sheet)

    var url = "https://content.dropboxapi.com";
    var options = {
      "hostname": url,
      "method": "POST",
      "encoding": "utf8",
      "followRedirect": true,
      "headers": {"Authorization": "Bearer XXXXXXXX", 
                  "Content-Type": "application/octet-stream",
                  "Dropbox-API-Arg": '{"path": "/testfile.csv","mode": "overwrite","autorename": false,"mute": false,"strict_conflict": false}'
                 },
      'muteHttpExceptions': true,
      'body': csvFile
    };
    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());
  } catch(e) {
    Logger.log(e)
  }
}

function convertRangeToCsvFile(csvFileName, sheet) {
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    var csvFile = undefined;
    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(",") != -1) {
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }
        if (row < data.length-1) {
          csv += data[row].join(",") + "\r\n";
        }
        else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);
  }
}

Answer 1

Try this:

function uploadToDropbox() {
  var ss=SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet=ss.getSheetByName('sheet5');
  var csvFile=DriveApp.getFileById(convertRangeToCsvFile('testfile.csv', sheet));//this is the file now do whatever you want with it. It contains the csv
  var url="https://content.dropboxapi.com";
  var options={
    "hostname": url,
    "method": "POST",
    "encoding": "utf8",
    "followRedirect": true,
    "headers": {"Authorization": "Bearer XXXXXXXX", 
                "Content-Type": "application/octet-stream",
                "Dropbox-API-Arg": '{"path": "/testfile.csv","mode": "overwrite","autorename": false,"mute": false,"strict_conflict": false}'
               },
    'muteHttpExceptions': true,
    'body': csvFile
  };
  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 convertRangeToCsvFile(csvFileName, sheet) {
  var activeRange=sheet.getDataRange();
  var data=activeRange.getValues();
  if (data.length>1) {
    var csv="";
    for(var row=0;row<data.length;row++) {
      for (var col=0;col<data[row].length; col++) {
        if (data[row][col].toString().indexOf(",") != -1) {
          data[row][col]="\"" + data[row][col] + "\"";
        }
      }
      if (row>data.length-1){csv += data[row].join(",") + "\r\n";
                            }else {csv += data[row];}
    }
    var file=DriveApp.getFilesByName('testFiles.csv').next();//assuming its the only one
    file.setContent(csv);
  }
  return file.getId();
}