0

I have google sheets addon in Google Workspace Marketplace. I want to store the emails of the users installing the addon. I'm thinking of three possible ways.

  1. Write to private spreadsheet under the addon account (different than the user's). Addon is running under user's so the question is, is this even possible - accessing the addon account (specifically writing to a spreadsheet) from within the user google account context?

  2. using PropertiesService - Write the user to script properties using PropertiesService class. One problem with this one is the limitations as explained in Quotas for Google Services. for me, at lease for now, this is enough. However the question is how to access those script properties programmatically. Of course I can access the data from the script editor, but this is not practical if I want for example to send mail to all the users.

  3. adding code to the addon that will be available only for specific users (admin). In this case since I can read the users from the script properties, and maybe write them to spreadsheet to be used later. This looks ugly, I admit.

I'm not asking for code solutions, but suggestions for the right or best approach.

OJNSim
  • 736
  • 1
  • 6
  • 22

1 Answers1

1

The easiest solution is to create a database

  • Create a spreadsheet located on your Drive, shared as "Everyne can edit".
  • Implement a flow where after Add-n installation data containing the user"s email will be appended to the spreadsheet.
  • This request will take place on user's beahlf, however given that the spreadsheet is shared publicly, there won't be any access permission issues
  • Even if the spreadsheet is shred publicly - given that the spreadsheet id is not known by anyone other than the Add-on code, you do not need to worry about undesired access to the database.
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • I'm not sure how I feel regarding this public shared spreadsheet, especially for with edit permissions. I understand your "no one know that" argument, but still...what about another "not that easy" solutions, any other approach you can suggest? – OJNSim Dec 06 '21 at 18:39
  • If you don't feel good about the public spreadsheet, you can implement some code on your end (since you are the spreadsheet owner) to regularly move incoming data to a private spreadsheet. The other two options you mentioned - they are doable, but you are aware yourself about the disadvantages. Another option is to make the Addon send you an email when it gets installed, but for this you will need to implement a Gmail scope - not a restricted one, but if your Addon is not related to Gmail, the users might find it confusing to have to authorize a Gmail scope. There is no perfect solution... – ziganotschka Dec 06 '21 at 20:44
  • Addon actually asking for Gmail scope (send mail). This is for the case there was unexpected exception, and addon will asks to send mail with error details. but what you say makes me think about this again, maybe I will remove it. Anyhow to send mail on behalf of the user to notify the addon installation, is not an elegant one. I have to think about it. Your suggestion to move the data to private spreadsheet is better. Not sure what you mean disadvantage referring (also) the first option I mentioned - saying disadvantage sounds like it is doable. is it? – OJNSim Dec 06 '21 at 21:03
  • Yes, it is. You can access the script properties by e.g. running a respective function manually from the script editor. Also, in the old editor, you can go on `File->Project Properties -> Script Properties` to see all existing script properties. – ziganotschka Dec 07 '21 at 14:44
  • Not asking about the script properties but about the other option, accessing a **private** spreadsheet in another (add-on) account, from the user account scope while installing, or simply using the add-on. – OJNSim Dec 07 '21 at 19:00
  • Option1 is only possible the way I explained in my solution. So making the spreadsheet publicly editable. Acting under an "Addon account" that is different form the user account - no, I do not think that this is possible. – ziganotschka Dec 07 '21 at 20:22
  • I'll go with your suggestion, write to public and move to a private spreadsheet. I am not 100% happy about doing public spreadsheet, but It is the most reasonable approach for now. I just found out that `onEdit(e)` is actually triggered whenever new record is added by a **script**, and not only when manually editing the spreadsheet as I mistakenly thought. So that also soften this approach, as the records will be moved to the private ss immediately. I will also remove the Gmail scope and use the same method to log errors. – OJNSim Dec 08 '21 at 10:12
  • This is interesting! Where did you find this information about "onEdit(e) is actually triggered whenever new record is added by a script"? – ziganotschka Dec 08 '21 at 10:24
  • My mistake. I made a test, in which I `console.log()` within the `onEdit`. I saw a new line in console but it was probably an edit I made manually without notice . Anyway, I was wrong, `onEdit` is **not** triggered by edits made by scripts. I still have to see later how this process can be automated. – OJNSim Dec 08 '21 at 11:56
  • See https://stackoverflow.com/questions/59286515/onedite-not-generating-trigger-event-when-cell-value-changes-due-to-inbuilt-fu/59287849#59287849 – ziganotschka Dec 08 '21 at 12:39
  • I actually do like this solution for two reasons: _1)_looks like misuse of script properties. what is the range is so much bigger? _2_ Consider this: A change occurred. and trigger is fired. While it is still executed more change(s) occur. This is open gate for conflicts. This scenario is actually valid for every mechanism that copy/move data from one place to another. This is not unsolved problem, you can handle that with more robust code, yet it is a problem that needs to be handled. If we'll ignore _#2_, I would prefer time driven triggers. – OJNSim Dec 22 '21 at 15:19
  • 1
    but I actually trying to do it differently using web app. This way it is possible to write the data to a private SS in the first place, also avoiding the the possible data consistency problem I referred in the previous comment. Web app has other issues, as I described [here](https://stackoverflow.com/questions/70449888/what-is-the-benefit-of-calling-google-web-app-with-access-token-and-what-are-the). My I think for now it is a better approach – OJNSim Dec 22 '21 at 15:31