getFolderById works fine in apps editor, but won't in related spreadsheet

huangapple go评论73阅读模式
英文:

getFolderById works fine in apps editor, but won't in related spreadsheet

问题

I have a spreadsheet which aims to generate invoices. One sheet per invoice, and some checkboxes to manage the content. One of them is designed to save sheet as pdf file into a dedicated folder of the GDrive of the same account.

Here is my code:

function saveInvoiceIntoGDrivePDF() {
    const currentSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
    const paramSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Paramètres généraux");
    const currentSheet = currentSpreadSheet.getActiveSheet();
    const invoicesStorageFolderOnGDriveId = paramSheet.getRange('B30').getValue();
    DriveApp.getFolderById(invoicesStorageFolderOnGDriveId);
    const authorizationToken = ScriptApp.getOAuthToken();
    SpreadsheetApp.getUi().alert("Got Auth Token!");
    const exportLink = "https://docs.google.com/spreadsheets/d/"+currentSpreadSheet.getId()+"/export?format=pdf&size=a4&portrait=true&scale=4&top_margin=1.00&bottom_margin=1.00&left_margin=0.50&right_margin=0.50&gridlines=false&printnotes=false&pageorder=2&horizontal_alignment=CENTER&vertical_alignment=TOP&printtitle=false&sheetnames=false&fzr=false&fzc=false&attachment=false&r1=0&r2=40&c1=1&c2=6&gid="+currentSheet.getSheetId();
    const invoicingYear = SpreadsheetApp.getActiveSheet().getRange('J5').getValue();
    const invoicingMonth = SpreadsheetApp.getActiveSheet().getRange('N6').getValue();
    const customerName = SpreadsheetApp.getActiveSheet().getRange('J9').getValue();
    const indexFacture = SpreadsheetApp.getActiveSheet().getRange('J7').getValue();
    const invoicePDFFileName = "Invoice_"+invoicingYear+"_"+invoicingMonth+"_"+customerName+"_"+indexFacture+".pdf";
    const exportParams = { method:"GET", headers:{Authorization:"Bearer "+authorizationToken}, muteHttpExceptions:true };
    var invoicePDFContent = UrlFetchApp.fetch(exportLink, exportParams).getBlob().setName(invoicePDFFileName);
    invoicesStorageFolderOnGDrive.createFile(invoicePDFContent);
}

Set authorizations into appsscript.json as following:

{
    "timeZone": "Europe/Paris",
    "dependencies": {
    },
    "exceptionLogging": "STACKDRIVER",
    "runtimeVersion": "V8",
    "oauthScopes": [
        "https://www.googleapis.com/auth/script.external_request",
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/spreadsheets.readonly",
        "https://www.googleapis.com/auth/spreadsheets"
    ]
}

This function, like many others, is triggered by a checkbox in the "management area" of each sheet, with the label "Save as PDF in Drive".

The problem is that this function works like a charm when running it from the Apps scripts editor, but won't gather drive permissions from the appsscript.json.

I understand there are limitations on authorizations, but what I can't figure out is that, as the script projet is (from my understanding) attached to the spreadsheet, why it can run from the editor but not from the spreadsheet it has been designed for? What is the interest to allow to write a script which won't work in "real world"?

And, if this is a strong authorization issue, is there a way to have Google "accept" this script, as it only creates new files in the same account it belongs to?

Any help appreciated,
Many thanks,
Emmanuel

Tried all I could from documentation and forums (but could hardly get "outdated" posts).

The issue is that this spreadsheets is to be run by people whith little computers knowledge, and for the moment, they have to:

  1. Click on the "open pdf in a separate tab"
  2. Download pdf setting the name manually
  3. Upload file from their computer to GDrive

This is (obviously) painful from their point of view, and very little satisfying as this is the very last straight line of my "application".

英文:

I have a spreadsheet which aims to generate invoices. One sheet per invoice, and some checkboxes to manage the content. One of them is designed to save sheet as pdf file into a dedicated folder of the GDrive of the same account.
Here is my code:

function saveInvoiceIntoGDrivePDF() {
const currentSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const paramSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Paramètres généraux");
const currentSheet = currentSpreadSheet.getActiveSheet();
const invoicesStorageFolderOnGDriveId = paramSheet.getRange('B30').getValue();
DriveApp.getFolderById(invoicesStorageFolderOnGDriveId); const authorizationToken = ScriptApp.getOAuthToken();
SpreadsheetApp.getUi().alert("Got Auth Token!");
const exportLink = "https://docs.google.com/spreadsheets/d/"+currentSpreadSheet.getId()+"/export?format=pdf&size=a4&portrait=true&scale=4&top_margin=1.00&bottom_margin=1.00&left_margin=0.50&right_margin=0.50&gridlines=false&printnotes=false&pageorder=2&horizontal_alignment=CENTER&vertical_alignment=TOP&printtitle=false&sheetnames=false&fzr=false&fzc=false&attachment=false&r1=0&r2=40&c1=1&c2=6&gid="+currentSheet.getSheetId();
const invoicingYear = SpreadsheetApp.getActiveSheet().getRange('J5').getValue();
const invoicingMonth = SpreadsheetApp.getActiveSheet().getRange('N6').getValue();
const customerName = SpreadsheetApp.getActiveSheet().getRange('J9').getValue();
const indexFacture = SpreadsheetApp.getActiveSheet().getRange('J7').getValue();
const invoicePDFFileName = "Invoice_"+invoicingYear+"_"+invoicingMonth+"_"+customerName+"_"+indexFacture+".pdf";
const exportParams = { method:"GET", headers:{Authorization:"Bearer "+authorizationToken}, muteHttpExceptions:true };
var invoicePDFContent = UrlFetchApp.fetch(exportLink, exportParams).getBlob().setName(invoicePDFFileName);
invoicesStorageFolderOnGDrive.createFile(invoicePDFContent);
}

Set authorizations into appsscript.json as following:

{
"timeZone": "Europe/Paris",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/script.external_request",
"https://www.googleapis.com/auth/drive",
"https://www.googleapis.com/auth/spreadsheets.readonly",
"https://www.googleapis.com/auth/spreadsheets"
]
}

This function, like many others, is triggered by a checkbox in the "management area" of each sheet, with the label "Save as PDF in Drive".

The problem is that this function works like a charm when running it from the Apps scripts editor, but won't gather drive permissions from the appsscript.json.

I understand there are limitations on authorizations, but what I can't figure out is that, as the script projet is (from my understanding) attached to the spreadsheet, why it can run from the editor but not from the spreadsheet it has been designed for? What is the interest to allow to write a script which won't work in "real world"?

And, if this is a strong authorization issue, is there a way to have Google "accept" this script, as it only creates new files in the same account it belongs to?

Any help appreciated,
Many thanks,
Emmanuel

Tried all I could from documentation and forums (but could hardly get "outdated" posts).

The issue is that this spreadsheets is to be run by people whith little computers knowledge, and for the moment, they have to:

  1. Click on the "open pdf in a separate tab"
  2. Download pdf setting the name manually
  3. Upload file from their computer to GDrive

This is (obiously) painful from their point of view, and very little satisfying as this is the very last straight line of my "application".

答案1

得分: 0

Checkboxes 用于管理内容。其中一个设计用于将表格保存为 PDF 文件。

看起来你正在使用 onEdit(e) 简单触发器 来运行 saveInvoiceIntoGDrivePDF() 函数。

onEdit(e) 在一个上下文中运行,在这个上下文中它无法访问任何个人信息,也不能调用需要身份验证的服务。所有的 DriveApp 方法都需要身份验证,因此它们不能从 onEdit(e) 中调用。

为了使其工作,请使用可安装触发器而不是简单触发器,或者通过其他方式运行 saveInvoiceIntoGDrivePDF() 函数,比如一个按钮,一个自定义菜单项,或者一个侧边栏

英文:

> checkboxes to manage the content. One of them is designed to save sheet as pdf

It sounds like you are using an onEdit(e) simple trigger to run the saveInvoiceIntoGDrivePDF() function.

onEdit(e) runs in a context where it cannot access any personal information nor call services that would require authentication. All DriveApp methods require authentication, so they cannot be called from onEdit(e).

To make it work, use an installable trigger instead of a simple trigger, or run the saveInvoiceIntoGDrivePDF() function through some other means, such as a button, a custom menu item, or a sidebar.

huangapple
  • 本文由 发表于 2023年5月8日 00:38:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76195111.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定