A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

1
vote
2answers
40 views

Row by row comparison with FILTER returning error

I am trying do a row by row comparison of two separate sheets, and display the unequal rows on a third sheet. As an example, rows three and five on Sheet1 are different from Sheet2 and I want to ...
0
votes
1answer
27 views

Sort column from another sheet alphabetically

I have a sheet that has a column of names. I want to have another sheet that copies all of the entries from this column in sheet 1 and alphabetizes them automatically as I add new entries into sheet ...
0
votes
0answers
16 views

Can I Add Multiple “Locations” to a single row for a Map Chart

I have a Google Spreadsheet that I am testing out the Map Chart with. I wanted to test two Charts, Org and Map. So I thought that a list of gods would give me both. However, I ran into a situation ...
1
vote
1answer
29 views

Alternate colored lines in Google Sheets that respect other formattings

I accidently read this question about alternate coloring for filter views wrongly and answered a question I had all the time. The question is: how can I use alternate colored lines but still have ...
2
votes
1answer
28 views

ImportRange as dependent function

I am trying to write a script which set the function importRange in a specific cell, using us KEY the content of another cell. I have the list of the KEYs in Sheet2 and here is what I wrote so far: ...
2
votes
1answer
22 views

Keyboard shortcut to extend Google Sheet formula down to the next cell?

I have a formula in cell A2, say, in a Google Sheet. With cell A2 selected, is there a keyboard shortcut to extend that formula down to cell A3?
1
vote
1answer
20 views

What is a cut and paste approach to converting a chunk of a google spreadsheet to markdown table?

I maintain my web site in a version of markdown, with the common table extension. I maintain my inventory as a google spreadsheet. I'd like a quick way to copy paste a chunk of a spreadsheet, and ...
1
vote
1answer
48 views

How to auto generate the date a URL was last updated?

I'm trying to find out if it's possible to fetch the date a URL was last updated. So column A I'd have the URL and column B the data would auto generate. I'm guessing this would be done using some ...
1
vote
1answer
37 views

Add Additional Lines to Google Sheets Line Chart

I'm having some issues adding simple lines to a line chart on Google Sheets. I have a line chart using Table A, has 10 X and Y values, X values are dates, Y values are a number, simple line chart. I ...
1
vote
0answers
15 views

How can I auto-format US and international phone numbers plus extensions of various lengths in Google Sheets?

I have contact data being submitted from a bajillion sources globally. I would like Google Sheets to format the phone number field specifically to accommodate international phone numbers and phone ...
2
votes
2answers
30 views

Create a grade sheet where it will flag number grades in a certain range by turning that row another color

I'm trying to create a grade sheet where it will flag number grades in a certain range by turning that row another color. The problem I'm having has to do with grades of Zero. I have this formula ...
3
votes
0answers
22 views

Alternate colored lines in Google Sheets that respect filter views [duplicate]

Basically I want the zebra stripes effect for readability but all the methods I've found so far don't work when using filter views. Does anyone know a way or is this not possible? Update: Using a ...
3
votes
2answers
24 views

Don't disturb formulas on Google Sheets when cutting and pasting

I have the formula: =IF(Q5=time(0,0,0),1,0) in cell A3. It works fine, but I need to "cut" and paste the contents on Q5 elsewhere and then Google Sheets "updates" the formula changing Q5 for the new ...
1
vote
0answers
40 views

Convert jagged array to 2D array in Google Sheets

I have two columns in a Google sheet: an ID column with a single value per cell and Color column with an arbitrary number of comma separated values per cell. I'd like to expand the Color column so ...
0
votes
0answers
22 views

How to freeze a row until a similar row shows up on Google sheets?

I would like to make an effect similar to how for example iOS apps persist a table's row until someone has scrolled beside it. example This behavior happens all the time in iOS apps. You can see ...
1
vote
1answer
51 views

Using ImportXML to pull HTML tags along with data

Can ImportXML be used to pull the HTML tags along with the data? For example: importxml(A1,"//div[@id='qlist-wrapper']") on stackoverflow.com will pull all the data in the div with the id ...
2
votes
2answers
58 views

Filtering by case-sensitive string equality

I discovered (when answering this question) that string comparison in filter is case insensitive: the formulas =filter(A:A, B:B = "Yes") and =filter(A:A, B:B = "YES") have the same output. ...
1
vote
0answers
20 views

Dynamic dropdowns - for sales contact management exercise

I am trying to figure out a method in Google spreadsheets where I would be able to display information of contacts after I have selected 2 more criterias before. So imagine it this way: You see a ...
1
vote
2answers
45 views

How to check if a cell contains any of an array of values?

TL;DR: Using SEARCH(search_for, text_to_search), "search_for" is a variable-length array, pls halp On Google Sheets, I have four cells, E1:E4. The values in each cell are as follows: | E ...
1
vote
1answer
53 views

Google Sheets - Keep multiple sheets synced (both ways)

I need to have multiple Google Sheets be synced both ways. For example: I have a "master" sheet that has all of my information. And then I have multiple "filtered" sheets. Each filtered sheet only ...
3
votes
1answer
31 views

Display the contents of a named range elsewhere in the sheet

I have a sheet like so: A B 1 W X 2 Y Z I have the range A1:A2 named as "Column1". I want to display that range elsewhere. Now if I wanted to display it as a row, I know =TRANSPOSE(Column1) ...
0
votes
0answers
16 views

How to lock/protect rows/columns width in “Google Sheets”

Using Tools > protect sheets we can protect certain ranges/sheets from begin "edited". But I want to protect some/all of the rows/columns from being resized (a.k.a. lock width/height).
2
votes
2answers
38 views

Automatically create an array of unique names and their number of wins

I'm trying to make a sheet that I can use to track which heroes I need to practice in a game... Want to manually input the following things: Map name (A2:A), Hero name (B2:B), Result (C2:C) I ...
2
votes
1answer
38 views

Detect whether a cell has strikethrough format

Is there a function that checks if the strikethrough format has been applied to the cell contents? I would like use a formula similar to (=IF(HAS_STRIKETHROUGH(C5), 1,0)
1
vote
1answer
35 views

How to share different tabs in the same Google Sheet with different people/groups

I usually create Google Sheets that have several tabs. Usually the audience of each tab should be completely separate. However it makes sense to keep these tabs in the same sheet since I like to have ...
2
votes
1answer
20 views

Easily switch between seeing only lines affecting a running total and all lines

Background: I have a spreadsheet with one row per date. Each row has a current total A, there are different ways to either add or subtract an value from that total. No other column except G (for A, ...
0
votes
2answers
34 views

Auto entering functions in sheets managed by forms

I want to fill a single column of my sheet with a function, the sheet is being managed by forms. So, whenever a new entry is added it adds a new row and that row will not have the function that I ...
0
votes
1answer
36 views

How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?

Based on my data, I calculated values of hexadecimal color codes. I want some cell (I don't care whether it's an empty cell at the end of the row or the same cell) to be the color based on the code. ...
2
votes
2answers
33 views

Conditional Average with a function

I'm trying to average a range but only if one condition is true. A B C 1: A 1 2 2: B 3 4 3: C 5 6 4: D 7 8 5: A 1 6 6: E 8 9 7: E 5 8 What I want is if the char I'm looking for is "A" then I ...
0
votes
0answers
38 views

Which of the functions INDIRECT or IMPORTRANGE slows down my Google sheet that most?

I have a master Google sheet that: Uses the importrange function to fetch data from 8 different columns in 8191 separate google sheets (files, not only sheets/tabs). To be extra clear: in this sheet ...
1
vote
1answer
30 views

What is the difference between creating a menu with .getUI().createMenu() and .addMenu()?

What is the difference between these two ways of getting the same thing (a new menu item)? Version 1 function onOpen(e) { SpreadsheetApp.getUi() .createMenu('My Menu') .addItem('My ...
0
votes
0answers
15 views

How to only use the Google Drive interface (and not the Docs one)

After the switch from Google Docs to Drive I've been trained to do everything through the Drive interface which I now prefer to the Docs one. But for whatever reason the Docs interface is still ...
3
votes
2answers
38 views

How can I sort in Google Sheets by cells containing section (or version) numbers?

I'm indexing a document with sections and sub-sections. I want to have one column representing the section-number and be able to sort by this column. However when the sub-section gets higher than 10, ...
2
votes
1answer
33 views

Split one column into two equal halves

GOAL: Split one column of items into two half-sized columns of items. So if there are originally 1 column by n rows I want two columns by (n ÷ 2) rows with exactly the same items as the 1 x n column. ...
0
votes
0answers
13 views

Enable smooth scrolling in Google Sheets for large cells

Similar to this question regarding Excel.
3
votes
1answer
103 views

Problem: Script does not trigger on FormSubmit: Removing Duplicates in Google Sheet of Form Responses based on a column

I am trying to remove older duplicate form responses based on a column using the following code. The credit for the code goes to: ...
2
votes
1answer
38 views

How to select multiple, non-consecutive cells in Google Sheets using a list of the cell addresses

I have a very long, comma-separated list of cell address coordinates, shortened here for the sake of brevity: C3,C7,C17,C56. I would like to select these cells. Is this possible to do by pasting the ...
3
votes
2answers
40 views

How to get a cell to show blank if it cannot lookup the search key?

For Example: If cell A1 contains the letter "A", "B" or "C" randomly in it, and in cell B1, lookup A1 (doesn't matter where). If cell A1 DOESN'T contain the letter "A", "B" or "C" in it, I want B1 to ...
3
votes
1answer
91 views

Google sheets calculate the amount to be invested per month to generate an amount desired using compound interest

Let's say I want to generate $100,000 over 5 years at 9% interest compounding yearly. Is there a function or formula that will let me calculate the amount to be invested per month to reach that goal ...
2
votes
1answer
30 views

Present importXML output in reverse order

How to reverse the order of the output of imporxml? For example, =importxml("http://example.com","//p") outputs +---+---------------------+ | 1 | This domain is ... | | 2 | More information... | ...
1
vote
1answer
43 views

Prevent importXML from spilling text across multiple columns

When importing chat transcript by importXML, e.g., =IMPORTXML("http://chat.stackexchange.com/transcript/7/2015/8/13", "//div[@class='content']") I find that some messages split across several ...
2
votes
1answer
59 views

Is there a way to automatically duplicate a large number of Google Sheets, name them, then copy their URLs to another sheet?

Background: For a genealogy project, I've created a Google sheet where participants enter all details of one ancestor. (FILEXXXX) In another Google sheet (not just another tab/sheet, another FILE) ...
1
vote
1answer
23 views

Conditional moving average: the last two weeks or over 10 data points

I am looking for a formula to generate a moving average of the last two weeks OR the last 10 data points (whichever produces more data points) conditional upon the presence of data in two other ...
0
votes
0answers
9 views

How do I make a custom set of hidden columns in google spreadsheet which I can turn of and on? [duplicate]

So specifcly to hide, not to filter or pivot table. And specifly columns and not rows. I want to put planned and actual columns next to each other under each day date, I want to switch between ...
2
votes
1answer
64 views

How to show instead of hide repeated values in pivot table?

By standard, repeated values in pivot table are only shown once. I made an example sheet to illustrate: This becomes a problem for people who use filters over pivot tables: Notice how Donald's ...
2
votes
1answer
16 views

Set global “export to PDF” settings for Google Drive Sheets

I'm an Aussie. Google Drive is obviously built for USA first. Each time I export a Google Drive Sheet as a PDF, I have to change the page size from Letter to A4, and other such settings. Is there a ...
2
votes
1answer
45 views

How can I retain cell formatting when data is inputted via a Form?

I have a fuel calculation sheet, and I've created a form that I input the amount I've just filled up (as well as total price and my current mileage) using a Form on my phone while at the pump. The ...
1
vote
1answer
21 views

Show false if field empty, function results if not [closed]

I am trying to fix the following condition: =if(AND($L$1>=F9,F9="");"exsteeded";"") This will actually do, if a certain date is over the current date, the exceeded will show. but it have to ...
1
vote
3answers
36 views

How to display ZIP codes in Google Sheets?

Boston has leading zeros for its ZIP codes. Google Sheets is very helpful in removing leading zeros but unfortunately it removes them from Boston ZIP codes. I tried to find a number format for ...
1
vote
1answer
12 views

Google sheets formula to countunique rows with a condition

I have a sheet where I keep track of some expenses. the data is formatted like this: Vendor A | Plumbing | $250 Vendor B | Cleaning | Vendor A | Cleaning | $19 Vendor C | Washing | $20 | ...