如何允许通过应用程序脚本访问 importrange 功能? [英] How to allow access for importrange function via apps script?

问题描述

当您在电子表格中手动输入导入范围函数时，您会收到一个弹出窗口，并且必须允许访问".

When you enter an importrange function manually into a spreadsheet you receive a pop up and must 'allow access'.

但是，我正在尝试通过脚本找到一种方法来执行此操作，因为我正在创建许多电子表格，每个电子表格都有一个 query-importrange 函数(我拥有"具有要导入数据的电子表格).对我来说太多了，无法通过弹出窗口手动允许访问"并更新功能以包含查询功能.

However, I'm trying to find a way to do this via a script because I'm creating many spreadsheets, each with a query-importrange function (I 'own' the spreadsheet which has data to import). There's too many for me to manually 'allow access' via the pop up and update the function to include the query function.

因此，我在应用程序脚本中寻找可以执行与弹出窗口相同的操作的函数调用.下面的代码段示例.

Hence, I'm looking for a function call in apps script that can perform the same action that the pop up did. Code segment example below.

有人知道可以允许访问"的功能吗?斯蒂芬

Does anyone know of a function that can 'allow access'? Stefan

// create new spreadsheet file
...
var ss = createSpreadsheet(fileName);
var spreadsheet = SpreadsheetApp.open(ss);
var sheet = spreadsheet.getSheetByName("Sheet1");

// Add student as Viewer
spreadsheet.addViewer(studentEmail);

// Add ImportRange function 
var sheet = spreadsheet.getSheets()[0];
var cell = sheet.getRange("A1");
var filter = "select * where Col3='" + studentEmail + "'";
var qry = '=QUERY(importRange("' + fileKey + '","14-15S2!A1:AE");"' + filter + '";1)';
cell.setValue(qry);
// I need a function to 'allow access' here, so the function can be allowed access. Otherwise, it throws an error.
...

推荐答案

我遇到了与此类似的问题，并发现答案是更改您从中导入数据的电子表格文件的权限(您的文件中的filekey")示例").

I had a problem similar to this and found the answer was to alter the permissions of the spreadhseet file from which you are importing data (the "filekey" in your example").

这是让允许访问"消失的谷歌应用脚​​本:

This is the google app script that made "Allow Access" go away for me:

file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW)

这篇关于如何允许通过应用程序脚本访问 importrange 功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！