Tuesday, June 1, 2010

Optimizing Spreadsheet Operations

As you probably expect, most scripts read from or write to a spreadsheet, so we thought it'd be valuable to share some best practices for making spreadsheet automation as fast and efficient as possible.

Let's start with a general principle: JavaScript operations are an order of magnitude faster than talking to any services. Put another way, anything that Apps Script can do by itself will be exceedingly fast, while talking to Spreadsheets, Translate, Sites, etc, will be measurably slower. The best advice here is to call services sparingly - the least expensive spreadsheet operation is no spreadsheet operation!

Obviously though the entire purpose of Apps Script is to act as glue between all of these services, so how can keep things fast and efficient without sacrificing functionality? Well, there's really just one golden rule:
Don't make several calls when you can do it in one.
We'll talk a little about the functions available to make this easier, but first let's explain why this is the case. The obvious reason is that the calls are relatively expensive, so making fewer calls is naturally going to improve performance. Secondly, if you can batch your calls (more on batching in a moment), it means that you're less likely to interleave reads and writes (gets and sets).

Why is interleaving sets and gets so bad? Knowing that calls to the spreadsheet are relatively slow, we do some look ahead caching, and some write caching. Chances are that if you read (get) one cell then you're quite likely to read another one close by, relatively soon. Likewise, if you write (set) one cell then you're also likely to write another one relatively soon. Think about any loops in your code - you may iterate over spreadsheet rows, or columns for example, and the caches will really help you here.

Every time you do a read, we must first empty (commit) the write cache to ensure that you're reading the latest data (you can force a write of the cache by calling (SpreadsheetApp.flush();). Likewise, every time you do a write, we have to throw away the read cache because it's no longer valid. Therefore if you can avoid interleaving reads and writes, you'll get full benefit of the cache.

We've explained that calling services is expensive, and that you can gain the maximum benefit of the cache by grouping reads and writes. A logical next step is to use the batch operations in the Spreadsheet API. Most set and get functions have 'batch' versions, for example there's a getValue/setValue pair, and there's a corresponding getValues/setValues pair.

These batch functions operate on a range of cells at once, and you only pay the cost of a single service call even though there's a lot more data being transferred. Additionally, by organizing your code to maximize use of batch functions you are also avoid the interleaved reads and writes that can ruin cache performance - a rare double win!

To illustrate the benefits of the batch setters over operating cell by cell, let's take a real script example. We've chosen the gallery script "Spreadsheet Fractal Art" as our optimization candidate, but the same techniques can be applied to most scripts. This particular script sets the background colors of every cell in a 100x100 grid, so it is perfect for batch writing - here's the original loop that writes colors to the sheet:

var cell sheet.getRange('a1');
for (var 0100y++{
  xcoord xmin;
  for (var 0x < 100x++{
    var getColor_(xcoordycoord);
    cell.offset(yx).setBackgroundColor(c);
    xcoord += xincrement;
  }
  ycoord -= yincrement;
  SpreadsheetApp.flush();
}


You can see that we loop over 100 rows and 100 columns, writing a cell color at each location for a total of 10,000 calls to the spreadsheet. Our write back cache does help a little - in fact we force a write back using flush at the end of every line, so there are really only 100 calls to the spreadsheet owing to the good caching. In our rough tests, calling 'plot' as written takes about 1 minute and 10 seconds.

We can re-write the code to make use of the batch version of setBackgroundColor:

var cell sheet.getRange('a1');
var colors new Array(100);
for (var 0y < 100y++{
  xcoord xmin;
  colors[ynew Array(100);
  for (var 0x < 100x++{
    colors[y][xgetColor_(xcoordycoord);
    xcoord += xincrement;
  }
  ycoord -= yincrement;
}
sheet.getRange(11100100).setBackgroundColors(colors);


In our rough tests, this one clocks in at around 1 second. The timer isn't accurate enough to tell us if it is a full 100x faster, but even if it is only a 60x improvement it is still a huge benefit gained simply by playing to the strengths of the system.

If you have any great performance optimizing tips, or code that is running very slowly, we encourage you to post in the Apps Script support forum -- and we'll feature some of the best tips in a future post.





Note: we made a minor modification to the "Spreadsheet Fractal Art" script to make this example easier to follow.  The script as published uses the setBackgroundRGB call, rather than setBackgroundColor you see above - thus the getColor_ function was changed as follows:
if (iteration == max_iteration{
   return "#000000";
else {
   var 255 (iteration 5);
   Math.min(255Math.max(0c));
   var hex Number(c).toString(16);
   while (hex.length 2)
     hex "0" hex;
   
   return ("#"+hex+"3280");
}

42 comments:

  1. Nice post, very helpful. Would you expand on clearing write and read cache part?

    ReplyDelete
  2. Tell me... is;

    SpreadsheetApp.openById("mySpreadsheet").getSheetByName("mySheet");

    faster than;

    var ss = SpreadsheetApp.openById("mySpreadsheet");
    var sheet = ss.getSheetByName("mySheet");

    In other words, does chaining the function calls count as one single call to the service? Or do they still count as individual function calls?

    If it makes no difference, then there are cases where I'd prefer to do it the 2nd way, to make the code easier to read and more modular.

    ReplyDelete
  3. I'm glad I found this web site, I couldn't find any knowledge on this matter prior to.Also operate a site and if you are ever interested in doing some visitor writing for me if possible feel free to let me know, im always look for people to check out my web site.
    eliminare blatte

    ReplyDelete
  4. KFC with an end goal to meet and surpass client desires offers its esteemed clients to round out a client criticism to be filled in return for a prize coupon with a free chicken cup.
    visit my kfc experience

    ReplyDelete
  5. Whataburger is popular for serving great quality, hot and crisp nourishment to their clients and its combo of burger, drink, chips still remain the most preferred combo on the menu.
    visit whataburgersurvey

    ReplyDelete
  6. Whataburger is popular for serving great quality, hot and crisp nourishment to their clients and its combo of burger, drink, chips still remain the most preferred combo on the menu.
    visit whataburgersurvey

    ReplyDelete
  7. Whataburger is popular for serving great quality, hot and crisp nourishment to their clients and its combo of burger, drink, chips still remain the most preferred combo on the menu.
    visit whataburgersurvey

    ReplyDelete
  8. Whataburger is popular for serving great quality, hot and crisp nourishment to their clients and its combo of burger, drink, chips still remain the most preferred combo on the menu.
    visit whataburgersurvey

    ReplyDelete
  9. Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
    TOP servicenow online training

    ReplyDelete
  10. Poker online situs terbaik yang kini dapat dimainkan seperti Bandar Poker yang menyediakan beberapa situs lainnya seperti http://62.171.128.49/hondaqq/ , kemudian http://62.171.128.49/gesitqq/, http://62.171.128.49/gelangqq/, dan http://62.171.128.49/seniqq. yang paling akhir yaitu http://62.171.128.49/pokerwalet/. Jangan lupa mendaftar di panenqq silakan dicoba ya boss

    ReplyDelete
  11. Hello there! I know this is kinda off topic however , I’d figured I’d ask. Would you be interested in exchanging links or maybe guest writing a blog article or vice-versa? My blog addresses a lot of the same topics as yours and I believe we could greatly benefit from each other. If you might be interested feel free to send me an e-mail. I look forward to hearing from you! Superb blog by the way!
    Java Training in Chennai

    Java Training in Bangalore

    Java Training in Hyderabad

    Java Training
    Java Training in Coimbatore


    ReplyDelete
  12. I just loved your article on the beginners guide to starting a blog.If somebody take this blog article seriously
    in their life, he/she can earn his living by doing blogging.Thank you for this article.
    tibco sportfire online training

    ReplyDelete
  13. Nice post!

    Worried About QuickBooks Error ?Get in touch with QuickBooks expert for instant solution.
    Click Here to know how to fix QuickBooks Form 941 Error

    Dial on QuickBooks Error Support Phone Number +1-855-977-7463.

    ReplyDelete
  14. Nice Blog !
    QuickBooks Error 248 can occur when a user tries to run payroll for the employees. You may also encounter this issue when you are taking a backup of your company file.call us and get the best possible solutions to resolve QuickBooks Error 248.

    ReplyDelete
  15. Situs Nonton movie, film dan tv series terbaru dengan subtitle indonesia diupdate setiap hari, dari situs terpopuler nonton disini link
    di bawah ini
    layarkaca21
    bioskopkeren
    daymovie
    terbit21
    boomthis

    Situs judi online terpercaya
    http://199.188.201.133
    http://162.213.251.13
    bonekaqq
    indoqq99
    sahabatqq

    ReplyDelete
  16. I want to to thank you for this good read!! I definitely enjoyed every little bit of it. I’ve got you book-marked to look at new stuff you post. 토토사이트

    ReplyDelete
  17. really like reading through a post that can make people think. Also, many thanks for permitting me to comment!
    경마사이트
    경마

    ReplyDelete
  18. Good to be going to your blog once more, it has been months for me. Nicely this article that ive been waited for so long. 바카라사이트

    ReplyDelete
  19. Hello, I enjoy reading all of your article post. I like to write a little comment to support you.
    토토사이트

    ReplyDelete
  20. Hello Dear, are you really visiting this web site regularly, if so after that you will definitely obtain pleasant experience. 경마사이트


    ReplyDelete
  21. I absolutely love this site. That’s exactly what I’ve been looking for 바둑이사이트넷

    ReplyDelete
  22. Perfectly written articles, Really enjoyed reading through. Please visit my web site. 온라인카지노

    ReplyDelete
  23. This is the right site for everyone whoike to find out about this topic. 카지노사이트탑

    ReplyDelete
  24. Bandicam crack is a lightweight screen recorder for Windows that can catch anything on your PC screen as a great video.I has been moreover deferential. exact play-success! Bandicam Serial Key

    ReplyDelete
  25. Took me technology to entre all of the observations, however I absolutely loved the article. It proved to be Very cordial to me and i am unconditional to all the commenters here! Its constantly clean whilst you can't on your own be informed, however with entertained! Avast Driver Updater Activation Code

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. As mentioned in our mission statement, CEA Aviation is committed to providing world-class training facilities for future commercial pilots. It is one of India's <a href="https://www.ceaaviation.org/delhi.php'>DGCA Ground Classes in Delhi</a> because it provides classes in the smallest groups possible, guaranteeing that each student receives personalised attention.Strong Ground Pilot Training is necessary for a successful takeoff, according to a qualified pilot.At this stage, the Pilot Ground School begins to have an influence.

    ReplyDelete
  28. wow what is this really? Why aren't you doing this now? I think it's so awesome and awesome I have to 대전출장아로마
    대구출장아로마
    부산출장아로마
    울산출장아로마
    서울출장아로마 share this with my friends and my son and wife right now I feel like I found an oasis in the desert Thank you so much for finding your site.

    ReplyDelete
  29. This comment has been removed by the author.

    ReplyDelete
  30. اكبر شركة اثاث مكتبي شركة ستار وود وتقوم بتصنيع أُثاث بناءاً علي رغبة كل عميل في مصانعها وفقاً لكتالوجات معتمدة لاحدث التصاميم العالمية  وتقدم أسعار مناسبة للغاية بالرغم من جودة وكفائة الخامات المصنوع منها الاثاث.

    ReplyDelete
  31. Get fast turn. Ten employee level.<a href="https://tinyurl

    ReplyDelete
  32. Amazing blog! I would like to thank you for the efforts you made to write this awesome blog
    Selenium training in KPHB

    ReplyDelete