Is it possible for a GAS script to lock a Google Sheet so nobody else can alter it until the script is done

Question

I am familiar with the Lock Service but that is only for locking scripts.

I have some code that will "process" a large Google Sheet. My script needs to re-order the rows. I need/want to make it so while the script is running nobody else can change the order. However, I still need another script to be able to append rows.

We use a Google Form for our team's intake. It appends rows to a sheet. I have an hourly job that will go through all the rows/records and "process them". I have a column that stores the last time a record/row was "processed". I want to sort on that column such that the "oldest" records are on top and then start processing from the top down. If the script fails or times out then the next iteration will just start over...

I know I could use getValues or getDisplayValues to get an array and then write the array back but I worry what would happen if someone sorted the rows as it would muck things up when writing the array back.

Is there some way to accomplish my goal? I want to be able to process the records, and maintain row order to avoid breaking my processing.

Answer 1

The way to block a spreadsheet "completely" is by changing the spreadsheet sharing settings. Remove all editors or change them to viewers, once your script finish, change them back as editors. In a extreme case, usa a second account to act as the owner of the critical files / spreadsheets and only use it for this purpose,so you could block your regular account for doing changes to the spreadsheet.

NOTE: A Google Form editResponseUrl could be used to edit the linked spreadsheet.

I'm facing a similar situation but I took a different approach, I'm using an index/key column (you could use the timestamp column) and using the index/key to save each edited row to the right position, then write the whole resulting array in a single operation (by using setValues()). In my case this is simple because I only require values, I'm not worried about notes, data validation, conditional formatting, comments, etc. and there isn't a Google Form linked to my spreadsheet.

Related

• Google Spreadsheet -- get sharing permissions by script
• Any way to share google docs programmatically?