createFile() in google Apps Script is not functioning properly

Question

I am trying to create a file. It works fine when I run the following code segment from the debugger in apps script. However, when I run it real time from the spreadsheet, it says I do not have permission to call createfile. Everything that is logged is identical. The issue is not I do not have authority as I am the only one in the spreadsheet and am the owner. The purpose of the CSV is to move it from my google drive into data for BigQuery

function saveAsCSV(row) { //Doc to Csv
//row = 3; //when this is uncommented and ran from the debugger, it works.

 try{

var fileName=  Date.now()
fileName = fileName + ".csv";


var csvFile = convertRangeToCsvFile_(fileName,row);
Logger.log(csvFile); //Both times ran on the spreadsheet and from debug equals the same.

DriveApp.createFile(fileName, csvFile);


SpreadsheetApp.getActiveSpreadsheet().getSheetByName("New and Open").getRange("J" + row.toString()).setValue("");

loadCsv(fileName);

}
catch(e){Logger.log("B" + e.message);} //No permission to create file
}



function convertRangeToCsvFile_(csvFileName, r) {

var ws = SpreadsheetApp.getActiveSpreadsheet();
try {
//var data = ws.getValues();
var csvFile = undefined;

var csv = "";
var row = r;

var datArray = Create2DArray(1,19);


  datArray[0][0] =  ws.getRange("A" + row.toString()).getValue().toString().toUpperCase();
  datArray[0][1] =  ws.getRange("B"+row.toString()).getValue().toString().toUpperCase();
  datArray[0][2] =  ws.getRange("C"+row.toString()).getValue().toString().toUpperCase();
  datArray[0][3] =  ws.getRange("D"+row.toString()).getValue().toString().toUpperCase();
  datArray[0][4] =  ws.getRange("E"+row.toString()).getValue().toString().toUpperCase();
  datArray[0][5] =  ws.getRange("F"+row.toString()).getValue().toString().toUpperCase();
  datArray[0][6] =  ws.getRange("G"+row.toString()).getValue().toString().toUpperCase();
  datArray[0][7] =  ws.getRange("H"+row.toString()).getValue().toString().toUpperCase();
  datArray[0][8] =  ws.getRange("I"+row.toString()).getValue().toString().toUpperCase();
  datArray[0][9] =  new Date(ws.getRange("K"+row.toString()).getValue().toString()).getHours();
  datArray[0][10] =  new Date(ws.getRange("K"+row.toString()).getValue().toString()).getMinutes();
  datArray[0][11] =  new Date(ws.getRange("L"+row.toString()).getValue().toString()).getHours();
  datArray[0][12] =  new Date(ws.getRange("L"+row.toString()).getValue().toString()).getMinutes();
  datArray[0][13] =  new Date(ws.getRange("M"+row.toString()).getValue().toString()).getHours();
  datArray[0][14] =  new Date(ws.getRange("M"+row.toString()).getValue().toString()).getMinutes();
  datArray[0][15] =  new Date(ws.getRange("N"+row.toString()).getValue().toString()).getTime();
  datArray[0][16] =  new Date(ws.getRange("N"+row.toString()).getValue().toString()).getFullYear();
  datArray[0][17] =  new Date(ws.getRange("N"+row.toString()).getValue().toString()).getMonth();
  datArray[0][18] =  new Date(ws.getRange("N"+row.toString()).getValue().toString()).getDate();

for(var i = 0; i < 19; i++){
  if(datArray[0][i] == ""){if(i > 9){datArray[0][i] = 0;} else{datArray[0][i] = "nil";} } 
  if(i < 18){csv += '"' + datArray[0][i] + '"' + ",";}
  else{ csv += '"' + datArray[0][i] + '"'; }
}

Logger.log("A " + csv);
Logger.log(csv + "\n" + datArray[0].join(","));
csvFile = csv;
return csvFile;
}
  catch(err) {
    Logger.log("C" + err);
    Browser.msgBox(err);
   }
}

Cameron Roberts Cameron Roberts · Accepted Answer · 2014-10-31T16:29:39

You mention in your comment on my answer that you are using onEdit to trigger the script. Since this is a Simple Trigger, your current approach will not work. When you use simple triggers to run an Apps Script, it runs in a sandbox with reduced permissions.

See: https://developers.google.com/apps-script/guides/triggers/#restrictions

The best I can recommend is create a custom menu option with a UI popup asking for the row number to export. If the code is triggered from a menu by the user, it runs with full permission to access that users account.

Depending on your use-case, a scheduled trigger might work too. It could run every 10 minutes or every Hour and export any changes to the spreadsheet. In this case the Apps Script runs as you, with permission to access your account, and the resulting CSV would be created on your drive.

Details on how to create a custom menu: https://developers.google.com/apps-script/guides/triggers/#onopen

Details on how to create a form for the user: https://developers.google.com/apps-script/guides/ui-service

Details on time driven triggers: https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers

createFile() in google Apps Script is not functioning properly

1 Answers