Tag Info

Hot answers tagged

4

You'll want to use Range.getValues(). The following function takes a range A2:B4, and returns its pure values: function getRangeValues() { var sheet = SpreadsheetApp.getActiveSheet(); var range = sheet.getRange("A2:B4"); var values = range.getValues(); return values; }; To use the script, you must first install it: In your spreadsheet, click ...


3

In N3 I entered this formula: =ArrayFormula({query(query({A3:D,value(B3:B)}, "select Col1, sum(Col5), count(Col4), sum(Col3) where Col1<>'' group by Col1 format sum(Col5) 'h:mm:ss'"),"select* offset 1",0), vlookup(query(query(query(A4:D, "select D, A, sum(C) group by A, D"), "select max(Col3), Col2 group by Col2 label max(Col3)''"), "select ...


3

I was able to reduce the formula to the one below. Formula =ARRAYFORMULA( IFERROR( E3:E6 + VLOOKUP(G3:G6, temperature, 2, 1) + VLOOKUP(H3:H6, alkalinity, 2, 1) + VLOOKUP(I3:I6, calcium, 2, 1) - 12.1, "Missing values" ) ) copy / paste =ARRAYFORMULA(IFERROR(E3:E6 + VLOOKUP(G3:G6, temperature, 2, 1) + VLOOKUP(H3:H6, ...


3

Filter() should do that. Let's say the sheet with the 'source data' is called Sheet1, then on the second sheet (where you want the level1 names), in cell A1 try: =filter(Sheet1!A:A; len(Sheet1!A:A)) and repeat for all the other sheets and ranges..


3

In addition to previous posts, I think I would go for a query function. Although not that short , it will eliminate the need 1) to drag the current formula down (newly added rows will 'automatically be added to the output of the query) and 2) to have the unique formula to populate col A on the count sheet. =query(Incidents!B2:C; "select B, count(C) where ...


2

I suspect that a third-party application or browser plug-in is interfering with the Windows clipboard and causing your problem. Whatever the issue is, you may be able to quickly accomplish your copy/paste goal by using the Web Clipboard tool inside of Google Docs. (Look for the collapsible section called "The web clipboard tool".) The directions are not ...


2

UNTESTED. Please try: =countif(array_constrain(filter(C:C,B:B="Scheduled"),20,1),E2) and cop down where E2, E3 and E4 contain Completed, In Progress and Pending respectively.


2

Please try: =QUERY('Form Responses 1'!A:N,"SELECT C,E,G,H where G is not null or H is not null order by C,E")


2

You can wrap the ISEMPTY() with a NOT() it basically switches what ever is in it to the opposite of its original result. =NOT(ISEMPTY(A2)) Its checking if A2 is not empty. If it has something in A2 it will give a True result. NOT - Docs editors Help


2

Doing calculations on date/times is tricky, because there are a lot of other parameters that you usually don't think about. Are the two times in the same time zone, for example? Or on which date are the two times? I see that you want 22 hours + 22 hours to be displayed as 1 day + 20 hours, which sounds reasonable enough, but that assumption fails as soon as ...


2

pnuts gave the answer that I needed in his comment to my question. I dont know how to select it as an answer so. I'll just quote him. Try deleting the 'spare' rows at the bottom of WaterTests. – pnuts


2

Please format A3 as Duration and ColumnF as h:mm:ss.


2

Try formatting Water Tests as Number, Number.


2

You need to specify a condition, not just a value, for the FILTER function. So: =FILTER(A2:A6, B2:B6 = MAX(B2:B6)) I have set up a demonstration spreadsheet for this, feel free to have a look or copy it.


1

I'll stick my neck out (yet!) again and say "No" - based on the deduction that if such existed then most of the many questions asking for a date stamp to be applied when a cell changes would not have been posted. However, that may be a "workaround" for you, ie take one of those scripts and apply it.


1

I would suggest this if you are simply copying to a new sheet. =query('Form Responses'!A:G, "SELECT * ") Syntax QUERY(data, query, [headers]) The 'Form Responses'!A:G, is the data range of the source sheet that you would like to draw data from. The "Select * ", is the query and will select everything within the range defined previously in the same ...


1

As stated in my comment, doing this with =importXML and XPath seems tricky. But doing it programmatically, using the XmlService of Google Apps Script, looks easier. I have written the following: function parseTvRageXml(url) { var result = []; var xml = UrlFetchApp.fetch(url).getContentText(); var document = XmlService.parse(xml); var root = ...


1

It is not completely clear how your data is structured. In case cell A1 = x y z and cell A2 = a b c, and values are separated with " " , try: =transpose(split(query(A1:A,,50000)," ")) In case A1:C1 = x y z and A2:C2 = a b c, try: =ArrayFormula(transpose(split(concatenate(A1:C&char(9)),char(9)))) And in case both don't work, please consider sharing ...


1

Sorry, regrettable but such appears not to exist in 'native' sheets. There are, understandably, complaints about this eg. I have had a look through Add-ons and see nothing promising there that might help.


1

Instead of just using the ranges, you will have to add the sheet name.. e.g: =ArrayFormula({query(query({'SHEETNAME'!A3:D,value('SHEETNAME'!B3:B)}....ETC


1

Let me add my comment as an answer... :-) You should be okay just using =SUM(), because sum() simply ignores non-numeric values.


1

The link you still have, contains the ID of the form you created. Building the following URL will bring you to the original form in edit modus. https://docs.google.com/a/<yourdomainname>/forms/d/<yourformid>/edit If you select from the menu Responses(?) > View responses, then it will lead you to the corresponding Google Spreadsheet. Tried ...


1

It will depend upon what else you have in your spreadsheet. Text like "Minutes(Cardinal)" might require care. First I would have the formulae visible (View, All formulae or Ctrl+`). Then it rather depends upon the formulae, of which you have only provided a single example. A column as the example that had been copied down could be changed with simple Edit, ...


1

Somewhat guessing what you require (examples often help) if say you have a couple of sheets named: Sheet2 Sheet3 and those names in A1 and A2 of Sheet1, then a formula like: =indirect(A1&"!B2") in Sheet1 should return the value of B2 from Sheet2 and if copied down one row, the value of B2 from Sheet3.


1

Try: =sort(filter(Leaderboard1!E5:F126, len(Leaderboard1!E5:E126)),1,True)


1

There is a Google Forms add-on called Form Values which at least offers a semi-automatic way: whenever you change your answers in the spreadsheet you need to manually re-populate the choices in your answer, but at least it's rather straightforward to do.



Only top voted, non community-wiki answers of a minimum length are eligible