New answers tagged

1

In Google Sheets, you need to wrap small in arrayformula so it produces an array: =average(arrayformula(small(D2:BD2, {1,2,3,4,5,6,7,8,9,10}))) There is an alternative solution, which is shorter and scales better if you will need 100 smallest instead of 10 smallest numbers: =average(array_constrain(sort(D2:BD2), 10, 10)) Here, sort sorts the array in ...


1

Short answer Set the format of timestamp values as Date only. Activate the "Treat labels as text" chart option. Instructions On the chart drop-down menu, select Advanced Edit Click on the Customization tab At the left panel, scroll down until you see the section called "Axis 1. Horizontal" Check the "Treat labels as text" checkbox. Click the "Update" ...


1

I don't know of a way to select a non-contiguous range via URL parameters. The solution I found is to create a new temporary spreadsheet, copy selected columns there, and export the new sheet into PDF. In the script, the array cols holds the columns to be taken: B-E and G correspond to 2,3,4,5,7. Version 1: print without formatting function printPDF() { ...


1

After seeing Rubén's answer I decided to write a different custom function for this task, with the following features: The parameter is provided as a range, not as a string: that is, =linkURL(C2) instead of =linkURL("C2"). This is consistent with how parameters usually work, and makes the references more robust: they will be maintained if someone adds a ...


1

A shorter formula, which works the same for any number of columns, is =array_constrain(filter(C2:F2, isna(vlookup(C2:F2, G$1:G1, 1, false))), 1, 1) (The formula in G2 shown; it should be copied down the G column). Explanation: isna(vlookup(C2:F2, G$1:G1, 1, false)) is True for the values that have not appeared above in column G. filter(C2:F2, ...) ...


2

I think I figured it out myself. This works for 5 columns, but I could add more by extending the formula. =if(isna(C12=vlookup(C12,$G$2:G11,1,false)),C12,if(isna(D12=vlookup(D12,$G$2:G11,1,false)),D12,if(isna(E12=vlookup(E12,$G$2:G11,1,false)),E12,if(isna(F12=vlookup(F12,$G$2:G11,1,false)),F12,""))))


1

Short answer The link points to a Q&A that has two answers but non-of them looks to me to be appropiate. I.E. the code of one of answers is incomplete and both of them send emails, so the adoption is too complex to be used as a starting point. Below is a script that is easier to adopt. Instructions Create a test form and set it to send responses to ...


1

Short answer Use a custom function to get the quoted string inside a cell formula. Code The external post shared in the comment by Yisroel Tech includes a script that replaces each formula in the active range by the first quoted string in the corresponding formula. The following is an adaptation as custom function of that script. /** * Extracts the ...


2

To find the number that's halfway between A2 and B2, use the formula =(A2+B2)/2 (You'll need to apply a suitable date format to the output.)


2

You can right-click the top of the column (Command+click on a Mac), choose "Resize column" and enter some reasonable value (the default is 100; the width is measured in pixels).


2

A quick way to do it is indeed to use vlookup. For example: in F1 I'd put =sort(unique({A1:A; D1:D})) which collects the data from two columns, eliminates repetition, and sorts. Then in G1 put =arrayformula(iferror(vlookup(F1:F, A$1:B, 2, False))) which takes the matching values from B column. Similarly, for H1 use ...


1

Here is a script that does the following: if a user enters 'DONE' in any cell in sheet named 'Sheet1', the row with that cell is copied over to Sheet2, and deleted from Sheet1. To use, just enter in Tools > Script Editor, and change sheet names if needed. The script does not check that 'DONE' is in the last nonempty cell of a row: it can be anywhere. ...


0

This is similar to my answer to IMPORTHTML at a certain time of day in Google Spreadsheets, but I guess the question isn't a duplicate, so: Enter the script (below) in the Script Editor (under Tools) Change the name of sourceSheet and logSheet to what you have. Create a trigger under Resources > This project's triggers, so it runs daily between 8pm and ...


2

I modified the script so that it accepts arrays of column headers: updateColNames lists the columns that you want to keep track of timeStampColNames lists the columns where timestamps should appear, in the same order. There is now a for loop that goes over the tracked columns one by one, comparing them to the edit location, and inserting timestamp when ...


3

This worked for me when I tested it with your sample strings: ="AND(A="&SUBSTITUTE(A1,","," OR A=")&")" It basically replaces each comma with the or part of the string.


1

Suppose A1 is "1,2,3" ="AND (" & JOIN(" OR ",ARRAYFORMULA("A="&split(A1,","))) & ")" where split(A2,",") splits A1 into an array {1,2,3} arrayformula("A=" & split(...)) convert the array to {"A=1", "A=2", "A=3"} join(" OR ", arrayformula(...)) joins the array with " or " inbetween value, so you have "A=1 OR A=2 OR A=3" "AND (" & ...


2

This can be done with an Apps Script. To use, enter it in (Tools > Script editor), save, and add a trigger via (Resources > This project's triggers) with parameters "from spreadsheet" and "on change". The change of column width is an event of type "OTHER". It doesn't seem possible to detect which column was changed; only what sheet (tab) it came from. So I ...


1

I've modified the code from my answer to fit your description. In the example below, columns 2-5 (that is, B-E) of sheet named 'Sheet1' are monitored for changes. In case of change, a timestamp is inserted in the last row of that column. To customize: change the sheetname and column bounds on line 4 of the script. If a non-contiguous range of columns is to ...


1

Suppose TEAM is in B2:B10, to find if member Alice is part of the team, you do regexmatch(B2:B10,"\b(?i)Alice\b"). Here, \b(?i)Alice\b is a regular expression. \b means word boundary, and (?i) means the word search is not case sensitive. Now, suppose you want to all A2:A10 values where Alice is a part, you do filter(A2:A10, ...


1

=regexreplace(index(ImportXml("https://www.google.com/finance?q=ibm", "//div[@class = 'companySummary']" ),0,1),"\n","") Using the regexreplace function, you can replace all instances of a newline, represented by "\n" and replacing it with "", thus transforming your string into a single line.


1

You can pull in those links with this formula: =IMPORTXML("http://www.nasdaq.com/markets/ipos/activity.aspx?tab=upcoming","//tr/td[1]/a/@href")


1

Short answer Use the following formula =SUBSTITUTE(A1,Char(10)," ") Explanation IMPORTXML added line feed characters which ASCII code is 10. An alternative to the use of Char(10) is to add a breakline (CTRL + ENTER) and enclose it in quotes marks: =SUBSTITUTE(A1," "," ")


1

You can use =datevalue("01-"&B2) (single-cell formula that can be copied down), or =arrayformula(datevalue("01-"&B2:B)) (converts an entire column at once), or =arrayformula(iferror(datevalue("01-"&B2:B))) (does the same, while suppressing any error messages). This works because the strings like 01-Oct-14 are understood by datevalue ...


2

Short answer Besides the use of other functions, like YEARFRAC() as was suggested by Live Forever, usually the way to get the shorter formula is to use a custom function. Example A Custom function /** * Calculates the progress of the year for the date provided. * * @param {DATE(2015,1,1)} input - The date to calculate the progress of the year. * ...


3

Here is a shorter formula that is equivalent to yours, using yearfrac: =yearfrac(date(year(A1), 1, 1), A1, 1) However, the formula you have is inconsistent with your description, since it returns 0 for 1-1-2015, representing that at the beginning of that day, no time has elapsed since the beginning of the year. To have 1/365 for 1-1-2015 and 1 for ...


2

As mentioned in a comment and later the answer by Vidar S. Ramdal, I would probably recommend to rewrite your logic from using lots of formulas, into using one/a few Google Apps Scripts. It's worth to note that Custom formulas, in contrast of nondeterministic functions like RAND, are recalculated only when their parameters change. Using a ...


2

As mentioned in the comments, I would probably recommend to rewrite your logic from using lots of formulas, into using one/a few Google Apps Scripts. See this example spreadsheet. It includes a Google Apps Script function that returns an array of 6 random numbers: function giveMe6RandomNumbers() { return [Math.random(), Math.random(), Math.random(), ...


2

The problem is here: all_sheets.push( [ sheets[i].getName() ] ) This should be changed to all_sheets.push( sheets[i].getName() ); The second version inserts a sheet name into array, which is what you want. The first version inserts a one-element array into array, resulting in a double array like [['Sheet1'], ['Sheet2'], ['Sheet3']]. For ...


0

The comma-separated representation is really inconvenient in a spreadsheet. So the first thing I'd do is to split it. Suppose column A has the data; put =split(A1,",") in cell B1 and copy down. This fills some of columns B,C,D with the names. Then you probably want a single column (E) listing each name only once. This can be achieved with ...


1

There are multiple ways, but the easiest is to use countif with wildcards: =countif(A1:A, "*X*") Here * matches any number of characters, so *X* matches any string containing X.


-1

Got the solution right here.... (see example sheets file in this link) To sum it up: If you can live without linebreaks in the text you are generating within a cell, then you will be able to paste without quotations. the line-breaking can then be done in the destination file. Exhaustive example in the link above.


1

Firstly, remove all the filters from your sheet if you have any. Then, just add this script in your script editor and run. function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var maxRows = sheet.getMaxRows(); //Logger.log(maxRows); for (var i=2; i<=maxRows;) { ...


6

You may use the Web version of Keep (https://keep.google.com) and copy/paste all items directly there and it will create a checkbox on each line you pasted. It will sync automatically with the mobile app.


0

The problem is that split does not work with an array (unfortunately). There are workarounds though. In your case, replacing index-split with regexextract does the job: =FILTER(A19:A32, NOT(COUNTIF(regexextract(B:B, "^\S+"), A19:A32))) The regular expressiion ^\S+ means: get all non-whitespace characters at the beginning of the string, which is what you ...


1

This will fix it for you: =text(regexreplace("timestamp","^\w{3},",""),"MM/DD/YYYY") you could also transform to remove the additional time parameter at the end with this: =text(regexreplace(regexreplace("timestamp","^\w{3}, ",""), ", \d+:\d+\w+",""),"MM/DD/YYYY") OR if your data consistently is formatted the way you did in your examples (with the ...


2

You can use "Custom Date Formatting" to obtain mm/dd/yyyy format; however this requires the datetimes to be entered as datetimes — not as strings of text that look like date and time. Usually, the difference is clear because under default formatting, datetimes are right-aligned while text is left-aligned.


2

If the names of sheets were known, you'd be able to do something like ={filter(Sheet1!C2:C, len(Sheet1!C2:C)); filter(Sheet2!C2:C, len(Sheet2!C2:C)); ... } to join all of those together. But since new Sheets will be added, and you don't know what names they will have, you need a script. I can think of two approaches: (a) the script will collect the ...


1

Short answer Instead of using IMPORTRANGE use Google Apps Script Explanation According to Change a spreadsheet's locale, time zone, recalculation, and language the recalculation interval for IMPORTRANGE is 30 mins. If shorter recalculation interval is needed then somehow the formula parameters should be changed. Unfortunately, in the new Google Sheets is ...


1

After every match, only the ranks of the two players change. So, instead of calculating the rank of every player after a match, just calculate the new rank of the players. The trick is, say Alice played Bob, you need to find the last time Alice played and her rank at that time, as well as the last time Bob played and his rank at that time. This spreadsheet ...


0

Short answer Yes, it's possible. To do that you will require to use the Edit or Change Google Sheets events of Google Apps Scripts. Extended answer You can find a code example in the answer from Jacob Jan Tuinstra to Updating logging sheet from another sheet doesn't work after inserting code offset References Extend Google Docs, Sheets, and Forms ...


0

Were you using the filter button to filter/sort or was it turned on at the time? What can happen is that if you have that filter turned on, but it is not properly wrapped around the entire data set it will only sort the data it IS wrapped around - what I do instead of using the filter to sort, is click on the tiny arrow that shows up next to the column ...


1

You need to use regexreplace first to remove the time part of the time stamp, and then the Text() function will properly transform the way you want. =TEXT(REGEXREPLACE("date timestamp string","\d+:\d+:\d+",""),"MM") Note: the timestamp part of the formula - may need to be altered depending on what exactly your string looks like, if you post an example of ...


2

It doesn't work with arrayformula - importdata, importxml, and split will not work it unfortunately.


2

You can do a count for each month by transforming an array formula to only count based on that month: =COUNTIF(ARRAYFORMULA(TEXT('Canada Twitter Raw Data'!A2:A,"MM")),"01") change the "01" for each month so february would look like this: =COUNTIF(ARRAYFORMULA(TEXT('Canada Twitter Raw Data'!A2:A,"MM")),"02")


2

An alternative to double clicking / pressing F2 on a cell is to paste the text into the formula window (next to "fx" above the sheet). Note that neither this nor other methods described will preserve formatting (font color, font face, etc), which may or may not be a plus.


0

I have managed to "solve" this. Pasting line by line is not the solution of course. The problem was the browser in my case, my friend who was using Mozilla had this problem. I was using Google Chrome and it worked fine for me. After he tried on Google Chrome, it worked for him too.


1

If logging the entire table I would keep importhtml in one sheet and use another sheet for historical log. Below these are Sheet1 and Sheet2. function dailyLog() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var currentSheet = ss.getSheetByName('Sheet1'); var logSheet = ss.getSheetByName('Sheet2'); var lastRow = logSheet.getLastRow(); ...


2

If you did this just for the convenience of your employees, then filter views would do the job nicely. But filter views don't offer any data protection: if someone can see the filter view, they can see the entire sheet by turning off the filter view. To actually protect the other rows from being viewed, you can create a separate spreadsheet for each ...


2

Lipis' code was not working for me, so here's an update. This code: works with current version of Google Spreadsheets correctly addressing array elements checks whether the values are numbers has a switch to consider zero values or not Code: /** v - Values range w - Weights range z - count zero values? */ function weightedAverage(v, w, z) { var ...


4

You could use a simple formula to build a string defining the Range, name that location, and then access it with the INDIRECT Function in your formulas. E.g. for your question above. Put in any cell (e.g. B3) a formula like: = "Sheet!$A$1:$A$" & B2 Name Cell B3 as "items" as a named range. Then whenever you would have used "items" in a formula just ...



Top 50 recent answers are included