QUERY syntax using cell reference
Copied from Web Applications: =QUERY(Responses!B1:I, “Select B where G contains ‘”&$B1&”‘”)
google-sheets
Faster way to find the first empty row in a Google Sheet column
This question has now had more than 12K views – so it’s time for an update, as the performance characteristics of New Sheets are different than when Serge ran his initial tests. Good news: performance is much better across the board! Fastest: As in the first test, reading the sheet’s data just once, then operating … Read more
How to format a duration as HH:mm?
There is no need to use formulas for that, you can define your own custom formats. Just go to Format -> Number -> More formats -> More date and time formats. It will open a window with several date and time formats to choose from. You can define your own as well, using the upper … Read more
Google Spreadsheet Formula to Use Current Row in Function
If you copy the formula you quoted into another row, or fill it down using Ctrl+D, it will automatically change the reference to the row you are copying/filling to. This is called a “relative reference”. If you use the formula =PRODUCT($A$1:$B$1), this is called an “absolute reference”, which will not change if copied elsewhere. You … Read more
Iterate over range, append string to each
You can try something like this: // // helper `forEachRangeCell` function // function forEachRangeCell(range, f) { const numRows = range.getNumRows(); const numCols = range.getNumColumns(); for (let i = 1; i <= numCols; i++) { for (let j = 1; j <= numRows; j++) { const cell = range.getCell(j, i) f(cell) } } } // // … Read more
How to do I locate a Google SpreadSheet ID?
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”.
Count number of cells with any value (string or number) in a column in Google Docs Spreadsheet
In the cell you want your result to appear, use the following formula: =COUNTIF(A1:A200,”<>”) That will count all cells which have a value and ignore all empty cells in the range of A1 to A200.
How do you add UI inside cells in a google spreadsheet using app script?
The apps UI only works for panels. The best you can do is to draw a button yourself and put that into your spreadsheet. Than you can add a macro to it. Go into “Insert > Drawing…”, Draw a button and add it to the spreadsheet. Than click it and click “assign Macro…”, then insert … Read more
Getting Google Spreadsheet CSV into A Pandas Dataframe
Seems to work for me without the StringIO: test = pd.read_csv(‘https://docs.google.com/spreadsheets/d/’ + ‘0Ak1ecr7i0wotdGJmTURJRnZLYlV3M2daNTRubTdwTXc’ + ‘/export?gid=0&format=csv’, # Set first column as rownames in data frame index_col=0, # Parse column values to datetime parse_dates=[‘Quradate’] ) test.head(5) # Same result as @TomAugspurger BTW, including the ?gid= enables importing different sheets, find the gid in the URL.
How do I access (read, write) Google Sheets spreadsheets with Python?
(Jun-Dec 2016) Most answers here are now out-of-date as: 1) GData APIs are the previous generation of Google APIs, and that’s why it was hard for @Josh Brown to find that old GData Docs API documentation. While not all GData APIs have been deprecated, all newer Google APIs do not use the Google Data protocol; … Read more