Format entire row if a particular cell in the row is not empty
For illustration assuming “whole row” is ColumnsA:Z inclusive, please try Format – Conditional formatting…, Custom formula is: =$C1<>”” with formatting of your choice and Range: A:Z.
google-sheets
External API call in Google Spreedsheet is possible?
There’s a way to make API calls and have the results go into a spreadsheet – the only way I know to do it is create/open the target spreadsheet, go to tools and then Script editor, and use this as a bound script: function Maestro() { var ss = SpreadsheetApp.getActiveSpreadsheet(); //get active spreadsheet (bound to … Read more
Generate all possible combinations for Columns(cross join or Cartesian product)
in post-pandemic new world we can solve this with: =INDEX(FLATTEN(A2:A3&” “&TRANSPOSE(B2:B4))) to account for future expansion we can do: =INDEX(FLATTEN(FILTER(A2:A; A2:A<>””)&” “&TRANSPOSE(FILTER(B2:B; B2:B<>””)))) for 3 columns: =INDEX(FLATTEN(FLATTEN( FILTER(A2:A; A2:A<>””)&” “&TRANSPOSE( FILTER(B2:B; B2:B<>””)))&” “&TRANSPOSE( FILTER(C2:C; C2:C<>””)))) 4 columns: =INDEX(FLATTEN(FLATTEN(FLATTEN( FILTER(A2:A; A2:A<>””)&” “&TRANSPOSE( FILTER(B2:B; B2:B<>””)))&” “&TRANSPOSE( FILTER(C2:C; C2:C<>””)))&” “&TRANSPOSE( FILTER(D2:D; D2:D<>””)))) for more see: https://stackoverflow.com/a/74160711/5632629
Google Sheets: how to create dropdown list using data from another sheet
Getting the items from another workbook, as opposed to another sheet in the same workbook is similar. It’s a two-step process. First, you need to import the data you want to use for the validation items into the workbook where you want to make use of it, then connect it up as described in @uselink126’s … Read more
Automatic colouring of a specific value wherever found in a table [closed]
In the dropdown menu, Format->Conditional formatting… Then set your rules and your color. You can select multiple cells and do this also. Edit: That is the extent of what you can do with coloring. You can maybe find a complex formula to find the minimum of the cells, and then if that matches what is … Read more
GoogleFinance often returns #N/A and internal error messages while getting stock quotes
you can either use alternative to GOOGLEFINANCE (depends on what exactly are you up to) or if you want to stick with it you can wrap it into IFERROR: =IFERROR(GOOGLEFINANCE(your_formula_here), GOOGLEFINANCE(same_formula_here)) or even: =IFERROR(IFERROR( GOOGLEFINANCE(your_formula_here), GOOGLEFINANCE(same_formula_here)), GOOGLEFINANCE(same_formula_here))
Google sheet embed URL documentation
Here are some of the parameters I found for Google Docs (thanks goes to Joel http://obstruction.tumblr.com/post/60784440737/google-docs-url-parameters-rm-minimal-rm-full): Google Docs URL parameters: rm=minimal rm=full rm=embedded rm=demo rm=(render mode) ui=2 (select the interface version) chrome=false (full screen mode) frameborder=(size of border) q=(Whatever) Search Query gid=24 (Which sheet you want to display) widget=false single=true range=A2:AA26 Output=html format=(export spreadsheet) format=xlsx … Read more
Google sheets duration to seconds
To convert duration to an integer expressing the number of seconds, use a formula such as =value(A1*24*3600) Time values are recorded so that 1 is one day. Multiplying by 24 (hours/day) and 3600 (seconds/hour) converts that to seconds. Then value makes it a number rather than duration. Old answer, about formatting only. You don’t need … Read more
How to take only certain columns from a FILTER result?
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)
How to Force New Google Spreadsheets to refresh and recalculate?
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