Tag Info

New answers tagged

-1

maybe it work for you. https://github.com/lightswitch05/table-to-json/ its using jQuery plugin to serialize HTML tables into javascript objects.


0

The issue here is that Google Apps Script (used to create the Power Tool add-on) is "blind" to filtered rows. In other words, when you select a range to apply the tool, you are actually selecting the entire range, including the ones that have been filtered out. A clunky workaround would be to select the relevant range while in a filtered view, copy it, ...


1

If I'm understanding correctly: =FILTER(A2:D3,A1:D1=A5)


1

The syntax and effect of CHOOSE is exactly the same for Google Spreadsheets as for Excel. However, I suspect your locale for the sheet in which your formulae are not working has been set to Spain. In that case (and indeed for certain other locales) the required delimiter for the parameters is ; rather than ,. Hence I believe @Jacob Jan Tuinstra’s solution ...


1

Both formula's work as intended, see screenshot. Screenshot Note As mentioned in my comment, the CHOOSE formula is present in Excel and Google Sheets. You need to use a semicolon in stead of a comma. Example I've created an example file for you: CHOOSE Reference Locale Example Excel vs. Google Spreadsheets (old)


0

With a trigger cell in A5 and in say A6: ="select "&A5 =query($A$1:$D$3,$A$6,0) might suit. This might be placed almost anywhere and produce the same results, with such results controlled by the value (letter) chosen in A5. Perhaps parameterised rather than dynamic.


0

not pretty but it works. =indirect(SUBSTITUTE(ADDRESS(1,COLUMN(hlookup(A5,A1:D3,2,false)),4),"1","")&"2:"&SUBSTITUTE(ADDRESS(1,COLUMN(hlookup(A5,A1:D3,2,false)),4),"1","")&"3") A5 being the place where you enter the letter


1

ASSUMING the weekdays will always be abbreviated exactly as shown, are the entire cell content (not part of a longer string) and data is in ColumnA, the following may serve: =ArrayFormula(sum(countif(A:A,{"Mon","Tues","Wed","Sun"}))) with the insertion of other search strings to suit.


0

I came here to look for an answer too for the same situation (calling data from external resource), but have since worked out an alternate method that works so I thought I'd share. This is by no means the technically savvy way to do it, but if you delete the cell, then undo, it will reload. Works a treat.


1

This will provide the sum of the fractions (note this will only work for improper rather than mixed fractions, and returns a decimal value): (I will edit the answer if I have interpreted incorrectly or more information is provided.)


1

Is there a way to make this function also run in published document mode? The short answer is, unfortunately, no. The published version of a spreadsheet has many functional limitations, and this is one of them.


0

As I understand your requirement: =SUM(INDIRECT("a1:"&B1))/COUNT(INDIRECT("a1:"&B1)) is possible, but does not seem advisable.


0

From the Google Docs forum: Select the range of cells. Then use Ctrl+C (copy; this should put the cells and their formulas in the paste buffer). Now drag (hover over the edge of the selection until the mouse cursor changes into a hand; now you can drag) the selected cells to the new position. Go back to the original top-left position of the copied cells ...


0

Are there a fixed number of comma-separated lists that would be valid? If so, you could us the Data > Validation option to set up so that the lists are always in the same order, so reporting is more meaningful. Alternatively, or perhaps even as well as that, you could use additional helper columns to calculate Boolean values based on presence of a given ...


0

Using two columns, you can do this: =TEXT(A1,"0000")


0

This is another way of doing it. Formula =QUERY(TIMESTAMPS!A:B, "SELECT A, B ORDER BY A DESC LIMIT 10") Explained The QUERY formula needs the data from the timestamps sheet, for it to process. The result must contain column A and B, of which column A is sorted descendingly (new to old). Finally, the result is limited to 10 entries (rows). Screenshot ...


1

This duplicates data (or since the source for the query is itself imported, even triplicates) but does seem to provide automatic resorting as data input changes: =query(A1:B5,"Select * order by B desc")


0

The easiest way may be to ensure that the column is formatted as Plain text before any data entry. Automatic conversions, say from 0123 to 123, take place on data entry and are "once off" - applying Text format to 123 does not revert to 0123 even if that was what was entered. (Google does not remember 0123 as such, even in Revision History.)


1

Assuming 4 is in D2, please try: =sum(B2:B4)+SUMIF(B2:B4,"",$A2:$A4) in B5 and copy it across to suit.


0

I guess we may never know, but perhaps what was required was something like: =QUERY(A2:D7,"Select * WHERE D contains 'Arts & Entertainment' or D contains 'Business, News & Finance' or D contains 'Tech' ")


0

Try this code: function AddRow() { var ssheet = SpreadsheetApp.getActiveSpreadsheet(); //gets the spreadsheet which it's bound to var sheet = ssheet.getSheets(); //gets all sheets in this spreadsheet for (var i = 0; i < sheet.length; i++) //browses all the sheets, untill "i" is larger than the number of sheets ...


0

Google Sheets now have a duration formatting option. Select: Format -> Number -> Duration.


0

With the gradual roll out of new Google Sheets from very late 2013 onwards the default text wrapping behaviour changed from “on” to “off” (which suits me). So what may have been taken for granted now requires a conscious choice, though only to select the range to which wrapping is to be applied and clicking a button: The button toggles.


1

Another option: =SORT(FILTER(Sheet1!A2:B,ROW(Sheet1!A2:A)<MATCH("Size",Sheet1!A:A,0)),2,1)


1

The handy little cheat: =MOD(B1-A1,1) but more intuitive: =IF((B1-A1)>=0,B1-A1,1+B1-A1) but that can be simplified to: =(B1-A1)+((B1-A1)<0)


1

QUERY expects dates to be in yyyy/MM/dd format: =QUERY('Calculated Pay [view]'!B1:B1000,"select * where B >= date '"&TEXT(A2,"yyyy-MM-dd")&"' and B <= date '"&TEXT(B2,"yyyy-MM-dd")&"'")


1

I believe the question is: how can I provide a product of the sums of columns A and B, from the current row down to the end of the sheet, and apply this automatically down the column with an array formula? =ArrayFormula(SUMIF(ROW(A:A);">="&ROW(A:A);A:A)*SUMIF(ROW(B:B);">="&ROW(B:B);B:B)) As per OP request, this will populate a header, and ...


1

Part of my answer would be: this is a known bug (newly received form submissions do not respect the format of the columns that they are landing in, as they did in the old version of sheets). Depending on what you are doing with the data afterwards, another option would be to reproduce the form submissions on another sheet, with the correct format. For ...


1

You can use PRODUCT to provide the product of a range of values, eg: =PRODUCT(C2:C201) And if you wanted the compound interest effective at each month, then in row 2: =PRODUCT(C$2:C2) and fill down as far as required.


0

A long winded way of saying "I don't know how" coupled with "seems may not be possible" (but a real shame): The best (but feeble) I can manage is to expect input (and thus also output) one digit at a time using a Scale of 0 to 9 for each digit.


0

It's extremely unlikely that this is the spreadsheet changing things "on it's own" - highly likely that it's operating as designed. Firstly, how did you make the exact copy in Sheet2? If you used an =(cell-ref) formula in every single cell, and then had an external process (eg your Form) add some more rows to the bottom of the existing data table, then the ...


0

Please try a Custom formula is of : =weekday(A1)=2 to the relevant column (ie adjust A to suit).


1

Might be easier to add a column C with a formula such as: =if(and(B1>eomonth(Sheet2!$A$2,-1),B1<=eomonth(Sheet2!$A$2,0)),"s",) and apply a simplified query such as: =query('Calculated Pay [view]'!B1:C1000,"Select B where C ='s' ")


0

With the below mentioned formulae, you can accomplish using double quotes. Formula 1 =SUM(A1:A3) & " ""lbs""" Formula 2 =CONCAT(SUM(A1:A3)," ""lbs""") Formula 3 =CONCATENATE(SUM(A1:A3)," ", """lbs""") Example I've created an example file for you: How can I append a string to a formula result? References ...


1

Instead of appending lbs to your formula, you might get what you want by formatting the cell, using a custom format: Select the cell containing the formula From the Format menu, select Number → More formats → Custom number format In the dialog boks, enter 0.00 "lbs" (note the quotes around lbs) Click Apply The cell should now display the ...


1

You might try pairs of single quotes inside double quotes: # "''lbs''" with or without the space.


0

The to_text and Char functions are what you need - like this: Set A5 = A1 + A2 + A3 and then use =to_text(A5) & char(34) & "LBS" & char(34) (It's good practise to keep your calculations and text-output of the results in separate formulae - it makes debugging easier.) There's an example spreadsheet here.


2

The column data 000000, etc. is being seen as a number and consequently the leading zeros are being removed. Unfortunately, AFAIK, there is no way in Spreadsheet to format a number with leading zeros. In the old Google Spreadsheet there was no way to format a number with leading zeros. However, as mentioned by pnuts in comments, in the new Google Sheets you ...


-2

Yes there is - cmd Y I just discovered this, and tested it on text input, text formatting and line insertion.


0

If you are using the new (Q2 2014) spreadsheets you can try the Recalculation option found under [File] » [Spreadsheet Settings ...] I believe it will work in your case. It depends what you mean by "on an alternate page". If you mean a 2nd sheet in the same spreadsheet you may be in luck. If you mean an alternate workbook, I think you lose. In our case, ...


0

To really fix the problem, you need to store the date as well as the time value in the cells, and to format the cells with calculations as Number > Duration. I've put some examples in this spreadsheet: https://docs.google.com/spreadsheets/d/1HgcCmhUI56msblvsIQ5sHt7Jz-Q5QldUF7ZQCTLrt2w/edit?usp=sharing


1

Truly horrible, but I think the formatting is an issue. Please try: =if(and(right(A1,2)="PM",right(B1,2)="AM"),B1-A1+1,B1-A1) (with the results formatted as Time or Duration).


2

Exactly what I think you want is not possible. The choice is to compromise on automation (eg filter ColumnG for "Yes" and format to suit) or to forego italic and strikethrough as the choice of fonts in conditional formatting at present is only for text colour. You need to decide whether the font is to be grey or the cell background colour is to be grey (or ...


1

There is an unfixed bug in Google Docs spreadsheets, which apparently causes both REGEXREPLACE and SPLIT to yield only partial output from within an ARRAYFORMULA parenthesis. As such, neither of the previously given answers are easily adaptable into formulas that can be passed an array of cells. Here is a workaround, using Google Apps Script to create a ...


1

With the following formula you can construct a month table, containing all days of the month. Formula =ARRAYFORMULA( DATE( YEAR(NOW()), // first A1, // second ROW( // ...


0

The default wrap setting in new Google Sheets appear to be the opposite of that in the previous version. Switch to new Google Sheets.


0

I would go about doing it like so. Code function myValues(range) { var output = []; for(var i = 0, iLen=range.length; i < iLen; i++) { output.push(checkValue(range[i][0])); } return output; } function checkValue(value) { var check; if(value >= 1 && value <= 71) { check = 1; } else if (value == 72) { check = '1 ...



Top 50 recent answers are included