Tag Info

Hot answers tagged

6

The possible solution without an additional column is: =FILTER(UNIQUE(A2:A7), ARRAYFORMULA( COUNTIF(A2:A7,UNIQUE(A2:A7))>1 )) Please, see prepared example here: Find Duplicates


4

Add a new select with a new id, and assign a set of values the same way. This should give a good idea how to fill multiple drop-downs from one data array where each column sets the drop-down value. function onSuccess(values) { //values is a 2d array. var opt, dropDown; for(i = 0;i < values.length; i++){ dropDown_one = ...


4

You need to add a helper column within the QUERY formula, like so: =QUERY( {A1:B6, A1:A6}, "SELECT Col1, COUNT(Col3) WHERE Col2 = 'SubCatA' GROUP BY Col1 LABEL COUNT(Col3) ''" ) Adding another QUERY formula will help to remove the helper column: =QUERY( QUERY( {A1:B6, A1:A6}, "SELECT Col1, COUNT(Col3) WHERE Col2 = 'SubCatA' GROUP BY ...


3

If we assume that a value of 30 should return 0, then the following formula could be entered into cell C6: =CEILING(C5/30) - 1 It divides the value found in C5 by 30, rounds it up to the nearest integer (the CEILING function), and subtracts 1. This table shows the result of a few selected input values: Column C Row 5 => | 29 | 30 | 31 | ... | ...


3

Simple Triggers, such as onOpen, are subject to certain restrictions: They cannot access services that require authorization. They can modify the file they are bound to, but cannot access other files because that would require authorization. They may or may not be able to determine the identity of the current user, depending on a complex set of ...


3

One can have alternating coloring for filtered views that involve only hiding some rows. This can be done by using the command subtotal which ignores the cells hidden due to a filter. You should have a column, for example A, which is non-blank in every row that contains data. Then apply conditional formatting to A1:Z (for example) with custom formula ...


3

See if this helps ? =ArrayFormula(query({A1:C,C1:C}, "select Col1, Col2, Col3, count(Col4) where Col1 <> ''group by Col1, Col2, Col3 label count(Col4)''"))


3

You can achieve your desired results by putting a tilde, ~ in front of the asterisk as an escape character, if you put the conditional formatting rules in the order listed below. First, create the one for orange when three asterisks occur using text contains and then specifying ~*~*~*. Select custom to pick an orange background. Then create the one for two ...


3

It appears you were trying to enter 'SUM of Transations' and so on into the formula. But "SUM of" if not a part of the name, it's just an indication of how the column was summarized. The following works in the calculated formulas: =Transactions/Sessions Since those columns are summarized using SUM, you will get the sum of transactions divided by the sum ...


2

Guide to Adding Custom Number Formats Click on Format Go to Number > More Formats > Custom number formats Paste custom number format (see below) Click Apply . . Different Custom Number Formats Output: ###-###-#### Syntax: ##########000"-"000"-"0000 Output: (###) ###-#### Syntax: ##########"("000") "000"-"0000 Output: 1-###-###-#### ...


2

You can combine a couple of the approaches suggested above by creating a custom function: function VERTICAL_TEXT(text) { var vert_text = text.replace(/(.)/g,"$1\n"); return vert_text; } and then using it in the cell: =VERTICAL_TEXT("cell contents")


2

Here is a script-based solution which allows one to insert a table with the contents from a given range within a given spreadsheet. I did not try to find a way to sync data real-time, as it didn't seem practical given potentially large volume of data that would have to be transferred with every edit to the spreadsheet. Instead, the script adds a custom menu ...


2

An alternative is to use Google Sites, or any other content editor that allow embedding iframes content. To embed Google spreadsheets in a page of Google Sites Create a new site or go to an existing site. Create a new page or click on Edit on an existing page. Click on Insert > Drive > Spreadsheet Select the spreadsheet Set the options Click on Save


2

The only way I found (using formulas) is by introducing a new count column. If your values are listed in column A, starting on row 2, enter the formula =COUNTA(FILTER(A$2:A$7, A$2:A$7 = A2)) in B2 and drag down to copy it. This gives: | A: VALUE | B: COUNT | |-----------|----------| | a | 1 | | b | 2 | | c | 1 ...


2

Trying to do this with built-in spreadsheet functions is sure to be frustrating. But the problem is easily solved with a custom function that returns an array: =allocate(B3:G32, B36:G36) The first argument is the list of preferences, the second is the list of class sizes. The output is a rectangular array with 1 marking enrollment. The function does not ...


2

By default, vlookup assumes the data is sorted, and finds the largest element that is less than or equal to the search key. Therefore, you should fill the table with the lower bound for each range: +-------+--------+-------+---------+ | Minor | Normal | Major | Item | | 1 | 1 | 1 | Arrow | | 5 | 11 | 11 | Weapons | | 10 | 21 ...


2

The problem is that "11:00:00 AM" is not a time value, it's just a string with characters 1, 1, :, 0, and so forth. You can convert it to a time value with timevalue function: =A1<timevalue("11:00:00 AM") or better yet, define the time directly with time =A1<time(11, 0, 0) The second approach is locale-independent; it does not rely on ...


2

It's because of <tbody>. There is no <tbody> in the HTML source. Browser puts it there (on its own; nothing to do with JavaScript on the page) because it believes it should be there, according to HTML spec. Just remove /tbody from the path: =IMPORTXML( ...


2

With the code below, you're able to insert the analytics information as well. Code var SHORT = "short", INFO = "info"; function onOpen() { SpreadsheetApp.getUi() .createMenu("Shorten") .addItem("Create Links !!","createShorts") .addItem("Get analytics !!","getInfo") .addToUi() } function createShorts() { performAction(SHORT); } ...


2

Firstly, you'd have to remove the ` from the end of first line, that may have caused some errors. Secondly, here's the sheet and code (also to be found below). Since detecting whether a column/row is hidden or not has not yet been implemented, I have made a little workaround for you. When hiding given column(s), it adds "(hidden)" to their header and that ...


2

Below is a rough example of how to show / hide individual columns from a Google Sheet. function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Custom Menu') .addItem('Show View A', 'showViewA') .addItem('Show View B', 'showViewB') .addItem('Show All', 'showAll') .addToUi(); } function showViewA() { var sheet = ...


2

It appears you don't need to change the data at all, just change the presentation of the data, i.e., formatting. Select column A, go to Format -> Number -> More formats -> More data and time formats, and set the desired format there: New date/time data added to Column A will automatically be shown as above.


2

Without a query: =unique(filter(A1:A6, B1:B6="SubCatA"))


1

=CONCATENATE(A1,".",B1,"@company.com") will do exactly what you want: It may not be the prettiest answer, but you can also combine a few concat() functions together to get =CONCAT(CONCAT(CONCAT(A1,"."),B1),"@company.com") to do this:


1

Whenever an argument is a range containing more than one cell, the custom function receives a 2D array. For example, suppose this is your spreadsheet. +---+---+---+ | | A | B | +---+---+---+ | 1 | 5 | 7 | | 2 | 6 | 8 | +---+---+---+ Then: myFunc(A1:B2) receives [[5,7],[6,8]] myFunc(A1:B1) receives [[5,7]] myFunc(A1:A2) receives [[5],[6]] - ...


1

To remove extraneous whitespace (new lines, tabs, spaces) at the beginning or at the end of the cells, one can use trim command. For example, suppose your current data range is A2:F20. In another part of the sheet, enter =arrayformula(trim(A2:F20)) then copy the results and paste values (CtrlShiftV or an equivalent) back to the original range. If you ...


1

Solution was to go to Resources -> Current Triggers and add a new trigger in the Script project.


1

If you right-click on the chart itself (i.e. the central area) and select the 'Edit: > Axis > Axis area' menu item, it allows you to edit the area the chart occupies. You can drag it up from the bottom to allow more room for the labels on the horizontal axis. This isn't exactly the most intuitive, and doesn't give as much control as you'd probably like, but ...


1

I think this requires a script (Tools > Script Editor), such as the following: it checks for blank A cells where there is data in B, and puts today's date there. If you use it, be sure to change the timezone ("GMT" in the example) to yours (here's a list of timezone names). One way to have this run is to add a trigger (Resources > Current project's ...


1

Yes, it's possible, but not in real-time, as it could possible if they were typing/viewing in the same document and screen area. Use IMPORTRANGE() function to get the data from each spreadsheet and arrays to properly join. I.E. In the admin spreadsheet, in the A1 add the following formula to get the column titles from the Test spreadsheet 1. ...



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