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
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
As you identified and Wiktor mentioned, COUNTIF only supports wildcards. There are many ways to do what you want, though. To name two: =ArrayFormula(SUM(–REGEXMATCH(E9:E13, “50-03[123][012]*”))) =COUNTA(FILTER(E9:E13, REGEXMATCH(E9:E13, “50-03[123][012]*”)))
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
I made cheeriogs for your problem. it’s works on GAS as cheerio which is jQuery-like api. You can do that like this. const content = UrlFetchApp.fetch(‘https://example.co/’).getContentText(); const $ = Cheerio.load(content); Logger.log($(‘p .blah’).first().text()); // blah blah blah … See also https://github.com/asciian/cheeriogs
I wrote a simple general custom function, which is 100% reusable you can unpivot / reverse pivot a table of any size. In your case you could use it like this: =unpivot(A1:D4,1,1,”customer”,”sales”) So you can use it just like any built-in array function in spreadsheet. Please see here 2 examples: https://docs.google.com/spreadsheets/d/12TBoX2UI_Yu2MA2ZN3p9f-cZsySE4et1slwpgjZbSzw/edit#gid=422214765 The following is the … Read more
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
use the JavaScript Date() object. There are a number of ways to get the time, date, timestamps, etc from the object. (Reference) function myFunction() { var d = new Date(); var timeStamp = d.getTime(); // Number of ms since Jan 1, 1970 // OR: var currentTime = d.toLocaleTimeString(); // “12:35 PM”, for instance }
You may replace all non-digit characters using the \D+ regex and an empty string replacement with =REGEXREPLACE(A11,”\D+”, “”) or with casting it to a number: =VALUE(REGEXREPLACE(A11,”\D+”, “”))
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))