# Tag Info

## Hot answers tagged google-spreadsheets

5

First, I'd split the data from column A into two columns. In this case it looks like the length is consistent so it makes it easier (I can demo the other if need be). Get the left chunk with =left(A2,11) Get the right chunk with =right(A2,10) Now you have Element 1: | Value 1.1 To flip the unique elements use =transpose(unique(B2:B7)) Now you have ...

5

Here is a script that automatically converts input to upper case: function onEdit(e) { e.range.setValue(e.value.toUpperCase()); } From the spreadsheet, go Tools > Script Editor. Put the above code in the code window (replacing anything pre-filled there), and save. If you wanted to enforce upper case and single-character entries, then data validation ...

4

I believe the data in columns B:E are formatted as text rather than numbers. In Google Sheets you'd select those cells and choose Format>Number>Number. See the example here.

4

You can get rid of all comments in a sheet by duplicating the sheet, deleting the original copy, and then renaming the duplicate back to what the original was called. This preserves all values, formulas, formatting, and notes. Only the comments are gone. Similarly, you can get rid of comments in an entire spreadsheet by copying it and deleting the ...

4

TL;DR =AVERAGE(OFFSET(A:A;COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1;0)) Example Explanation First we count how many numbers are in row A with =COUNT(A:A). Cells containing text won't be counted. Our example would give us 9 From this number we subtract 7 since you want the average of the last 7 values: =COUNT(A:A)-7. This will give us a 2. ...

3

Unfortunately, you will need a conditional formatting rule for each team (not cell, though), as you can only set one fill color per rule. Assuming you want to format based on the value of an entire cell rather than a substring, then in each of your rules, set the condition to "Custom formula isâ€¦" and then enter =(\$B1="Team 1") (replacing "Team 1" with the ...

3

Select the cell you wish to paste into, then press F2 and it will allow you to paste the text in without separating the line breaks into their own cells.

3

You could make the second validation list an if/then statement dependent on the answer to the first one. =if(A1="Movies","Horror","Baseball") =if(A1="Movies","Romance","Basketball") An example is here.

3

A script cannot give a file to you (well, it could email it to you, but I think this is going too far). You'll need to use "save as csv" menu command, and since it only saves the current sheet, you'll have to create a sheet that has only those columns in it. The straightforward way is to just create a new sheet and type in cell A1 ={'My Sheet'!A:A, 'My ...

3

The simplest way is this: =if('Additional Options'!A1="yes",1,0) Assuming your value is in A1 on your target sheet. Of course, if the value there is anything else, not just "no", it'll return 0. If you want 1 and 0 only for "yes" and "no", and something else if it's not one of those values, you can nest your if statements. =if('Additional ...

3

You can use embedded arrays to join the two data sets for the purpose of plotting. For example, if your data is in columns A and B of two sheets, and has a header row, the following will join and sort the two sets: ={Sheet1!A1:B1; sort({filter(Sheet1!A2:B, len(Sheet1!A2:A)); filter(Sheet2!A2:B, len(Sheet2!A2:A))}, 1, true)} Explanation: Sheet1!A1:B1 ...

3

You are right: it can be simpler than that. Pick "Format cells if... Is equal to" and put =MAX(A\$2:A\$10), and you'll have the same result. Why dollar signs? Because without them, the references are relative. The formula that you enter is assumed to be designed for the upper left corner of the range being formatted; for all other cells in the range, it ...

3

Here is one approach: =average(indirect("D" & iferror(large(filter(row(D7:D), len(D7:D)), 7), row(D7)) & ":D")) Explanation: filter(row(D7:D), len(D7:D)) returns an array that consists of the row numbers of the nonempty entries in the given range. large(..., 7) picks the 7th largest number from this array: this is the row number where you want ...

3

The "Basic JavaScript Features" section in the official Apps Script documentation states that "Apps Script is based on JavaScript 1.6, plus a few features from 1.7 and 1.8." Therefore, it would be helpful to first learn the fundamentals of JavaScript, as the other answers have already mentioned. But! It is important to keep in mind that the Apps Script ...

3

Generally: the way to refer to "this" cell is to enter notation for the upper-left corner of the range being formatted. For example, if the range is C1:C, then C1 means "this cell". For example, formatting C1:C with custom formula =C1 < today() - 14 will format all cells in C with dates two weeks in the past. If the range you want to format ...

2

I used Matthijs's solution which worked for me. I had a hard time following along and understanding his answer though so here's the step-by-step solution: Use this template Copy both of the checkboxes on the "Function" sheet Return to your Drive file and add another sheet Name the new sheet "Function" Paste both checkboxes into cells A1 and A2 Copy the ...

2

You'll need to make a spreadsheet. Then go to tools>script editor. You can cut/paste the script below replacing all of the contents that were put in by default. You'll need to add your folder ID where it says THIS_SHOULD_BE_YOUR_FOLDER_ID (leave the quotes). Save it. Hit the play/run button You'll need to give it permission to run when asked. That ...

2

When cells contain linebreak characters, the 'Overflow', 'Wrap', and 'Clip' formatting options do not have the desired effect of limiting the height of the row. To work around this, one can create an adjacent column that uses a formula to strip out the linebreak characters, then hide the original column. This approach preserves the ability to properly ...

2

A1 notation is supported now. To center column A: SpreadsheetApp.getActiveSheet().getRange("A1:A").setHorizontalAlignment("center");

2

You could use a for loop and iterate down Column E, which is column 5. Then insert the cell values into an array. var data = s.getDataRange().getValues(); //var data = s.getRange(1,5,s.getLastRow(),1).getValues(); var emails = []; for (var row in data) { var cell_value = data[row][5]; emails.push(cell_value); } for (var e in emails) { // TODO: ...

2

You identified and solved the issue correctly: =K4 is a relative reference, so when it is applied to the range K7:K26, the following happens: K7 (being the upper left corner of the range) gets compared to K4, as stated in the formula K8 (one cell down) gets compared to K5 (one cell down) K9 (two cells down) gets compared to K6 (two cell down), and so on. ...

2

I got it to work with this: =QUERY(ImportHTML( "http://eciresults.nic.in/StatewiseU05.htm" , "table" , 9 ), "SELECT * where Col2>0",0)

2

Apply to A3 data validation rule with the custom formula =or(A2<>"X", isblank(A3)) It looks like this:

2

You can't do that with a function. You need a script. Here is an example to get you started. It assumes updated price in A1, reference price in B1, and historical record in column C. After entering this script in Script Editor, add a trigger (Menu > Resources) to run it every minute. The code is more or less self-explanatory. It assumes you are using this ...

2

At present you have var column = r.getColumn(); // Get column # from active cell which means the timestamp gets entered in the column that you edited. If you want to have a single timestamp instead, put var column = 1; // timestamp goes to Column A every time or some other number instead of 1.

2

While it's possible to insert a button (Insert > Drawing) and assign a script to it, the resulting buttons float over the sheet in their own layer, not being bound to any cell. This wouldn't work for you since you want the action to be related to a particular row of the sheet. Instead, I would use a custom item of the main menu, added by the function ...

2

Cells containing the empty string "" appear empty but are not (isblank returns FALSE for them). They get sorted ahead of nonempty strings, which is not what you want. To avoid this, replace if(isblank(B1:B), "", datevalue(B1:B)) by the simpler iferror(datevalue(B1:B)) The command iferror returns its (optional) second argument if there is an error ...

2

No, the aggregation functions available for query do not include concatenation of strings. An alternative approach, illustrated by the following example: +---+------+---------------+------+------------------------------+ | | A | B | C | D | +---+------+---------------+------+------------------------------+ | ...

2

You can't really have "variables" in a spreadsheet; it's not a programming language. The values are contained in cells and can be referred to by location, possibly relative to location of other cells. If cells get rearranged so that the relations within rows or columns are lost, that is already a problem. In your example, you want to refer to Count of ...

2

In addition, if you don't mind the timestamp to be in the last column, you can change the 'sorting formula' to =sort(ArrayFormula({transpose(split(regexreplace(join(";", 'Form Responses 1'!DD2:DD), "(\w+),\s(\w+),\s", "\$1, \$2;"),";")), VLOOKUP(TRANSPOSE(SPLIT(JOIN("/", REPT('Form Responses 1'!DI2:DI&"/", if(len('Form Responses 1'!DD2:DD), (len('Form ...

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