with IMPORTRANGE SpreadSheet A, B, C, etc. Requests access to extract data from SpreadSheet 1 (Figure 1). Example:
=IMPORTRANGE("SpreadSheet 1"; "Sheet!A1")
I want to remove access (revoke permissions) from Spreadsheet A to extract data from Spreadsheet 1 (Picture 2) Revoke access allowed to Picture 1 to revoke Grant SpreadsheetA permission to extract data from Spreadsheet1
This is just a simplified example because actually I have over 200 spreadsheets connected to 1 (actually 2) database spreadsheets, that's why I want to revoke permissions, how many spreadsheets have access I've hit this limit multiple times on a spreadsheet
Picture of a real spreadsheet to help understand part of my code, the script will run on the same spreadsheet that requested access
I could be wrong but I have an old script that I think worked at one time
function removePermission()
{
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ss_c = ss.getSheetByName('Config');
var currentSheetId = ss.getId();
var targetId = "";
if (ss_c.getRange("B6").getValue() == 1)
{
targetId = ss_c.getRange("D6").getValue();
}
else if (ss_c.getRange("B6").getValue() == 2)
{
targetId = ss_c.getRange("D7").getValue();
}
var currentFile = DriveApp.getFileById(currentSheetId);
var targetFile = DriveApp.getFileById(targetId);
var targetPermissions = targetFile.getPermissions();
for (var i = 0; i < targetPermissions.length; i++)
{
var permission = targetPermissions[i];
if (permission.getType() == "user" && permission.hasAccess())
{
currentFile.removeEditor(permission.getEmail()); // or the next one
// targetFile.removeEditor(permission.getEmail());
}
}
}
But now it shows errorTypeError: targetFile.getPermissions is not a function
So I tried to modify it with what I searched for
function test()
{
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ss_c = ss.getSheetByName('Config');
var currentSheetId = ss.getId();
var targetId = "";
if (ss_c.getRange("B6").getValue() == 1)
{
targetId = ss_c.getRange("D6").getValue();
}
else if (ss_c.getRange("B6").getValue() == 2)
{
targetId = ss_c.getRange("D7").getValue();
}
var currentFile = DriveApp.getFileById(currentSheetId);
var targetFile = DriveApp.getFileById(targetId);
var targetEditors = targetFile.getEditors();
var currentEditors = currentFile.getEditors();
var activeUserEmail = Session.getActiveUser().getEmail();
for (var i = 0; i < targetEditors.length; i++) {
var editor = targetEditors[i];
var editorEmail = editor.getEmail();
// Check if the editor exists in the current file before removing
if (editorEmail !== activeUserEmail && currentEditors.some(e => e.getEmail() === editorEmail)) {
targetFile.removeEditor(editorEmail);
}
}
}
Passed, but doesn't work
I'm not familiar with exactly how the editor works in this situation, and searching I can't find related questions and solutions
I am the owner and only user, not sure but it is related to the file not the user, appreciate the help, thank you
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
I believe your goals are as follows.
IMPORTRANGEfunction.IMPORTRANGE.Problems and Solutions:
Check out the method to directly cancel the
IMPORTRANGEauthorization of Spreadsheet. Unfortunately, I can't find the method. However, we already know that when using endpoints, the authorization process can be run by a script. Ref I think this might work as a workaround in your case. The flow for this workaround is as follows.IMPORTRANGE, except for the spreadsheet you want to revoke.Follow this process to get a Google spreadsheet by revoking the specific spreadsheet you want.
However, in this workaround, the spreadsheet ID is different from the original ID because the original spreadsheet has been copied. So, I'm not sure if this will be useful in your actual situation. So, I'm just proposing this workaround.
When the above process is reflected in the sample script, it becomes as follows.
Sample script:
Please copy and paste the following script into the script editor of the original spreadsheet and set
spreadsheetIdsOfdeletePermissionand save the script.function myFunction() { // Please set the Spreadsheet IDs you want to revoke the authorization of "IMPORTRANGE". const spreadsheetIdsOfdeletePermission = ["###spreadsheetId1###", "###spreadsheetId2###",,,]; // Retrieve original Spreadsheet. const ss = SpreadsheetApp.getActiveSpreadsheet(); const ssId = ss.getId(); const orgFile = DriveApp.getFileById(ssId); const parent = orgFile.getParents().next(); // Copy original Spreadsheet. const tempFile = orgFile.makeCopy(ss.getName() + "_new", parent); const tempFileId = tempFile.getId(); // Authorize "IMPORTRANGE" except for "spreadsheetIdsOfdeletePermission" const tempSS = SpreadsheetApp.open(tempFile); const token = ScriptApp.getOAuthToken(); const reqs = tempSS.createTextFinder("=IMPORTRANGE").matchFormulaText(true).findAll().reduce((ar, r) => { const id = r.getFormula().match(/^\=IMPORTRANGE\("(.*?)"/)[1].split("/")[5]; if (!spreadsheetIdsOfdeletePermission.includes(id)) { const url = `https://docs.google.com/spreadsheets/d/${tempFileId}/externaldata/addimportrangepermissions?donorDocId=${id}`; ar.push({ url, method: "post", headers: { Authorization: `Bearer ${token}` }, muteHttpExceptions: true }); } return ar; }, []); if (reqs.length == 0) return; const res = UrlFetchApp.fetchAll(reqs); res.forEach(r => { if (r.getResponseCode() != 200) { console.log(r.getContentText()); } }); }Notice:
UrlFetchApp.fetchUtilities.sleep .instead ofUrlFetchApp.fetchAllreferences: