Dev Stack Source

Answer : When you create a menu item with .addItem('Nathaniel MacIver', menuItem2('nm@emailaddress.com')) the function menuItem2 is called with the parameter 'nm@emailaddress.com'. This results in the alert that you see. The return value of the function is undefined (as you don't return anything from it). So you end up with the same menu item as if it was .addItem('Nathaniel MacIver', undefined) which clearly isn't going to do anything. The method addItem takes only a function name, it does not allow for passing parameters to that function. To do what you want, you'll need separate functions for each person, each with an email hardcoded inside that function. Although you can't pass functions that invoke with a variable directly through the .addItem() method, because it only accepts strings, you can take a couple of extra steps (extra steps relative to what you were trying to do) to dynamically create functions that are pre-s...

Answer : You can pass "<>" (including the quotes) as the parameter for criteria . This basically says, as long as its not empty/blank, count it. I believe this is what you want. =COUNTIF(A1:A10, "<>") Otherwise you can use CountA as Scott suggests COUNTIF function will only count cells that contain numbers in your specified range. COUNTA(range) will count all values in the list of arguments. Text entries and numbers are counted, even when they contain an empty string of length 0. Example: Function in A7 =COUNTA(A1:A6) Range: A1 a A2 b A3 banana A4 42 A5 A6 A7 4 -> result Google spreadsheet function list contains a list of all available functions for future reference https://support.google.com/drive/table/25273?hl=en. The criterium should be "?*" and not "<>" because the latter will also count formulas that contain empty results, like "" So the simplest formula would be =COUNTIF(Range,"?*")

Answer : Note: when it says "B5" in the explanation below, it actually means "B{current_row}", so for C5 it's B5, for C6 it's B6 and so on. Unless you specify B B B 5 - then you refer to one specific cell. This is supported in Google Sheets as of 2015: https://support.google.com/drive/answer/78413#formulas In your case, you will need to set conditional formatting on B5. Use the " Custom formula is " option and set it to =B5>0.8*C5 . set the " Range " option to B5 . set the desired color You can repeat this process to add more colors for the background or text or a color scale. Even better, make a single rule apply to all rows by using ranges in " Range ". Example assuming the first row is a header: On B2 conditional formatting, set the " Custom formula is " to =B2>0.8*C2 . set the " Range " option to B2:B . set the desired color Will be like the previous example but works on all rows, not just row 5...

Answer : API key is not enough. You're trying to use spreadsheets.values.append which requires OAuth authorization: Authorization Requires one of the following OAuth scopes: https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file https://www.googleapis.com/auth/spreadsheets For more information, see the [Auth Guide](https://developers.google.com/identity/protocols/OAuth2). Note it says OAuth. You could use the format as below: https://docs.google.com/spreadsheets/d/{sheetID}/export?format=csv Make a URLConnection to this URL, after replacing the sheetID with your public sheet and read the csv file as you would normally do in your programming language of choice. Please note that this is only true for Readable Public Google spreadsheets, and is a bit of a hack when you don't necessarily need to do the Credentials jig and jive In my case I need to publish content from a Google Spreadsheet to a web page. My workaround consists in u...

Answer : Short answer A custom function can't be used as a part of a custom formula in the Google Sheets' conditional formatting built-in user interface. Explanation In Google Sheets, a custom function is like spreadsheet built-in functions but written by the user using the Google Apps Script editor. They can't modify other objects, only return one or multiple values. By the other hand, a custom function only is calculated when it's added to a cell or when at least one of it's parameters changes. They can't use non-deterministic functions as parameters like NOW(), TODAY(), RAND() and RANDBETWEEN(). Test In order to test the statement in the short answer, I created a did the following: Create a simple custom function function two() { return 2; } Add the custom function as part of a custom formula in the conditional formatting side panel =two()=2 Result: Nothing changed. References Custom Functions in Google Sheets I have also found that custom functions c...