1

I've been struggling for two days now to allow the users from my organization to edit the protected pieces of a spreadsheet using only the script I made.

I've been through hell and how I made it work in the end is, I made a fake hidden sheet where the data is stored, and then onOpen, I copy-paste that data to the sheet that the user sees (that they cannot edit). I want to make the UX a bit better if possible, and I was hoping there is a way to force the onOpen trigger from the script. I know I probably didn't do this right, but I cannot spend more time researching, so I need to brute-force it now. (I've tried onChange triggers, I've tried setting permissions in my web app, using doPost, and my brain hurts, this is the first time I'm doing scripting).

TL;DR Is there a way to refresh the whole tab from the script editor? I need to trigger the onOpen event without the user having to reload the page.

bella lugosi
  • 49
  • 7
  • It sounds like you have done some changes to a hidden sheet, and now want to ensure that all users see the same thing in the visible sheet that they are viewing. Have you grasped the real-time nature of Google Sheets? You do not need to change the spreadsheet "at each user's computer". It's all shared. If you change a spreadsheet for one user, it changes for everyone. – doubleunary Sep 27 '21 at 20:12
  • I am not sure I understood you correctly so I'll explain my situation further and hopefully that would get me closer to understanding :) The sheet I built is a database of users: some personal data, and some calculated fields. I am the sheet owner and everyone else is the editor. I have created some buttons to help them add and remove users, but I want the buttons to be the only way they can edit the database. I have protected the sheet they see, which led to two-day battle with permissions. If the user cannot edit a range, neither can the script they ran. Does that make sense? – bella lugosi Sep 27 '21 at 22:32
  • 1
    Your question is about how to trigger `onOpen(e)` to run. @Rubén has answered that. It is unclear whether the question is really about the _problem_ you need to solve, or about the _solution_ you have been working on, under the assumption that it would somehow help. It seems to me that the _problem_ is really about managing range protections. See [What is the XY problem?](https://meta.stackexchange.com/a/233676) – doubleunary Sep 28 '21 at 08:11
  • You are completely right about my initial question, I should've been more precise. I needed an immediate solution to slightly improve the current solution and get it ready for launch. I decided to go with the stupid solution because I'm short on time but I'm fully aware that how I did this is not the way it should be done. That's why I tried to get as much input as possible on both topics. So yes, @Rubén has answered my main question (the cry for immediate help), as for the main problem (X), I think I will have to learn more and just build the whole thing from the scratch. – bella lugosi Sep 28 '21 at 10:54

2 Answers2

2

To "force" the onOpen simple trigger you have the following options

  • call onOpen 
    function respondToChange(e){
  onOpen();
}
    If your onOpen function requires the event object you will have to emulate it. 
    function respondToChange(e){
  const event = {};
  // add the required properties to event
  onOpen(event);
}
  • change the spreadsheet locale by using setSpreadsheetLocale

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
0

When a function is run by clicking a button or choosing a custom menu item, or through a simple trigger such as onOpen(e) or onEdit(e), it runs under the account of the user at the keyboard.

To trigger a function to run under another account that has rights to the protected ranges, you need an installable trigger.

doubleunary
  • 13,842
  • 3
  • 18
  • 51
  • Hello! Thank you for your reply. I tried to do this and the trigger was working (I even managed to filter the events properly, as well as delete the unnecessary triggers as soon as they occur), however, when I tried to run the script that would trigger the onChange event from an account other than mine, it wouldn't run the script. At this point, I was adding various permissions to the appsscript.json and after hours of trying I just couldn't keep on trying anymore. I'm sure there's something small I'm missing but can't seem to figure out what it is. – bella lugosi Sep 28 '21 at 10:58