Is setValues slower than setValue?

Question

I've got a Google Doc with Google Apps Script script running that multiple people contribute to. There is an onEdit trigger that is fired when people edit the sheet. As I understand it, if one person edits the sheet this triggers my onEdit trigger function.

If a second person edits the sheet before the first onEdit finished processing then it stops the first instance and the second onEdit starts without finishing the first one.

To avoid this I'm trying to make my code as efficient as possible with the theory that if the onEdit process takes less time it's less likely to overlap. To accomplish this I've tried to minimize my read/writes. Using a series of logger statements I've tracked down lines of code that are time consuming. I've noticed that one of my setValues() lines takes almost 6 seconds to run. It's a large array being posted back to the sheet after I've made adjustments to it.

My Questions:

1. Does a setValues() call take longer than a setValue()?
2. Does a setValues() call of a small array take less time than a setValues() of a large array?

I know that one setValues() will be much faster than 1000 setValue() commands.

I had presumed one write to the sheet should take about the same amount of time regardless of size. Is there any way I can minimize this time?

Pengelly

Regarding " a second person edits the sheet before the first OnEdit finished processing then it stops the first instance and the second OnEdit starts without finishing the first one" How do you noticed that? – Popham

See camo.githubusercontent.com/… and gist.github.com/tanaikech/d102c9600ba12a162c667287d2f20fe4 If @Tanaike wills, He might add a answer. – Raff

Another option to prevent simultaneous edits is by using LockService – Raff

About your 3 questions, how about the following answer? A1: About Does a setValues() take longer than a setValue()?, it's no. A2: About Does a setValues() of a small array take less time than a setValues() of a large array., it's yes. A3: About Is there any way I can minimize this time?, how about using Sheets API? Ref – Genna

Lance, to my knowledge, even if you make concurrent edits, all triggers fired will finish. The same goes for edits from different users, unless you explicitly stop the execution (via LockService, for example), both will run. So I am curious, what's your data source? Have you checked the executions tab? – Damage

One more thing that might cause longer execution time of setValues() is size of your sheet. Make sure to delete empty rows at the bottom of every sheet. – Raff

In addition to what TheMaster mentioned above, could you please clarify if you measured how long it takes for the looped setValue to run on a sheet where you say the setValues method runs about 6 seconds? It is not unheard of that even a batch operation takes several seconds (in fact, it can take even more) if the size of the array is quite large. – Damage

Thank you everyone for the feedback. I'm relatively new to this game so I'm unfamiliar with some of your suggestions. I'll try to give a bit more info on the sheet that might help clarify some of the feedback. My Google Sheet document consists of a new spreadsheet each week which is a schedule that people select time slots. The OnEdit fxn then copies selections to master log which is quite a large spreadsheet. It's possible that people select a time slot on more than one day which they tend to do rapidly (ie. before the OnEdit script is finished). – Pengelly

@Rubén - Nothing very scientific to notice this. I've noticed I get dropped data in my master log some times and it seems to correlate with when people make multiple selections in a row. One or more of the selections don't update to the master log. I thought this might occur because if the first selection isn't complete when the second starts it stops. I did a bit of reading on this site that I thought supported that but it's very possible I'm completely wrong. – Pengelly

@Genna - I need to do more reading on LockService and Sheets API. I'll consider these as solutions but it seems I might be wrong about what is occurring anyways. – Pengelly

@Oleg - I have not checked the executions tab. I'll need to look at this. Again, I'm unfamiliar with this but will do some reading and see how I can use it. As for your second question I've got a few different Set Values in this same script and they take varying amounts of time. The 6 second one is quite a large array. TheMaster mentions deleting extra row. My Workbook currently does have extra rows set aside for future dates. I guess I need to look into automating the scripts to add more rows and more dates as necessary. – Pengelly

Thank you for replying. I think that the direction for using LockService will lead to a solution. In Stackoverflow, a lot of sample scripts of LockService can be seen. Ref If you have any questions when you check them, feel free to tell us. – Genna

@LanceSalmikivi you can just set an arbitrarily large ( take a look at how long the script takes to run and adjust accordingly, you can even dynamically profile it ) value to wait for the lock - this way you will make sure that the first user to start the edit will apply theirs. – Damage

On further investigation I was right that it's multiple instances of OnEdit causing the issue but wrong with exactly how it was happening. As stated previously, the script grabs a large sheet as an array, adjusts the data, and reposts the adjusted array back into the sheet. Each instance of OnEdit was running to completion but a second instance would grab the array before the sheet had been updated with the changes. Therefore concurrently running instances would overwrite each other and only the final execution would be logged. I expect LockService will be the solution to this. Thanks again – Pengelly

Answer 1

Q1: Does a setValues() take longer than a setValue()?

A1: It's no. About this, you can see from the following experimental result.

Q2: Does a setValues() call of a small array take less time than a setValues() of a large array?

A2: It's yes. About this, you can see from the following experimental result.

Q3: I had presumed one write to the sheet should take about the same amount of time regardless of size. Is there any way I can minimize this time?

A3: When you want to reduce the process cost for putting the values more, how about using Sheets API? About this, you can see it at the figure of "A2".

Note:

• The rationale of above answers is Benchmark: Reading and Writing Spreadsheet using Google Apps Script.

• As @TheMaster's comment, I also think that your issue might be resolved by LockService.

  • I think that the sample script of LockService can be seen at the stackoverflow. Ref

Genna

Awesome @Tanaike. Perfect results! – Dumbstruck