Tag Info

New answers tagged

1

I use this custom number format for telephone numbers: "+"## ### ### ### it results in something like this: +49 123 456 789


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 ...


2

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


0

There is a way to automate the list updating by setting a Trigger[ 1 ] [ 2 ] for Update Lists. The path is Script Editor -> Resources -> Current project's triggers Then, add a new trigger. For the given examples, you can set updatesList to run from spreadsheet and On form submission. This way, the function onOpen won't be needed.


0

Here is what I did. Hit ctrl+f to search Typed \s+$ or \n in the find field I left the replace field empty Clicked "Search Using RegEx" Clicked replace I hope it works for you. Also, you can replace \n with any regex expression and it can be immensely helpful.


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 = ...


0

Conditional formatting - custom formula range 2:227 = if($i:$i = "Duplicate",True,False) select the color you want the row to be highlighted.


0

You can use "format cells if equal to...", but there are a couple of things to watch for: the reference to B4 should be B$4, with an absolute row number the rule "if equal to... B$4" would mean the cell value has to be literally "B$4". You want "if equal to... =B$4"


0

Here is an approach similar to yours; the filtering is done by regexmatch in the middle. =countif(mmult( arrayformula(if(regexmatch(B3:D,"^[^MP]$"),1,0)), transpose(arrayformula(column(B3:D))) ),">0") Explanation: regexmatch(B3:D,"^[^MP]$") matches the cells that contain one letter which is neither M nor P. if(...,1,0) converts boolean match ...


1

Here is how it could work. The script does the following: Get the sheet with form responses Get the last (nonempty) row there Pick the entry from column B. To make matching more robust, it trims the entry (removing any whitespace left or right of text), and converts to lower case. If the result is 'yes', an email is sent. The body is the content of the ...


1

The data is there, but it's out of sight because of the blank rows that are included due to the use of whole columns (A2:B). Filter the rows where A or B cells are not empty. One way to do that is by using QUERY(), i.e.: =QUERY({{'source data 1'!A2:B};{'source data 2'!A2:B}}, "Select Col1,Col2 Where (Col1<>'' OR Col2<>'') Order by Col1",0)


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.


1

You don't need arrayformula to do this. Just enter =indirect(C4&"!B$10:K$10") and you're done. This is an example of how Many array formulas will be automatically expanded into neighboring cells, obviating the explicit use of ARRAYFORMULA -- https://support.google.com/docs/answer/3093275?hl=en


0

Like this: Get the selected cell (for simplicity, I deal only with one cell even if a larger range is selected) Get the formula from it. If there is a formula, replace all references to C(number) with AC(number). Put the new formula into the cell. function replaceReferences() { var cell = ...


1

If a column of a spreadsheet is protected from a user (i.e., that user does not have edit rights to the column), then the user cannot use Filter command from the menu. The command will even be greyed out when the cursor is in that column; it won't be greyed out elsewhere but an attempt to filter will still be blocked if it would affect a protected range. ...


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 ...


1

Indeed, one cannot put things like "A6" inside the query string because then it would just a part of a string, not a cell reference. Instead, concatenate the value of the cell to the string: =JOIN(", ", UNIQUE(QUERY(A2:F4, "select E where day(A) ="&day(A6)))) You don't need transpose, because join works equally well with one-dimensional arrays of ...


1

At this time, Google docs editors don't include a built-in feature but you could extend them through add-ons and Google Apps Script. But someone already did the hard work for us and developed a free Google Gadget that work together with Google Apps Script to use Google Forms as the UI for creating and editing records Google Sheets for storing records ...


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

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 ...


1

One approach is to add a status column next to the data input: for example, in L10 it would be =if(iserror(match(H10, H11:H, 0)), "current", "obsolete") which returns "current" if no entry below the present row has the same email. Then your query can filter out the obsolete results. The above does not work with arrayformula. I don't think there is an ...


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); } ...


0

I do not have a solution if you insist on using a form, but otherwise I have a very simple solution: Let's say that the unique column is A. Then you create the following data validation rule on A2 (the 1st record after the header): =COUNTIF($A$1:$A$999,A2)<=1 . Then, you copy the cell and select the entire column, right-click, expand the paste special ...


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

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 ...


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 ...


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 | ... | ...


0

So, given a three-column table Timestamp | Username | Comment, you want a separate table that shows the most recent comment by every user: Username | Most Recent Comment. Let's say the first one is in columns A:C, and the other table will be D-E. I would do the following: D2 =unique(B2:B) which picks the usernames from column B, listing each once. ...


1

So for those who asked similar question as me, There is apparently no Group field function for Pivot in Google Sheet (as it there is in Excel) yet. Hence the simplest way is to add another column transforming your dates to the groups you need, e.g. weeks, months, years... Hopefully the functionality is soon in Google sheet too.


0

Here is a script that adds two custom menu commands: "Freeze Formulas" and "Unfreeze Formulas". The logic is very simple: freezeFormulas replaces every formula in the spreadsheet with text value of the formula, preceded by triple backtick (randomly chosen string that doesn't normally appear in spreadsheets; you can use something else like exotic Unicode ...


0

Overview There are two main situations: How to replicate the number of rows inserted How to set the corresponding formula to each cell 1. How to replicate the number of rows inserted One alternative among others is to use the Change Google Sheets installable event instead of OnEdit. The Change event has the property changeType. Your project could use ...


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 ...


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

=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:


0

When editing conditional formatting, select Custom formula and use the following... =if(A1 = 1 , true) Select you colors and your done.


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( ...


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 ...


-1

May be you can use a simple app script which can convert CSV's online into Google Spreadsheets? Here is a post that I wrote sometime back.


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 ...


0

This is actually possible by protecting the entire sheet, rather than protecting a set range, then once you have set the sheet you can add exceptions. So for instance you could restrict sheetA, the set an exception for A:B and J:K. The effect of this is that C:I and K:Z.. would be protected


0

In the header you should see, just under "Data" in the menu, a 123 with a down carat. Hovering on this shows "More Formats". Try selecting "Plain Text" there.


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")


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


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

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 ...


0

Thanks for taking you time to answer me. I've tried the suggested formula - but it doesn't work? Can you see that I do wrong?


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 ...


0

Google Forms doesn't edit data in Google Sheets, instead they send responses. Responses sent to a Google spreadsheet could be updated but should be enabled the option "Allow users to edit responses". By the other hand, Google Forms and Google Sheets don't have a built-in feature to send responses to specific sheets based one answer. One alternative is to ...


0

You can refer this tutorial, it contains video too. In the script code, change var timestamp_format = "MM-dd-yyyy"; // Timestamp Format. By default it only gives you date, change to below code to get time. var timestamp_format = "MM-dd-yyyy hh:mm:ss"; // Timestamp Format. Is this what you are looking for ? I tried it and it works perfectly.


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. ...



Top 50 recent answers are included