Thanks to another user here, I was able to allow other users to add a new SKU to a sheet without unprotecting it. Original post seen here: Restrict Editors to Specific Ranges Script

Now, I am trying to do the inverse, allow users to delete a 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);
    };
    };
  };
};

Since I am new to javascript, and VERY new to Web Apps, I tried to Frankenstein the above code into the answer I was provided in the above URL. 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);
}

Any insights are greatly appreciated.

CodePudding user response：

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:

• Using a checkbox
• Hook it to a installable edit trigger, which runs as the user who installed the trigger. Therefore, if the owner installs the trigger, no matter who edits the sheet, the trigger runs as the owner, giving access to privileged resources including protected ranges.

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:

• Advantage:

  Code simplicity and maintainabilty. No need for webapp or any complicated setup.

• Disadvantage: Security

  If the code is bound to the sheet, editors of the sheet get direct access to the script of the sheet. So, any editor with malicious intentions would be able to modify the code. If the function with installable trigger has gmail permissions, any editor would be able to log all the emails of the owner. So,special attention needs to be paid to permissions requested. Note that, this is already the case with your web app setup. Any editor maybe able to modify doGet to access protected data. If the webapp is in a separate standalone script, this isn't a issue.