Google Apps脚本中的createFile()无法正常运行 [英] createFile() in google Apps Script is not functioning properly

问题描述

我正在尝试创建一个文件.当我从应用程序脚本中的调试器运行以下代码段时，它工作正常.但是，当我从电子表格实时运行它时，它说我没有调用createfile的权限.记录的所有内容都是相同的.问题不在于我没有权限，因为我是电子表格中的唯一人员并且是所有者. CSV的目的是将其从我的Google驱动器移至BigQuery数据

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

推荐答案

您在对我的回答的评论中提到您正在使用onEdit触发脚本.由于这是一个简单触发器，因此您当前的方法将行不通.当您使用简单的触发器运行Apps脚本时，它将在权限降低的沙箱中运行.

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.

请参阅: https://developers.google.com/apps-script/guides/triggers/#restrictions

我能推荐的最好方法是创建一个带有UI弹出窗口的自定义菜单选项，询问要导出的行号.如果该代码是由用户从菜单触发的，则该代码将在具有访问该用户帐户的完全权限的情况下运行.

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.

根据您的用例，计划的触发器也可能起作用.它可以每10分钟或每小时运行一次，并将所有更改导出到电子表格.在这种情况下，Apps脚本将以您的身份运行，并且有权访问您的帐户，并且将在驱动器上创建结果CSV.

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.

有关如何创建自定义菜单的详细信息: https://开发人员. google.com/apps-script/guides/triggers/#onopen

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

有关如何为用户创建表单的详细信息: https://developers .google.com/apps-script/guides/ui-service

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

有关时间驱动触发器的详细信息: https://开发人员. google.com/apps-script/guides/triggers/installable#time-driven_triggers

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

这篇关于Google Apps脚本中的createFile()无法正常运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！