Updated on April 26
From this X and this blog, it seems that in the current stage, this situation can be resolved using Sheets API. The sample script of Google Apps Script is as follows. Before you test this, please enable Sheets API at Advanced Google services.
function myFunction() {
// Please set your Spreadsheet ID.
const spreadsheetId = "###";
const requests = [{
updateSpreadsheetProperties: {
properties: { importFunctionsExternalUrlAccessAllowed: true },
fields: "importFunctionsExternalUrlAccessAllowed"
}
}];
Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
}
As additional information, in this case, when importFunctionsExternalUrlAccessAllowed: true is modified to importFunctionsExternalUrlAccessAllowed: false, an error like Cannot set import functions external URL access allowed from true to false. occurs. It seems that when access is allowed once, this cannot be canceled.
Abstract
This is a sample script for enabling “Allow Access” for IMPORTHTML, IMPORTDATA, IMPORTFEED, IMPORTXML, IMAGE and IMPORTRANGE functions in Google Apps Script.
Description
Using the IMPORTRANGE function in Google Sheets requires granting permission to access data within the spreadsheet. As you know, this has always been the case. However, I recently noticed that permission requirements have been expanded to include IMPORTHTML, IMPORTDATA, IMPORTFEED, IMPORTXML, and IMAGE. Source
Currently, entering a formula like IMPORTXML into a cell triggers a warning message as shown in the top image:
Warning: Some formulas are trying to send and receive data from external parties.
Clicking “Allow access” resolves the issue and enables IMPORTXML to function properly. However, this manual confirmation is required for each instance.
Imagine a scenario where you need to repeatedly copy a Google Sheet containing IMPORTXML formulas. Manually clicking “Allow access” for each copy, followed by updating the values, can be time-consuming. Automating this process through a script could significantly reduce manual effort.
This report proposes a sample script to achieve this automation.
Endpoint for permitting Allow access button
In order to retrieve the endpoint for permitting the “Allow access” button, I used DevTool of Chrome. The workflow is as follows.
- Open Google Spreadsheet including IMPORTXML with Chrome.
- Open DevTool and open the network tab.
- Click the “Allow access” button.
By this flow, when the “Allow access” button is clicked, the following endpoint can be retrieved.
POST https://docs.google.com/spreadsheets/u/0/d/{spreadsheetId}/externaldata/allowexternalurlaccess?includes_info_params=true&usp=drive_web&cros_files=false&token={token}
In this case, I guess that the access token can be used as token. From this endpoint, the sample curl command is as follows.
curl -X POST -H "Authorization: Bearer ###accessToken###" https://docs.google.com/spreadsheets/u/0/d/###spreadsheetId###/externaldata/allowexternalurlaccess?includes_info_params=true&usp=drive_web&cros_files=false
In my test, I confirmed that the access token with only the scope of https://www.googleapis.com/auth/spreadsheets could be used. When this curl command is run, the “Allow access” button can be clicked.
Also, when I tested other functions of IMPORTHTML, IMPORTDATA, IMPORTFEED, IMAGE, and IMPORTRANGE except for IMPORTXML, I obtained the following result.
-
IMPORTHTML, IMPORTDATA, IMPORTFEED, IMPORTXML, and IMAGE use the endpoint of
https://docs.google.com/spreadsheets/u/0/d/{spreadsheetId}/externaldata/allowexternalurlaccess?includes_info_params=true&usp=drive_web&cros_files=false -
IMPORTRANGE uses the endpoint of
https://docs.google.com/spreadsheets/d/{spreadsheetId}/externaldata/addimportrangepermissions?donorDocId={sourceSpreadsheetId}&includes_info_params=true&cros_files=false
In my test, even when the query parameters of includes_info_params=true&usp=drive_web&cros_files=false and includes_info_params=true&cros_files=false are not used, the permission could be done. So, in the following sample script, these query parameters are not used. But, if an error occurs, please add them and test it again.
Usage
1. Create a new Google Spreadsheet
Please create a new Google Spreadsheet. And, please put the following sample formula to the cell “A1”.
=IMPORTXML("https://tanaikech.github.io/index.xml","//channel/title")
This is my blog. When you manually click the “Allow access” button, tanaike is shown in the cell. In this sample, this process is run with Google Apps Script.
Please open the script editor of this Google Spreadsheet.
2. Sample script
The sample script for testing this is as follows. Please copy and paste the following script to the script editor of Google Spreadsheet.
When this script is run, the active Spreadsheet is copied to the root folder. And, the “Allow access” button for using IMPORTXML is clicked by this script. By this, when you open the copied Spreadsheet, you can see the value of tanaike retrieved by =IMPORTXML("https://tanaikech.github.io/index.xml","//channel/title").
function sample1() {
// 1. Copy Spreadsheet.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const copiedSS = spreadsheet.copy("copied Spreadsheet");
// 2. Permitting "Arrow Access" button.
const url = `https://docs.google.com/spreadsheets/u/0/d/${copiedSS.getId()}/externaldata/allowexternalurlaccess`;
const params = {
method: "post",
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
};
UrlFetchApp.fetch(url, params);
}
- By this script, the functions of IMPORTHTML, IMPORTDATA, IMPORTFEED, IMPORTXML, and IMAGE work.
3. Additional information
As additional information, the sample script for using all functions of IMPORTHTML, IMPORTDATA, IMPORTFEED, IMPORTXML, IMAGE, and IMPORTRANGE is as follows. This sample script can be also used for permitting not only IMPORTHTML, IMPORTDATA, IMPORTFEED, IMPORTXML but also IMPORTRANGE. If your Google Spreadsheet includes the formulas of IMPORTRANGE, please set the source Spreadsheet IDs to sourceSpreadsheetIds. By this, when this script is run, all functions of IMPORTHTML, IMPORTDATA, IMPORTFEED, IMPORTXML, IMAGE, and IMPORTRANGE have already been able to be used on the copied Spreadsheet.
function sample2() {
// 1. Copy Spreadsheet.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const copiedSS = spreadsheet.copy("Copied Spreadsheet");
// 2. Permitting "Allow Access" button.
const copiedSSId = copiedSS.getId();
const params = {
method: "post",
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
};
const check_ = (t) =>
spreadsheet
.createTextFinder(t)
.useRegularExpression(true)
.matchCase(false)
.matchFormulaText(true)
.findNext();
const requests = [];
if (check_("IMPORTHTML|IMPORTDATA|IMPORTFEED|IMPORTXML")) {
const url = `https://docs.google.com/spreadsheets/u/0/d/${copiedSSId}/externaldata/allowexternalurlaccess`;
requests.push({ url, ...params });
}
if (check_("IMPORTRANGE")) {
const sourceSpreadsheetIds = ["###"]; // Please set your source Spreadsheet IDs you want to use with IMPORTRANGE.
requests.push(
...sourceSpreadsheetIds.map((id) => ({
url: `https://docs.google.com/spreadsheets/d/${copiedSSId}/externaldata/addimportrangepermissions?donorDocId=${id}`,
...params,
}))
);
}
if (requests.length == 0) return;
UrlFetchApp.fetchAll(requests);
}