5

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.

IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89
  • I suppose you could use [Protection](https://developers.google.com/apps-script/reference/spreadsheet/protection) class at the beginning of the script function to remove access from all editors and then add those users back to being able to edit (or simply put, remove protection) at the end of the function. – Sourabh Choraria Oct 02 '19 at 03:47

1 Answers1

3

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

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • 2
    @IMTheNacho You could also use hidden [developer metadata](https://developers.google.com/sheets/api/guides/metadata) to follow the same index/key approach mentioned above. – TheMaster Oct 02 '19 at 20:12
  • @TheMaster I had no idea that was there. I will look into it. Looks like it could be really helpful. Thanks! – IMTheNachoMan Dec 10 '19 at 03:34
  • 1
    Thanks @Rubén. I ended up doing what you said. I am also using Data Studio for folks to have a view into the data so they can sort in Data Studio without mucking with the source data. :) – IMTheNachoMan Dec 10 '19 at 03:36