Use IMPORTRANGE to remove one spreadsheet's permission to extract data from another spreadsheet

Question

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

Answer 1

I believe your goals are as follows.

• You have a Google Spreadsheet that contains the authorized IMPORTRANGE function.
• You want to revoke multiple authorizations from the spreadsheet for IMPORTRANGE.

Problems and Solutions:

Check out the method to directly cancel the IMPORTRANGE authorization 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.

1. Copy the original spreadsheet.
2. Authorization 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 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 authorization for "IMPORTRANGE".

Notice:

• Unfortunately, I cannot understand your actual situation. Therefore, if an error occurs related to consecutive requests, you may need to use UrlFetchApp.fetch instead of UrlFetchApp.fetchAll in a loop with Utilities.sleep .

references:

• How to allow access to the importrange function through application scripts?
• fetchAll(request)