Restrict Editors to Specific Ranges Script

Question

Thanks to the help of someone from Stack, the following script was provided to make a protection script I had written run faster. While the new script does apply protections except the specified ranges, users who are provided editor access are able to edit outside of the desired ranges.

My hope is to ensure that users are only able to enter data in specific ranges, but in order to enter data, they need editor access. Is it possible to restrict editors to only edit the desired ranges?

// 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() {
  // 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: { range: { sheetId }, unprotectedRanges: gridranges[i] } } }));
  
  // 4. Request to Sheets API with the created request body.
  Sheets.Spreadsheets.batchUpdate({ requests: [...deleteProptectedRanges, ...protects] }, spreadsheetId);
}  

Edit: The solution provided by Tanaike works to restrict editors to me (the owner), but the script will be run by other users when they insert a row using the following:

function addNewApparelSKU() {
  const ss = SpreadsheetApp.getActive();
  const ui = SpreadsheetApp.getUi();
  const sheet = ss.getSheetByName('Apparel INV');
  const response = ui.prompt('Enter New SKU', ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() === ui.Button.OK) {
    const text = response.getResponseText();
    sheet.appendRow([text]);
    sheet.sort(1);
    myFunction(); //references the Protection script
  }
} 

When this script is used by another editor, it gives an error because the user can't insert a row due to the restrictions.

Answer 1

I believe your goal is as follows.

• You want to protect the specific ranges in each sheet and want to make users edit only the specific ranges.
• From your updated question, the script of addNewApparelSKU is run by clicking a button on Spreadsheet.

About the following script was provided to make a protection script I had written run faster., if the script of your previous question is used, how about the following modified script?

And, in this case, it is required to run the script (in this case, it's myFunction().) by the owner (you). For this, I would like to run this script using Web Apps. By this, the script can be run by the owner.

Usage:

1. Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And pleaes enable Sheets API at Advanced Google services.

And, please set your email address to const email = "###"; in myFunction.

function addNewApparelSKU() {
  // This is from addNewApparelSKU().
  const ss = SpreadsheetApp.getActive();
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Enter New SKU', ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() === ui.Button.OK) {
    const text = response.getResponseText();

    const webAppsUrl = "https://script.google.com/macros/s/###/exec"; // 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('Apparel INV');
  sheet.appendRow([text]);
  sheet.sort(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 = "###"; // <--- 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);
}

2. Deploy Web Apps.

The detailed information can be seen at the official document.

1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".

2. Please click "Select type" -> "Web App".

3. Please input the information about the Web App in the fields under "Deployment configuration".

4. Please select "Me" for "Execute as".

   • This is the importance of this workaround.

5. Please select "Anyone" for "Who has access".

   • In your situation, I thought that this setting might be suitable.

6. Please click "Deploy" button.

7. Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec.

   • When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
   • You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

8. Please set the Web Apps URL to const url = "https://script.google.com/macros/s/###/exec"; in the above script.

9. Please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this. You can see this flow at here.

3. Testing.

Please run addNewApparelSKU() by clicking the button. By this, the script is run by the owner.

Note:

• When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
• You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".
• My proposed script is a simple script. So please modify it for your actual situation.

References:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script