remove permission to one spreadsheet to pull data from another spreadsheet with IMPORTRANGE

Question

with IMPORTRANGE SpreadSheets A, B, C, etc Requests acces to pull data from SpreadSheet 1 (pic 1). Exemple:

=IMPORTRANGE("SpreadSheet 1"; "Sheet!A1")

I want to remove acess ( revoke permission) from Spreadsheet A to pull data from Spreadsheet 1 (pic 2) undo that allow acess on the pic 1, to revoke permission to Spreadsheet A to pull data from Spreadsheet 1

It is just an example to simplify, because in reality I have over 200 Spreadsheets connecting to 1 (actually 2) Database Spreadsheets, that is why I want to revoke permissions, there is a limit on how many Spreadsheets can access one Spreadsheet and I have reached that limit many times already

Picture of real Spreadsheet to help understand part of my code, script will run on same Spreadsheet that requests access

i may be wrong but an old script i had, i think used to work

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 displays error TypeError: targetFile.getPermissions is not a function

so i tried to modify with what i searched

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

passes, but doesn't work

im not familiar how editors work exactly in this case and searching i couldn't find related problems and solutions

im the owner and only user, not sure but it is related to files not users, help is appreciated, thanks

I have to apologize for my poor English skill. Unfortunately, I cannot understand `i want to remove access, from sheets that im going to delete`. Can I ask you about the detail of it? First, I would like to correctly understand your question. — Tanaike, Jul 23 '23 at 02:12
ok, lets say Sheets A, B and C connect to pull data from Sheet 1, I want to remove access from sheet A from 1 but keep B and C accessing it — Bruno Carvalho, Jul 23 '23 at 02:56
getId gets sheet i want to delete and have access removed from targetId, i will run that script on sheets i will delete, not the target — Bruno Carvalho, Jul 23 '23 at 03:03
Thank you for repling. I have to apologize for my poor English skill. Unfortunately, I cannot understand your goal from `lets say Sheets A, B and C connect to pull data from Sheet 1, I want to remove access from sheet A from 1 but keep B and C accessing it` and `getId gets sheet i want to delete and have access removed from targetId, i will run that script on sheets i will delete, not the target`. — Tanaike, Jul 23 '23 at 04:34
What dows `lets say Sheets A, B and C connect to pull data from Sheet 1` mean? I cannot understand the differences between Sheets A, B, C, and 1. You want to use 4 sheets A, B, C and 1 in a Google Spreadsheet? Can I ask you about the detail of your goal? First, I would like to correctly understand your question. — Tanaike, Jul 23 '23 at 04:34
let me try to make this as simple as possible Sheet A B and C request to pull data from Sheet 1, like in the picture on my post, all 3 have access allowed, then I want to undo (remove) that permission from Sheet A, if i need sheet A to pull data from Sheet 1 again, I will have to allow access again, repeating what i did in the picture, but permissions for sheet B and C remains — Bruno Carvalho, Jul 23 '23 at 04:48
Thank you for replying. I have to apologize for my poor English skill again. I cannot understand between `access allowed` and each sheet (sheets A, B, C, and 1). What are the sheets A, B, C, and 1? — Tanaike, Jul 23 '23 at 05:04
Although, this is just my guess, for example, your "sheets A, B, C, and 1" mean 4 independent Google Spreadsheets which is not each sheet in a Google Spreadsheet? And, in your situation, the values are retrieved from 3 Google Spreadsheets "A", "B" and "C" to the Google Spreadsheet "1" using `IMPORTRANGE`. And, you want to revoke the access permission for retrieving the values from Google Spreadsheet "A". Is my understanding correct? — Tanaike, Jul 23 '23 at 05:10
im sorry for the confusion, it was totally my fault for not providing correct information and mixing sheets and spreadsheets, i edited post to include pictures and clarify information, but yea, 4 independent spreadsheets, although values are retrieved from Spreadsheet "1" (DATABASE) to Spreadsheets "A", "B" and "C", thanks for your patience and im sorry for my mistake, i hope you can understand it now — Bruno Carvalho, Jul 23 '23 at 13:24
forget about deleting, etc i was trying to explain with more details than needed, lets say, one SS can allow 500 other SS, i dont know, but i know there is a limit, and im not sure how those file permissions work, if you delete one SS that is using one of those spots, it doesnt free (vacancy) that spot, ive tested it, to test if script work it would mean same SS having to ask access again while other SS remain with access, again i dont know how internally permissions work, that is why im over explaining, img 2 with arrows should illustrate what i want to achieve — Bruno Carvalho, Jul 23 '23 at 20:34
Thank you for replying and adding more information. From your reply, I proposed a workaround as an answer. Please confirmit. If that was not useful and I misunderstood your question, I apologize. — Tanaike, Jul 23 '23 at 23:54

score 2 · Answer 1 · answered Jul 23 '23 at 23:54

I believe your goal is as follows.

You have a Google Spreadsheet including the authorized IMPORTRANGE functions.
You want to revoke the several authorizations for IMPORTRANGE from Spreadsheet.

Issue and workaround:

I checked the method for directly revoking the authorization of IMPORTRANGE from Spreadsheet. Unfortunately, I couldn't find the method. But, we have already known when an endpoint is used, the authorization process can be run by a script. Ref I thouoght that this might be able to be used in your situation as a workaround. The flow of this workaround is as follows.

Copy the original Spreadsheet.
Authorize IMPORTRANGE except for the Spreadsheet you want to revoke.

By this flow, a Google Spreadsheet is obtained by revoking the specific Spreadsheets you want.

But, in this workaround, the Spreadsheet ID is changed from the original one, because the original Spreadsheet is copied. So, I'm not sure whether this is useful for your actual situation. So, I just propose this workaround.

When the above flow is reflected in a sample script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of the original Spreadsheet, and set spreadsheetIdsOfdeletePermission and 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());
    }
  });
}

When this script is run, the original Spreadsheet is copied and a new Spreadsheet is created by revoking the specific authorizations of "IMPORTRANGE".

Note:

Unfortunately, I cannot know your actual situation. So, if an error occurs related to the continuous requests, UrlFetchApp.fetch instead of UrlFetchApp.fetchAll might be required to be used in a loop with Utilities.sleep.

References:

I really thank you and appreciate your effort, going beyond and above with a workaround, but what I needed was a way to revoke that permission, im going to leave it unanswered in case someone find way and post, that being said, I liked your workaround, understood what you tried to do, creative and Im going to use part of that in other scripts,, thank you for your time and i apologize for the confusion I caused not describing things properly — Bruno Carvalho, Jul 24 '23 at 13:10
@Bruno Carvalho Thank you for replying. About `but what I needed was a way to revoke that permission`, although I have been trying to directly achieve your goal, unfortunately, I couldn't find it. I think that this is my poor skill. I have to apologize for this. From this situation, I proposed a workaround. I deeply apologize that my workaround was not useful for your situation. But, I think that this workaround might be useful for other users. So, I would like to leave this answer. — Tanaike, Jul 24 '23 at 13:15

remove permission to one spreadsheet to pull data from another spreadsheet with IMPORTRANGE

1 Answers1

Issue and workaround:

Sample script:

Note:

References: