New answers tagged

0

The necessary changes are: replace onEdit with onOpen, and eliminate the check if( activeCell == "K2" ) as the script is no longer about the active cell. I also shortened the script by replacing the inner conditional statement with a ternary operator. function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var mysheet = ss.getSheetByName("...


1

Yes, this can be done. Following I will show an approach that use the running total formula posted by AdamL but first, it prepares the input data to be used by the referred formula: First sort the input range in inverse order C1: =SORT({{A3:B7},ROW(A3:A7)},3,FALSE) Calculate the running sum for the first (key) column F1: =ArrayFormula(SUMIF(...


0

Instead of using drop-downs from data validation and formulas put all the information in a single sheet and use filters or filters views. For details see the official help article Filter your data - Docs editors Help


0

At this time it's not possible to embed a named-range in Google Slides but you could use the Table Chart that use a reference of the form Sheet1!A1:B5 and then insert the resulting chart to a Google Slides presentation. For details see Chart types - Docs editors Help


1

This can be done with query as follows: =query({index(B1:Z10, 2, 3)}, "select * where Col1 <> '"&A1&"'", 0) The query string means "select all where column 1 of the queried array is not equal to the content of cell A1". The curly braces around index are there to make it possible to refer to the column as "Col1", without knowing whether it's ...


1

Instead of switching back to the old Google Sheet version, one can simply add the add-on of the plug-in needed (for e.g. Google Analytics) and can create, run or schedule a report from the Add-ons menu option.


1

Stepped area chart does not support variable width of steps. But you can use the ordinary area chart like this: The data provided to this chart was as follows. The first column was marked for "use as labels" when inserting the chart. 0.00 0 1.00 0 1.00 1 1.41 1 1.41 2 1.73 2 1.73 3 2.00 3 2.00 4 2.24 4 2.24 5 2.45 5 2....


1

There are multiple ways to handle this. One solution is to keep the input sheet down to one row with data, overwriting old data in Row 2 daily. Then the built-in spreadsheet functions can handle splitting: =transpose(split(rept(A2&",", B2), ",")) creates a column with the date in A2 repeated the number of times indicated in B2; and =transpose(split(...


0

You don't need a script, conditional formatting will do. Having selected the range C2:D (for example), apply to it the rule "custom formula is..." =$C2<>$D2. Here the reference to columns is absolute, but the row number is relative; so, in every row both C and D will be highlighted if unequal.


0

I would enter =array_constrain(filter(C$2:C$5, (A$2:A$5 <= A8) * (B$2:B$5 >= A8)), 1, 1) in cell B8, and copy it down the results array. Explanation: filter keeps only the cells from C where A8 falls between A and B. array_constrain makes sure at most one result is returned (this may be unnecessary if you are sure the ranges in the lookup array ...


0

The query formula can do things like this. Example: =query(A2:B, "select todate(A), count(B) where B = 'info' group by todate(A) order by todate(A) asc") This selects the dates (ignoring times) from column A for which there is at least one entry 'info' in B. The number of such entries is also returned. If you want to also have 0 entered for the dates ...


3

You can use the HYPERLINK function. Then you can click the image, and see it in a new tab. For the you tab you can save / download / export or whatever you want. Example Function =HYPERLINK("http://www.abc.net.au/news/image/7650580-3x2-340x227.jpg",IMAGE("http://www.abc.net.au/news/image/7650580-3x2-340x227.jpg"))


1

There is a difference between the string of characters "100103" and the number 100103. Your workshops tab contains numbers. regexextract returns a string. To make things work, convert string to number using value after regexextract: =arrayformula(iferror(value(regexextract(regexextract(F2:F, "ID#\d+"), "\d+"))))


2

This can be done with the following script: set a trigger "on form submit" for this function. The customizable parameters are: the column number where to insert Id, and the initial Id. The function uses Script Properties to keep track of the used Id, so they will not be repeated. function generateId(e) { var columnNumber = 5; var initialId = 42; ...


2

The method copyValuesToRange does what its name suggests: copies values to range. You want to copy both the values and formatting. For this, use the copyTo method: var sheet = SpreadsheetApp.getActiveSheet(); sheet.getRange("A1:A5").copyTo(sheet.getRange("B1:B5"), {formatOnly: true}); sheet.getRange("A1:A5").copyTo(sheet.getRange("B1:B5"), {contentsOnly: ...


-2

The answer is that Google change something in the sheets and there's a new delimiter now. =loadBlueprints("corp";4324234;"7okqZ1gOyG43243243242342qb2wkyd21C";90926985) The above would be the correct way to call the function in the spreadsheet.


1

This can be done by using indirect in R1C1 notation. The following formula matches your situation: =indirect("R" & G201 & "C" & column()-6 & ":R" & G201 & "C" & column(), False) The range is described by a string formed by concatenating R<row number>C<column number>:R<row number>C<column number> The ...


0

This question was brought up a few times on the site having posts with very different, concrete, working approaches. for a simple copy-paste (non-dynamic solution) please have a look at: Adding Multi-choice Questions faster in Google Forms by using a script please read : Is there a way to link a "Choose from a list" style question in a Google Form to a ...


0

Interacting with Google Forms before or during submission is not possible. Alternative You could use the Google Apps Script HTML Service for designing the "Thank you" message page presented after submitting the form (that will carry the ID followed by an explicit message for the submitters to copy down this ID) and create an app to handle your issue. You ...


3

You can use regexextract: =IF(ISTEXT(REGEXEXTRACT(A1, "sites")), 1,0) To explain regexextract allows you to see if that word appears in your string. However by default if the string is not found it returns #n/a , therefore i chose to wrap that formula with istext. This will only return true if the string is found.


3

=if(search("sheets",B2)>0,1,0) =if(IFERROR(search("sheets",B2)>0,0),1,0) Both work. The second writes a 0 if there's an error.


0

I am afraid that is not possible by downloading the "images" since these are not "actual" images but dynamically generated visual representations. Workaround You "tried downloading the CSV file", which means you would be getting a static "image". In that case, your easiest working alternative would be to use a screen-capture application/addon to capture a ...


2

So, the logic is: processing a data row, split the entries by commas where possible. Then output as many new rows as there are parts in the first column's entry. For those entries that are not comma-separated lists, the same value is repeated. The way I wrote the script, its output is placed in the same sheet, replacing the original data. This can be ...


0

About the Bitly link http://dev.bitly.com/spreadsheets.html You need to replace the A1 cell with the url you'd like to use. It's also quite handy that they provide a way to fetch click count too.


0

Keyboard shortcut Use Ctrl-Down to move to the last filled entry in the column with hours. More precisely, Ctrl-Down moves down to the next cell that either begins or ends a group of filled cells. If you do not have any blanks between filled-in cells, it goes to the last one. From there, pressing Down arrow again brings you to the cell waiting to be filled. ...


1

To determine the last column, one should decide in what row to measure it. Suppose it's the 2nd row; then the last column number is =max(filter(column(K2:2), len(K2:2))) where I put K to avoid circular dependency, since this formula will also affect some cells in row 2, to the left of column K. I'd put the above formula in some cell, e.g., E1 (or on ...


1

Bookend's answer works perfectly. I had to call the function from outside of the spreadsheet though (which makes sense, given that the output changes the contents of the spreadsheet). Here's how to do that: function onOpen() { var ui = SpreadsheetApp.getUi(); // Or DocumentApp or FormApp. ui.createMenu('Extra Functions') .addItem('Shuffle Sheet',...


0

This is not possible. A sheet of a Google Spreadsheet contains either a grid with data and formulas, or a chart that was placed on its own sheet. It cannot contain a document.


2

After selecting the range N2:Y, apply to it conditional formatting with "Custom formula is:" =sum(arrayformula(n(regexmatch($N2:$Y2, "Yes, I take this medication within the specified dosage range|I am unsure If I take this")))) = 0 Explanation: $N2:$Y2 refers to the range N-Y in the current row. regexmatch($N2:$Y2, "this|that") returns True when the cell ...


2

=IF(RIGHT(E23,2)>RIGHT(D23,2),RIGHT(E23,2)-RIGHT(D23,2),"NO") "=" not required inside if statement.


5

IF you have the text for example in A1 you only need one formula to extract all 3 portions: =REGEXEXTRACT(A2,".*Qty: (\d+),.*Size: (\w+),.*Color: (\w+)") To explain a little: Anything you enclose in it's own capture group or set of parentheses, automatically gets pushed to the next cell. For each type, as long as the remaining data you see in the ...


3

I took some test CSV data and pasted it into a Google Sheet. When I opened the clipboard and chose "split text to columns", my data was immediately split. There shouldn't be anything else you need to do. Try with a smaller sub-set of the data.


1

This can be done by invoking match inside arrayformula, as follows: =arrayformula(if(len(A12:A), if(isna(match(A12:A, B12:B, 0)), "Don't remove", "Remove"), )) or with linebreaks, =arrayformula( if(len(A12:A), if(isna(match(A12:A, B12:B, 0)), "Don't remove", "Remove" ), )) The combination if(isna(match( means "if not found", ...


2

Here is one way I tend to use for this kind of problem: Based on your example, the solution would be: =SUM(A10:INDIRECT(CONCATENATE("A",MATCH(999999999,A:A,1)))) How this works: The interesting part is: INDIRECT(CONCATENATE("A",MATCH(999999999,A:A,1))) INDIRECT is returning a reference based on the given string. Concatenate("A",...) is creating a cell ...


2

You can use the following QUERY to obtain the result you want. Formula =QUERY(A1:C6, "SELECT A, MAX(C) GROUP BY A PIVOT B") Screenshot Example I've created an example file for you: Transpose only one column in Google Spreadsheets


3

The multiplication sign in Google Sheets is the asterisk *, not any kind of cross symbol.


1

Instead of getFormulas and setFormulas, use copyTo method: range.copyTo(newRange); This will have the same effect as if you selected the last row and copied it below. In particular, it will also copy down any values that were entered directly into cells. If you prefer such values to not be copied, use getFormulas/setFormulas combination on newRange: ...


2

A bit late to the party but this is what I did. function onEdit(event){ var sheet = event.source.getActiveSheet(); if(sheet.getName()=='Sort by country full'){ var editedCell = sheet.getActiveCell(); var columnToSortBy = 16; var columnToSortBy2 = 2; var tableRange = "A2:AU1451"; if(editedCell.getColumn() == columnToSortBy){ ...


1

Yes, like this: July 13 (will default to current year), or July 13 2016. Both will register as a date value. If your locale is not English, you might have to use a different format and/or month name. Once you have entered something that is recognized as a date in a cell, you can double-click the cell. This will open a date picker, which allows you to ...


0

What just worked for me, after having tried copying a cell that was generated by a formula (with line breaks and some single quotes) and didn't work correctly pasting to NotePad and NotePad++, was copying to a Google Doc (or to Microsoft Word, but why do that instead?). Google Doc and Word both displayed the wanted result. Select what you want to copy from ...


0

I recommend using countifs and sumifs to handle multiple conditions. They make unnecessary your workaround with joining the columns. Example: =sumifs(E:E, B:B, "Gregor", C:C, "A") This means: add all values from column E where B is "Gregor" and C is "A".


1

There is a similar function SUMIF(), which sums the matches instead of counting them.


1

Figured it out, adding a header of 0 to the query fixed it. https://support.google.com/docs/answer/3093343?hl=en QUERY(data, query, 0)


0

You could also split your data up into different sheets (tabs on the bottom) then print all sheets. Not ideal, but it works for simple spreadsheets.


0

You seem to be missing the bottom section of this from Bookends original: } var rangeBuilder = doc.newRange(); rangeBuilder.addElement(table); doc.addNamedRange('embeddedSheet-range', rangeBuilder.build()); }


0

Use this script, adjusting the sheet name and date format if necessary. It should be entered in Script Editor, found in Tools menu. After saving the script file, go to Resources > Current project's triggers, and create a trigger to run the function newColumn daily, at your preferred time. function newColumn() { var ss = SpreadsheetApp.getActiveSpreadsheet(...


0

There is no way to get the list of sheet names with a built-in spreadsheet function, not even for the current spreadsheet. You can use a script to fetch sheet names and create a formula automatically. The script given below adds a menu item Import > all sheets to the menu, when the spreadsheet is opened. When invoked, it will get the sheet names from the ...


1

So, you want to Check that the edit was to the 3rd column Get the content of the 1st cell in that row Send an email there. Here is how: e.range is the active range, offset(0, -2) means "same row, two cells to the left". After entering the script in Tools > Script Editor, add a trigger: from the script editor, go to Resources > Current project's ...


0

In the formatting dialog, select "custom formula" with the formula = B1>=A1+2. That is, enter the inequality or equality you wish to trigger the formatting, preceded by = sign.



Top 50 recent answers are included