0

I have written a form for user to enter. I used code to guide them through the form. For example, after entering Cell A4, it jumps to D4, after D4 it jumps to A5, etc. Even though the execution time (as viewed in execution transcript) is not large (close to 0.1 seconds most of the time) the Google sheet response time is generally about 1 second. It feels quite laggy. Is there a way to improve the responsiveness of Google sheet of this action? Execution transcript

Rubén
  • 34,714
  • 9
  • 70
  • 166
Tam Le
  • 360
  • 3
  • 17
  • If you can [reduce the calls to the sheet](https://developers.google.com/apps-script/guides/support/best-practices) (i.e., those `Range.getA1Notation()`s), that will help a small bit. Also, [please don't post pictures of code/errors](https://stackoverflow.com/help/how-to-ask) and do try to include a sample of your relevant code so people can better understand what issues you might be having. – sinaraheneba May 27 '19 at 02:52
  • 1
    @Altigraph As you can see, **4** operation of the `Range.getA1Noation()` cost less than _1 millisecond_. Meanwhile, the response time is about 1 second. That probably should be quite low on optimization priority list. My issue the difference between script time and response time, not the code itself. – Tam Le May 27 '19 at 03:05
  • I'm commenting on improving what can be improved about the server-side code because that is what Google Apps Script is. If you want a more responsive client, you'll need to use something else instead of/in addition to GAS; e.g. a sidebar as shown below, some other client-side UI calling your Apps Script as a [web app](https://developers.google.com/apps-script/guides/web), etc. – sinaraheneba May 27 '19 at 03:14

2 Answers2

2

Besides the time that get a method to be executed you should consider the "transport time" (the communication between Google Servers and the user device), the spreadsheet recalculation time and the UI refresh time.

To improve the form users' chances to have a better experience,

  • avoid or reduce the use of formulas
  • avoid or reduce the use of volatile functions like NOW()
  • avoid or reduce of the use of open references like A:A
  • reduce the length of calculation dependency chains
  • etc.

Also ask the form users to

  • remove all the web browser extensions
  • close all other web browser tabs
  • close all other local applications
  • use a very fast Internet connection
  • etc.

Further reading

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Is there a way to measure "transport time", the spreadsheet recalculation time and the UI refresh time ? – Tam Le May 27 '19 at 03:54
  • 1
    Bruce McPherson (http://ramblings.mcpher.com) has a post including code about that on his site (maybe that article doesn't cover all, but it's a really great start). – Rubén May 27 '19 at 03:56
  • Great resource, I wish I knew it much sooner. Couldn't find the part you are pointing to though. – Tam Le May 27 '19 at 04:32
1

Using Apps Script to try to move the user around the spreadsheet is probably not something you will be able to make feel comfortable.

Instead, see the guide on dialogs and sidebars, and consider if building a form in HTML/Javascript would be a more appropriate solution (assuming simply building a Google Form is not).

sinaraheneba
  • 781
  • 4
  • 18
  • I am building a form to have user enter data from an image. After they done with an image, the form clears, get the next image to process. Is that possible with Google Form? – Tam Le May 27 '19 at 03:58
  • To make sure I understand what you want: An image is loaded on the page, the user does some data entry from the contents of that image, submits their entry, the form clears, a new image is loaded, and the user repeats this process? I've not done much with Google Forms to know if this is possible or not with their platform, but it is *absolutely* possible using a side-bar (if you want them to use this while being on the sheet) or with google apps script deployed as a web app. – sinaraheneba May 27 '19 at 04:06
  • e.g., a simple HTML page with your image and the form to submit. Can also pre-fetch the next image so it's there immediately upon submission, without needing to wait for a response from the server. – sinaraheneba May 27 '19 at 04:07
  • "Pre-fetch" you are talking about is in Google script/sheet or HTML page? – Tam Le May 27 '19 at 04:18
  • potentially, both, depending on how you build it and where you are getting these images from (i.e., Drive via DriveApp, or some other service). For instance, loading two images when the page is loaded, displaying the first one. When the first is submitted, the client switches to the second image and begins collecting the third, or however you find it works best (loading more or fewer images in advance, working in batches, etc.) – sinaraheneba May 27 '19 at 04:21
  • I am getting the images by `.insertImage('a_Google_drive_link',1,1)`. How do I load 2 images at once? – Tam Le May 27 '19 at 04:30
  • Again, Google Apps Script is run on the cloud, not on your client. Trying to make your client run more smoothly by changing the apps script code is like trying to improve air traffic at Heathrow by making faster planes. I am talking about building your form in HTML, not using the spreadsheet. – sinaraheneba May 27 '19 at 05:00
  • "potentially, both, depending on how you build it and where you are getting these images from (i.e., Drive via DriveApp, or some other service)" I asked that because of these words. – Tam Le May 27 '19 at 08:00
  • Yes. If you are fetching your images from another location, you may only need to write that code on the client-side. If you are fetching your images from drive, you will need to write Apps Script code to send it to the client. – sinaraheneba May 27 '19 at 09:01