Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – How to Ignore Permissions Error with Apps Script on Google Sheets

I have an Apps Script that updates columns in a Google Sheet. It is only updating unprotected columns. At the end I do a sort on all columns (including some protected columns). Those without permissions get an error from google sheets.

I tried to put a try/catch statement on the sort but that did not work. Google sheets still gives the permissions error and the code stops.

Can I either put some form of a try/catch around the sort or can I somehow see if the user has permissions before the code executes?

Here is the code that gives the Google Sheets permission error:

var wb = SpreadsheetApp.openById(id)
var ss = wb.getSheetByName(sheetname)
var sortrange = ss.getRange('A5:AM')
sortrange.sort(1)

This is the try/catch I attempted:

var wb = SpreadsheetApp.openById(id)
var ss = wb.getSheetByName(sheetname)
var sortrange = ss.getRange('A5:AM')
try {
  sortrange.sort(1)
}
catch(ExceptionError) {
}
Tags:

2

Answers


  1. 0

    You need to put the api request in the try...catch.... Besides, there would be a syntax error of Exception e.

    try {
  var wb = SpreadsheetApp.openById(id)
  var ss = wb.getSheetByName(sheetname)
  var sortrange = ss.getRange('A5:AM')
  sortrange.sort(1)
}
catch(ExceptionError) {
 // do nothing since you want to ignore it.
}
    Login or Signup to reply.
  2. 0

    You can modify protected ranges through Protection Class.

    • In Google’s Documentation, you can find helpful information and examples that can clarify possible doubts about usage;
    • I suggest you the following strategy: you’ll have to allow editing before you do the sorting, then eventually protect the range once more; of course, you could come up with different ways;
    • By storing the returning value of the function shown below, you’ll be able to iterate over it and remove protection — check canEdit() in the documentation — of the range stopping your routine;
    SpreadsheetApp   
 .getActive()
 .getProtections(SpreadsheetApp.ProtectionType.RANGE);
    • Another strategy could be raising privillege to the Editor(s), and setting yourself as one — check the method addEditor(user) in the documentation to do so;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search