-1

I’ve created a Google Sheet – kind of like an app – using script. It’s a number of blank cells where once added some information, it creates a string with that information in the right order. I created this to help some colleagues. We are using free accounts.

I face 2 problems:

  1. The app is supposed to be used by only one person at a time, but I’m sure at some point two or more people will want to use it at the same time. Is there a way to allow this without they interfering with each other? I’ve read you can share a link that creates copies of your doc, but that wouldn’t work in this case because I intend to keep updating it regularly.

  2. The changes people make to the sheet will stay there. Right now I have a onOpen function that just rewrites everything, but if someone accesses the document while someone is using it they’ll rewrite everything and ruin their work.

Any ideas on how to solve these two problems? Thank you so much in advance!

Rubén
  • 34,714
  • 9
  • 70
  • 166
Aurum
  • 1
  • 1
  • Are you using a free Google account or a Google Workspace accounts? What type of account are using the spreadsheet editors? – Rubén Apr 03 '22 at 02:25
  • 1
    Hey Rubén! Thanks for replying. It’s a free google account for all. – Aurum Apr 03 '22 at 09:09
  • Hey Aurum, I face the same issue at where I work as well. There isnt a function on Apps Script currently that will cut it for now, might want to give a product feedback to Google:https://issuetracker.google.com/issues/new?component=191640&template=824113 But what I find works is duplicating the same UI and backend functions for allowing more users per time. Perhaps you can share a sample Spreadsheet. – Nami888 Apr 03 '22 at 14:57

1 Answers1

0

I think that you have to find another way to implement your "app" because Google Sheets not a good tool for limiting that only one user edit a spreadsheet a time, but if for any reason you decide to keep with it,...

... you need to implement a workflow like the following

  1. Save the active user email on PropertiesService.getUserProperties().
    This should be done by each user by running a script themselves. The most user-friendly will be by using a macro, custom menu or button (a drawing with an assigned Google Apps Script function). You might use an open installable trigger to validate that the active user have already done this.

  2. You might have to make the function that clears the data a "smart" function.
    You might have to consider additional cases, like if the owner of the spreadsheet opens it when there is an editor working on the spreadsheet.

  3. Use installable triggers to manage the sharing settings.

    • Use an open trigger to remove all the editors except the active user
    • Use a time-driven trigger to add again the editors. To make this work effectively should define how the time-driven trigger will know that the last editor have finished their session, i.e. you might use DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getLastUpdated()

The above should help you with both problems, as long you as the owner do not open the spreadsheet as is used by someone else.

Other alternatives that might work better is to create an add-on or a web application.

Related

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