Is setValues slower than setValue?

0
votes

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?

1
arraysgoogle-apps-scriptgoogle-sheetsread-write
See camo.githubusercontent.com/… and gist.github.com/tanaikech/d102c9600ba12a162c667287d2f20fe4 If @Tanaike wills, He might add a answer. - TheMaster
Another option to prevent simultaneous edits is by using LockService - TheMaster
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 - Tanaike
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? - Oleg Valter
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. - TheMaster

1 Answers

5
votes

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

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

enter image description here

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.

enter image description here

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: