1

I have a script with a dozen functions that we use for various tasks in our work. It's always been working great, I never deployed it as a web app or an API (And I also never was able to understand what those are or how to use them). Now, I added some protection to the sheet in question. One specific function adds values to a protected range. After the protection came into effect, it's not letting my employees execute the function in question since they don't have express permission to change those protected ranges manually. (changing them through the script is allowed).

So my question as somewhat of a noob to GAS, is how do I make this specific function execute as me every time ?

P.S: I did do a lot of research on the subject but I honestly couldn't figure it out. I know I need something called doGet or doPost, but I do not know what those are, how to use them or what they do. Looking them up only got me more confused..

Would be grateful for any kind of help or guidance. I thought this wouldn't be so much of an issue, but I've been at it for the last 3 hours and it's driving me crazy.

Here's what the function looks like : (Column C is the protected Range, you can see that some code eventually crosses it at some point)

function jun() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('E2:AG2').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Expenses 2019'), true);
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).createFilter();
  spreadsheet.getRange('B1').activate();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues(['1', '2', '3', '4', '6', '7', '8', '9', '10', '11', '12', ''])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(2, criteria);
  spreadsheet.getRange('A1').activate();
  criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues(['Total'])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(1, criteria);
  spreadsheet.getRange('245:245').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('\'EXPENSERIZER\'!E2:AG2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getActiveSheet().getFilter().remove();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('EXPENSERIZER'), true);
  spreadsheet.getRange('C11').activate();
};
Abdel Beldy
  • 11
  • 1
  • Set up a installable trigger? – TheMaster Jun 21 '19 at 19:21
  • Not really sure how that would help ? what would be the trigger ? and would it change permissions temporarily or make the function use the author's user account ? – Abdel Beldy Jun 21 '19 at 19:26
  • `onEdit` may help. What's the trigger now? How is the function executed by the user? It'll `make the function use the author's user account` – TheMaster Jun 21 '19 at 19:29
  • Now, i've linked the function to an image on the sheet that users click to execute. Could onEdit work ? I doubt it, it shouldn't be executed after an edit, but then again I don't know how onEdit works exactly. Any enlightenment would be greatly appreciated. – Abdel Beldy Jun 21 '19 at 20:15
  • `onEdit` could work with a checkbox instead of a image. Alternatively, yes, publish the web-app to run as you and ping the web app from your function.[This](https://stackoverflow.com/a/54622357/) might help. Only difference is your doGet() will execute `jun()` and your testFunction will be linked to the image – TheMaster Jun 21 '19 at 20:19
  • Okay, if I understand correctly, I just need to put the `jun()` code into a doGet wrapper ? and create another function `testFunction` with the exact same code and link that to the image ? Don't I need to add something else to the doGet like a contentService part or something ? could you tell me what I need to change in the code? – Abdel Beldy Jun 22 '19 at 09:04
  • Why don't you try and see? If you have a specific problem, ask a new question. Yes, you need to return something, like success or anything else you like, which will be received in `testFunction` after the urlFetch() inside the testfunction indicating everything went well. If you do have a problem, try to provide [mre] in a new question(It'll help you debug). – TheMaster Jun 22 '19 at 09:26

0 Answers0