It's not really Magic, but worth the time to read

This turned out to be a lot longer that I expected. Hopefully that won’t prevent people from reading it.

A little bit about spreadsheet magic but more about how to post example Google Functions and or Google Script in a community post.

First for the spreadsheet magic. It’s not really magic, but all Google spreadsheets have settings. It can be found at File/Spreadsheet Settings. There is an option for setting the Locale. That setting says “This affects formatting details such as functions, dates, and currency.” The functions part I want to talk about.

So, if you see some formula that someone suggested you try and you copy it to your spreadsheet, take note of the use of commas and/or semicolons. Because in the United States, and other countries, the parameters of any given function are separated by commas. Where in other Locales they use semicolons. In most cases, Google is smart enough to automatically make the change for you but I have seen cases where it doesn’t. When I post example formulas, because I’m in the US, you will see commas. So if you do get a message that says something like Error in analysing the formula, and you copied and pasted the example from a post here, check for those commas and semicolons.

Second. When you do post formulas or any type of code there is a syntax that you can use to make it way easier to read and allow people to copy it to use. First off backticks and single quotes are two different things. The keyboard key for a backtick is usually found on the upper left hand part of a keyboard (the shift of the backtick is a tilde ~ ). When I first started posting to sites like this I had no idea what it was ever used for :slight_smile: Another reason to use this technique is to prevent those textural “quotes” to throw you off. The quotes around the word “quote” are not the same quotes used in your formulas to depict strings. I’ve spent more time than I’d like to admit in wondering why something I copied didn’t work, only to find out the difference was those darn quotes.
Here is the way you should post either a formula or scripting code:

Use 3 backticks on one line by itself.
< put your code here >
3 more backticks here.

Here is an example of what I mean. Here is the difference between putting a few lines of formula in the text of the message and using the 3 backtick way.

for (var i = 0; i < calEvents.length; i++){
for (var j = 0; j < calEvents[i].length; j++){
allEvents.push([calEvents[i][j].getTitle(), calEvents[i][j].getStartTime(), calEvents[i][j].getEndTime()]);
}
}

  for (var i = 0; i < calEvents.length; i++){
    for (var j = 0; j < calEvents[i].length; j++){
      allEvents.push([calEvents[i][j].getTitle(), calEvents[i][j].getStartTime(), calEvents[i][j].getEndTime()]);
    }
  }

=ARRAYFORMULA(IF(ISBLANK(A2:A), “”, “your image url here”))

Vs.

=ARRAYFORMULA(IF(ISBLANK(A2:A), "", "your image url here"))

Here is an image that shows what I typed into the post to get these results.

5 Likes