google-sheetsgoogle-apps-scriptspreadsheet-protectionprivilege-escalation

How to run certain scripts under the authority of a specific user?

I was able to allow other users to add a new SKU to a sheet without unprotecting it (Original post). Now I am trying to do the inverse, to allow users to delete an SKU without unprotecting the sheet.

I started with the following, which works as expected:

function deleteEachRow(){
  const ss = SpreadsheetApp.getActive();
  var SHEET = ss.getSheetByName("Ordering");
  var RANGE = SHEET.getDataRange();
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('WARNING: \r\n \r\n Ensure the following sheets DO NOT contain data before proceeding: \r\n \r\n Accessory INV \r\n Apparel INV \r\n Pending TOs \r\n \r\n Enter New SKU:', ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() === ui.Button.OK) {
    const text = response.getResponseText();
    var rangeVals = RANGE.getValues();
  
    //Reverse the 'for' loop.
    for(var i = rangeVals.length-1; i >= 0; i--){
    if(rangeVals[i][0] === text){
      
      SHEET.deleteRow(i+1);
    };
    };
  };
};

I tried to Frankenstein the above code into the answer I was provided. Now the script runs without error but fails to delete the entered SKU as expected. This is the script I am running:

function deleteEachRow1(){
  const ss = SpreadsheetApp.getActive();
  var SHEET = ss.getSheetByName("Ordering");
  var RANGE = SHEET.getDataRange();
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('WARNING: \r\n \r\n Ensure the following sheets DO NOT contain data before proceeding: \r\n \r\n Accessory INV \r\n Apparel INV \r\n Pending TOs \r\n \r\n Delete Which SKU?:', ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() === ui.Button.OK) {
    const text = response.getResponseText();

    const webAppsUrl = "WEB APP URL"; // Pleas set your Web Apps URL.

    const url = webAppsUrl + "?text=" + text;
    const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
    // ui.alert(res.getContentText()); // You can see the response value using this line.
  }
}

function doGet(e) {
  const text = e.parameter.text;
  const sheet = SpreadsheetApp.getActive().getSheetByName('Ordering');
  var rangeVals = RANGE.getValues();
  
    //Reverse the 'for' loop.
    for(var i = rangeVals.length-1; i >= 0; i--){
    if(rangeVals[i][0] === text){
      
      SHEET.deleteRow(i+1);
    };
    };
  myFunction();
  return ContentService.createTextOutput(text);
}

// This script is from https://tanaikech.github.io/2017/07/31/converting-a1notation-to-gridrange-for-google-sheets-api/
function a1notation2gridrange1(a1notation) {
  var data = a1notation.match(/(^.+)!(.+):(.+$)/);
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(data[1]);
  var range = ss.getRange(data[2] + ":" + data[3]);
  var gridRange = {
    sheetId: ss.getSheetId(),
    startRowIndex: range.getRow() - 1,
    endRowIndex: range.getRow() - 1 + range.getNumRows(),
    startColumnIndex: range.getColumn() - 1,
    endColumnIndex: range.getColumn() - 1 + range.getNumColumns(),
  };
  if (!data[2].match(/[0-9]/)) delete gridRange.startRowIndex;
  if (!data[3].match(/[0-9]/)) delete gridRange.endRowIndex;
  return gridRange;
}

// Please run this function.
function myFunction() {
  const email = "MY EMAIL"; // <--- Please set your email address.

  // Please set your sheet names and unprotected ranges you want to use.
  const obj = [ 
  { sheetName: "Ordering", unprotectedRanges: ["O5:P", "C2:E2"] },  
  { sheetName: "Accessory INV", unprotectedRanges: ["E5:H"] },  
  { sheetName: "Apparel INV", unprotectedRanges: ["E5:F"] },  
  {sheetName: "Pending TOs", unprotectedRanges: ["E6:H"] }, 
  {sheetName: "INV REF", unprotectedRanges: ["C6:C"] },
];

  // 1. Retrieve sheet IDs and protected range IDs.
  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const sheets = Sheets.Spreadsheets.get(spreadsheetId, { ranges: obj.map(({ sheetName }) => sheetName), fields: "sheets(protectedRanges(protectedRangeId),properties(sheetId))" }).sheets;
  const { protectedRangeIds, sheetIds } = sheets.reduce((o, { protectedRanges, properties: { sheetId } }) => {
    if (protectedRanges && protectedRanges.length > 0) o.protectedRangeIds.push(protectedRanges.map(({ protectedRangeId }) => protectedRangeId));
    o.sheetIds.push(sheetId);
    return o;
  }, { protectedRangeIds: [], sheetIds: [] });
  
  // 2. Convert A1Notation to Gridrange.
  const gridranges = obj.map(({ sheetName, unprotectedRanges }, i) => unprotectedRanges.map(f => a1notation2gridrange1(`${sheetName}!${f}`)));

  // 3. Create request body.
  const deleteProptectedRanges = protectedRangeIds.flatMap(e => e.map(id => ({ deleteProtectedRange: { protectedRangeId: id } })));
  const protects = sheetIds.map((sheetId, i) => ({ addProtectedRange: { protectedRange: { editors: {users: [email]}, range: { sheetId }, unprotectedRanges: gridranges[i] } } }));
  
  // 4. Request to Sheets API with the created request body.
  Sheets.Spreadsheets.batchUpdate({ requests: [...deleteProptectedRanges, ...protects] }, spreadsheetId);
}
Solution

  • Probably the easiest way to do this would be to avoid using a button and using a checkbox with a installable edit trigger, which also has a great side effect of mobile support.

    Proposed solution:

    The installable version runs with the authorization of the user who created the trigger, even if another user with edit access opens the spreadsheet.

    Notes: