5

Since Jul. 25th 2019 it's actually possible to specify in which account to create the new file, exactly as you stated in your question. From now on: docs.new/1 - creates the file in your personal account docs.new/2 - creates the file in your second account (if you're signed in) That applies to all .new shortcut URLs, check the explainer video from Google.


5

It is as simple as using the following sequence formula =SEQUENCE(A1) Or you even could have something like this: =SEQUENCE(A2,1,A4,A6) You could even use the SEQUENCE function for dates =SEQUENCE(A2,1,A4,A6) SEQUENCE


4

As this post is old but the current data has changed I will add a new answer for (2019) data. Again for your cell count the answer is still: NO That said google has upped its cell limit to 5 million. I have tested this and it will load exactly 5 million cells into google sheets but no more. As a reference at the time of this post the current limitations ...


4

Asterisk (i.e. *) repeats the following character to fill in the remaining space in the cell. If you are willing to use a custom number format (perhaps because you want a different currency or don't like the way the accounting format displays negative numbers), the character that specifies this layout is the *, followed by a space. Under Format / Number / ...


4

Disclaimer: I am posting this answer that doesn't exactly answer the asker's question because this is the answer I needed when this question was the first result on Google, and I want to help the next person who has the same question I did. This may not be the prettiest formula, but the result is the prettiest I could manage. Here's the Google Sheets ...


4

You want to update the value in Cells A1 and B4 at the same time. The solution is to update the values as an array. In this script: var values = range.getValues();: gets the entire range A1:B4 values[0][0]= "Apple" ; and values[3][1] = "Banana";: update the array for the equivalent of cells A1 and B4 respectively. range.setValues(values);: updates the ...


4

The best formula for you to use would be: =NOW()+[days to hit goal] You mention: ...turn the number of days to hit my goal into a date format (so display "04/12/2020" vs "5.8 days") You also mention in answer: I used the following formula: = TODAY ( ) + [days to hit goal] and then formatted for a date within the cell. It now displays ...


3

I know this page is quite old and likely no longer relevant to OP, but I came across this page via Google when trying to find a solution to the same problem. I found an alternate path so I figured I'd chime in with a simpler, formula-based solution in case someone else finds this page in the future. We can use the =Indirect() formula to influence our =sum() ...


3

This can now be accomplished via the Show edit history feature. Right-click on a cell and select "Show edit history". It will show you who made the edit, when it was made, and what changed. You can then go back and forward through cell edits.


3

If you want to paste a JSON object you can use http://www.convertcsv.com/json-to-csv.htm to convert it to a CSV and then import that into Google Sheets. If you want to rotate the data from columns to rows you can use http://www.convertcsv.com/transpose-csv.htm I found this easier than messing with an addon script.


3

If you, as Ryan Shillington suggests, want to know the number of cells within a range that has "sites" in it, you might try: =COUNTIF(A1:A100;"*sites*") The * acts as a wildcard notion, so it will look for a match anywhere within the cell, not just an exact match for the whole cell. I've also done this with a cell reference instead of "sites", but ...


3

If the cells all contain ".com" or "www" or "http" etc. I just do a simple find and replace. Find "www" -> Replace with "www" Search range or column etc. Replace all


3

As of today's version of Google Sheets (on Dec 6, 2019), we can do this to make the values appear: Create your pie chart (assuming you know how) Click in the upper right of the chart, on the 3 dots. "Chart editor" will appear on the right. Under "Chart editor", there will be "Setup" and "Customize". Click on "Customize" There should be a selection for "...


3

The best way to handle sharing files with large and dynamic groups on Google Drive is by using Google Groups, but it's also possible to do the way you are thinking by using Google Apps Script or the Google Drive API and the Google Sheets API To use a group from Google Groups to handle sharing files, share the files with the group by adding its email address....


3

I figured out a way that is almost as good. Early on in Google sheets double clicking the paintbrush icon worked. Why Google removed that I don't know. But while you can't make the paintbrush icon "stick" anymore, what you can do is: click the cell (or range of cells) whose format you want to copy. click the paint-format paintbrush icon (to copy format). ...


3

Instead of date 'TODAY()' use date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'" NOTES: The last " should be placed in a proper place in order to have a text value as the second argument of QUERY. 'TODAY()' doesn't returns Today's date, it returns a string with the word TODAY followed by parenthesis. Try this =ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, "...


3

You have to "stack" your columns. You can do that using the following formula: =FILTER({A4:A10;C4:C10}, MATCH({B4:B10;D4:D10},"RINGO")) EDIT (following your comment): =SORT(FILTER({A4:A10;C4:C10}, MATCH({B4:B10;D4:D10},"RINGO")))


3

Yes, it is parameter linewidth. Example : =SPARKLINE(L4:L;{"linewidth"\3}) Reminder on how to construct a =sparkline : =SPARKLINE("range",{"charttype"\"line","xmin"\0,"ymin"\0,"color"\"orange","empty"\"ignore","linewidth"\6}) More on sparkline here.


3

Quick and dirty way : =UNIQUE(ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(A1:F12&"🐥"),"🐥")))) (Source : organizing google-sheets data range into single column) However, care must be taken not to exceed 50k characters when using concatenate. If the case arises, simply break the formula into several pieces or use arrays. =unique({A1:A10;B1:B10;C1:C10}) ...


3

Use curly brackets within this very simple query: =QUERY({Alpha!A2:B;Beta!A2:B;Charlie!A2:B},"select * where Col1 <>'' order by Col2") The trick here is the curly brackets {....} we use to create and combine the arrays from the different tabs. Extra tip: {Alpha!A2:B;Beta!A2:B;Charlie!A2:B} A semicolon ; combines vertically (in columns, one ...


3

I modified the excellent code provided by Jacob Jan Tuinstra to fit your needs assuming your data starts at A2. Step 1 Open the Script editor from your sheet menu and paste this code: function result(range) { var output2 = []; for(var i = 0, iLen = range.length; i < iLen; i++) { var s = range[i][1].split(","); for(var j = 0, jLen = s....


3

2nd EDIT (following OP's request) "I need a solution that gets me all the way from having a list of filenames (what I have now) to having a list of links I can click on and get to the corresponding file. Incomplete answers like "write a custom menu that uses DriveApp.getFilesByName" won't be accepted" 1. On the spreadsheet's menu find Tools and click on ...


3

You can build a custom parser for any fixed JSON string using regular expressions. For example, if you want to extract the child items into columns you can do the following (assuming your JSON string is in A1): =SPLIT(REGEXREPLACE(INDEX(REGEXEXTRACT(A1,"(\[(\n|\s|""|\w|,)+\])"),1),"(\[|\]|\n|\s|"")",""),",") You can adapt this formula to suit whatever ...


3

With a regex, you can replace each character with itself + ','. Then use a regular split function. =split(REGEXREPLACE(A1,"(.)","$0,"),",",true,true)


3

You can use the following formula: =SORT(B2:B11,ROW(B2:B11)*N(B2:B11<>""),0) Functions used: SORT (Sorts the rows of a given array or range by the values in one or more columns.) ROW (Returns the row number of a specified cell.) N (Returns the argument provided as a number.)


3

From Google's Keyboard shortcuts for Google Sheets: Move to next sheet : Alt+Down arrow Move to previous sheet : Alt+Up arrow


3

Working with date, time and specifically duration is a pain in JavaScript, and Google Sheets does not make it much easier. A JavaScript Date object (which is really a datetime object) is not suited for working with durations. A datetime is a lot more complex than it seems at first. So when you ask Where did the "days" part of the duration go? ... ...


3

Just like Microsoft's VBScript, Google has its own language - Apps Script. It lets you automate sheets with a Javascript-like syntax, should be easy if you know Javascript. Tutorial and documentation from Google: https://developers.google.com/apps-script To use it, open your Google Sheet file > Tools > Script Editor...


3

One trick that helps a lot is to shuffle the pivot analysis off into another file File 1 does your data entry, and contains your static files for lookups. File 2 uses one or more IMPORTRANGE, one for each sheet it needs from file 1. Instead of updating every minute or on every change, IMPORTRANGE runs every 30 minutes. Since I didn't need Real Time pivot ...


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