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

learn more… | top users | synonyms (2)

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
50 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
57 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
52 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
37 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
34 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
37 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
37 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
96 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
37 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
29 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
39 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
62 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
44 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
33 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 | ...
1
vote
1answer
34 views

How to write an IF-formula with multiple OR/AND arguments to detect duplicates?

Update3: Thanks again for you patience and kindness! Unfortunately it's still not working: "Formula error" Update2: Changing all commas (,) to semicolons (;) gives me "Could not find a matrix ...
4
votes
2answers
92 views

How to use “indirect” inside a importrange-formula?

I need help to dynamically fetch a value from another Google spreadsheet like this: FILE1 has 0 or 1 values in column G. Now I want to fetch the G column value from FILE1 with a row number specified ...
3
votes
2answers
29 views

How to restrict a Google Script to a certain cell range?

I'm working in an automatic script that shows the last modified dates of some cells within a sheet. So far, I've succeed with the sheet restriction adding an if condition, but I want to restrict this ...
1
vote
1answer
83 views

Import XML data Reloading everytime I open the sheet

I have a problem with my google sheets updating the IMPORTXML data everytime i open my sheet. Does importxml function run everytime I open Google Sheets ? Is there a way of turning off this? I don't ...
1
vote
0answers
29 views

Auomatically lock/protect a range or row based on date

Any one can help how to do a scripts for auto lock / protect range/row by date I have try but this script only lock a cell only ( cell M:M ) a = SpreadsheetApp.getActiveSpreadsheet(); var s = ...
1
vote
0answers
29 views

Google spreadsheets keep keys in sync between sheets

I have a google spreadsheet with a "master" sheet that has a list of rows (first column is a key and other are some data). I want to be able to have more sheets in the doc that would have the same ...
3
votes
1answer
18 views

Sort Column but keep associated value in Google Sheets

I have an Google Sheets spreadsheet that looks something like this: |Team #|Rank| | 100 | 4 | | 200 | 3 | | 234 | 6 | I want to sort the teams by Rank, from best to worst, and then assign ...
2
votes
1answer
33 views

Add email hyperlink to spreadsheet

I have a spreadsheet where I track incoming email questions and outgoing responses. As questions come in, I have a script that appends a row (using .appendRow()) with information in the first few ...
0
votes
1answer
29 views

Exercise breakdown in mm:ss and exercise/second

It's a workout of 11 minutes with As Many Reps As Possible. I have no coding experience and very primitive google functions knowledge. Column B is set 'number' of exercise. Column C is the ...
2
votes
2answers
82 views

Google Sheets: how to resolve emails to user names

We have a Google Apps account for our organization. When you create a Google Form using your Apps account, there is a helpful option to have the form capture the user's email address when the form is ...
2
votes
1answer
59 views

Can I add two calculated fields to a Google Spreadsheet based on a Google Form?

I am new to Google Scripts and am hoping for some guidance on improving a manual process I complete in Google Sheets. To analyse the data I receive on a Google Form I run, I manually add-in (or copy ...