When you are QUERYing a computed array, you need to use the Colx notation rather than column letters: =QUERY(FILTER(B:D,D:D>=2),”select Col2, Col1″) which incidentally can be achieved by just using the QUERY: =QUERY(B:D,”select C, B where D >= 2″) or just FILTER: =FILTER({C:C,B:B},D:D>=2)
Found these docu on the google docu pages: row — int — top row of the range column — int— leftmost column of the range optNumRows — int — number of rows in the range. optNumColumns — int — number of columns in the range In your example, you would get (if you picked the … Read more
File -> Spreadsheet Settings -> (Tab) Calculation -> Recalculation (3 Options) On change On change and every minute On change and every hour This affects how often NOW, TODAY, RAND, and RANDBETWEEN are updated. but.. .. it updates only if the functions’ arguments (their ranges, cells) are affected by that. from my example I … Read more
Update 2019-03-01: The best solution is now =IFNA(VLOOKUP(…), 0). See this other answer. You can use the following formula. It will replace any #N/A value possibly returned by VLOOKUP(…) with 0. =SUMIF(VLOOKUP(…),”<>#N/A”) How it works: This uses SUMIF() with only one value VLOOKUP(…) to sum up. So the result is that one value, but … Read more
Found this deep inside the Apps Script API reference. A spreadsheet ID can be extracted from its URL. For example, the spreadsheet ID in the URL https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0 is “abc1234567”.
Yes, by using the INDIRECT function: A1: Sales Report – WB 10.06 Then you can use: =INDIRECT(“‘”&A1&”‘!B3”)
Short answer There is no built-in function but you could build a custom function. Example /** * Converts a datetime string to a datetime string in a targe timezone. * *@param {“October 29, 2016 1:00 PM CDT”} datetimeString Date, time and timezone. *@param {“Timezone”} timeZone Target timezone *@param {“YYYY-MM-dd hh:mm a z”} Datetime format *@customfunction … Read more
No need to extact the value to determine if the cell is empty. Google Spreadsheet API already has a method for this: Range – isBlank method var cell = r.getCell(rws-1, 10); if (cell.isBlank()) { cell.setValue(“foo”); }
One option: =COUNTIF(B:B; “Mammal”) + COUNTIF(B:B; “Bird”) According to the documentation: Notes COUNTIF can only perform conditional counts with a single criterion. To use multiple criteria, use COUNTIFS or the database functions DCOUNT or DCOUNTA. COUNTIFS: This function is only available in the new Google Sheets. Example: =DCOUNTA(B:B; 2; {“Type”; “Mammal”; “Bird”})
You can start with a google sheet set up with a main page and drop down source page like shown below. You can set up the first column drop down through the normal Data > Validations menu prompts. Main Page Drop Down Source Page After that, you need to set up a script with the … Read more