Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. Don't use it for the Google Drive Excel files editor.

7
votes
2answers
2k views

Let users only change their own data in Google Spreadsheets

I have a Google Spreadsheet in Google Drive. Users can change everything and anything in the spreadsheet. Now, I want them to be able to change empty fields or their own fields only. Is this ...
9
votes
8answers
28k views

Pasting text with line breaks into Google Sheets populates multiple cells

If I copy text with a line break in it, like this: 1st line 2nd line and then paste in into a Google Sheets cell, then it pastes '2nd line' into the cell below. What are the ways to avoid this?
2
votes
2answers
620 views

Allow users to use my online spreadsheet without saving their edits

I made an online spreadsheet to calculate grades. I want my users to use it without Google Sheets showing it real-time to other users. How do I do this? It seems if I allow people to edit the ...
2
votes
2answers
5k views

How to escape a double-quote in a TSV file for import into a Google Sheets?

How does one specify a cell to contain exactly one double-quote character in a TSV file so that it will be handled correctly when imported (as a spreadsheet) into Google Sheets? (Note: when importing ...
1
vote
1answer
527 views

How do I get the unique number of days in a datetime column in Google Spreadsheets?

I have a column full of datetime values in a Google Spreadsheet. I want to get a count of how many unique days there are. As in 8pm and 7pm today would count as 1. Tomorrow at 9pm, 3pm and 4pm would ...
3
votes
2answers
4k views

Share certain columns

I have a Google Forms that gets filled. This obviously goes into Google Sheets. I want a summary of this information to be publicly viewed. Kind of like the summary page in sheets but I want only ...
2
votes
1answer
2k views

How can I make some data on a Google Spreadsheet auto-sorting using onOpen?

I was working with @W0lf here but I am unable to comment/continue the conversation anymore. I am trying to use autosort function onOpen but is not working: function onOpen(event) { var ss = ...
0
votes
1answer
387 views

Working with names in Google Sheets

I have created a spreadsheet to keep track of my players on my team. I would like to sum all of their individual times playing regardless of who they played (different players). Is this possible? ...
8
votes
4answers
14k views

Having units in Google Spreadsheets?

I want to be able to input plain numbers like 200, or 3000 and have the cell display 200g, 3kg, etc... But still be able to add the values in the cells. So if I add the two above I'd get 3.2kg on the ...
14
votes
2answers
22k views

Google Docs speadsheet auto-fill: control what increments [duplicate]

Possible Duplicate: 'Lock' part of formula when copying from one cell to another Is there a way to control which variables in a formula get incremented when you use auto-fill in a Google ...
14
votes
2answers
25k views

Format a date fetched from another cell for display using CONCATENATE

Given say 2 different dates in A1 and B1, what I want to do is just show the range of dates as a label in A2 by using =concatenate(A1, " - ", A2) However, A1 is in the default format (MM/DD/YYYY) and ...
0
votes
2answers
2k views

Is there a limit on functions in Google Spreadsheets? [duplicate]

As to answer a question on Super User, I was making a sample spreadsheet on Google Docs. I made it first in Excel 2010 on my computer to copy/paste it later on. When I tried to copy a formula that ...
40
votes
5answers
64k views

Sum values with same name

I have a Google Sheets with a long list of names and amounts. Many of the names are duplicates. For example: John | 10 Bill | 2 Susan | 3 Frank | 4 Sally | 10 John | 2 Susan | 2 John | 3 Is ...
1
vote
1answer
2k views

How come I can't input a formula into my Google Spreadsheet?

Here are my steps: focus a cell in my Google Spreadsheet, hit = and then highlight the field D3, hit +, highlight the field D4, hit +, highlight the field D5, hit Enter. However, the field just ...
2
votes
1answer
6k views

JOIN formula in Google Spreadsheets

I want to use the JOIN formula in Google Spreadsheets to transform all values in one column into a list, with , around each value. Has anyone used this formula before? The only information I can ...
0
votes
1answer
5k views

Change all values of a column at once

How can I change all values of column A (currently empty) to be 5? In Excel, I could do Paste Special > Add.
11
votes
5answers
28k views

How do I remove the '0' from a field with an equation?

I have a Google Spreadsheet that I enter in new values everyday and want to have the equation run down the entire column so I don't have to drag the previous day's box down to copy the info. It would ...
4
votes
1answer
2k views

Sorting unique rows by a related SUMIF column

I am putting together a worksheet to keep track of expenses. I currently have the following source table called "Expenses": A B C D 1 Date Description ...
10
votes
6answers
50k views

Advanced date logic in Google Spreadsheets

I have the folowing situation: Today is 4/9/12. I have a cell with a date in the sheet that is 4/13/12. This cell should turn red since it is within 7 days from the current date. What is a ...
0
votes
1answer
697 views

How to reference Google spreadsheet inside your mail message?

I would like to embed a Google spreadsheet directly into my email, so that it reflects the document changes. How do I do that?
123
votes
2answers
235k views

How does one add a new line in a cell in a Google Spreadsheet?

I pressed return to make a new line in a cell of my Google Spreadsheet. That just moved the cursor to the next cell. How does one add a new line in a cell?
18
votes
7answers
86k views

How to Fill a Column with Sequential Dates in Google Sheets

In Excel you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example if you enter 9/1/2014, select that cell, then right-click drag down over the cells ...
6
votes
5answers
17k views

Is there a way to generate number of days in a month?

The title kind of says it all but just to give you the use case, I have a spreadsheet where I track monthly advertisement revenue and want to end up with a daily revenue column. Here's a simplified ...
2
votes
1answer
2k views

Bring data from Google Spreadsheet to Google Documents

Is there anyway to read the data from a Google Spreadsheet (which I owh) to another Google Document, something like specifying a data source in MS Word?
5
votes
2answers
14k views

A Way to Add Text Before Your Hyperlink in Google Spreadsheets? [duplicate]

Is there a way to add text before the hyperlink? So for example: This section needs reworking. You can find the section HERE. HERE would be the text replacement for the actual link. The actual ...
46
votes
2answers
187k views

Link to specific sheet in Google spreadsheet

I have a complicated Google spreadsheet with many sheets and a table of contents. Is there some way to create a link to the sheet names so that with a click one can go directly to the sheet? That is: ...
2
votes
1answer
637 views

Send Google spreadsheet with embedded HTML in Gmail

When I try to publish content from Google Spreadsheet, it puts it into an HTML iframe. This is found under File > Publish to Web. How can I send this iframe content in a mail, sent over Gmail?
2
votes
2answers
2k views

How to write a logical function that checks several columns and rows

I have three columns in my Google Spreadsheet: C (Dialog) - D (Declined) - E(Answered) All the columns are dates. Example: C D E 14/02/2012 28/02/2012 14/02/...
3
votes
2answers
6k views

Hyperlink within a Google Sheets?

I have a Google spreadsheet with many tabs. I would like to put a link in a cell on one tab that when clicked takes me to another cell on another tab. Is this possible?
2
votes
2answers
1k views

How to put the name of a cell into another cell?

In Google Sheets, is there a simpler way to put the name of a cell reference into another cell than this? =concatenate(char(code("A")+COLUMN(D3)-1),row(D3))
15
votes
1answer
14k views

Refer to Sheet with value in Cell?

The usual way to reference a sheet is here but how do you reference a sheet when the sheet's name is a value in a cell? For example, A1 = "Sheet number two" B1 = A1&"!B4" Here I want B1 to ...
1
vote
2answers
2k views

Return an offset cell value, where last occurrence in range is matched

I have a worksheet like this: || A | B | C | ---++--------+-------+-----| ---++--------+-------+-----| 1 || 1 | a | | 2 || 2 | b | | 3 || 3 | X ...
5
votes
1answer
6k views

How do you conditionally join text in Google Sheets?

So, I thought this would be relatively straightforward for an advanced user, but this one is troubling my brain. I have a schedule of people: 1 2 3 4 5 6 7 8 John D. a a ...
16
votes
10answers
23k views

Downloading charts from Google Sheets in high resolution

I have a Google Sheets with data from which I've created a few charts using the built-in charting tool. Now, I'd like to download these charts in a format I can use in a PDF document I create locally (...
10
votes
8answers
49k views

How to make a query with Google Spreadsheet, that checks for a date

I have a range that goes from B4:H124. All the cells in column B are dates. I would like to retrieve the data from a row (B:H), where the DATE equals to the content of a specific cell, lets say Q4. ...
2
votes
2answers
2k views

How to count cells that are below a certain date and check if other cells in the current row are empty?

I need to count how many cells in a specific column are below a certain date. Also, at the same time, the counter should only be increased, if some other cells next to each cell being counted are ...
21
votes
7answers
28k views

How to export a filtered set of rows from a Google Spreadsheet?

Is it possible, when exporting a Google Spreadsheet into a CSV, to apply this operation only to filtered set of rows? I need to get in CSV exactly the rows which are visible on the sheet after a ...
1
vote
1answer
6k views

How to query for count of dates in Google Docs?

I have a Google Spreadsheet of line items. One of the columns is a Date field. How can I query (or use a formula) to, for example, get the count of records where the Date field is within February of ...
8
votes
4answers
32k views

How to sum ​​values ​​in a range of dates

I have a Google Spreadsheet in the format below — here's an example. Column A → Dates Column B → Numbers I'd like to sum values ​​in column B in a range of dates in column A. Example: Sum ...
19
votes
2answers
66k views

How do I create a box plot chart in Google Spreadsheets?

I want to create a simple box plot from 1D data, which doesn't seem to be supported by the default Insert → Charts. There is a histogram gadget but I haven't seen any box plot gadgets. Do I ...
-3
votes
1answer
1k views

Simple row references in Google Spreadsheets and resorting data

This is my scenario Users are entering data into rows with several columns Last column is calculated on values of few previous columns (so it's row number independent). While entering new and ...
10
votes
3answers
21k views

How to chart the count of items?

I have a spreadsheet that is filled by a form. It contains values like the following: Timestamp Person Category 2012-02-13 10.31.22 a x 2012-02-13 11.06.37 b y 2012-02-13 11.34....
10
votes
2answers
47k views

Calculating shaded cells

I am using Google Spreadsheets to create a matrix (time/activity) as a daily schedule for staff to sign into. Is there a formula to calculate the number of cells in a column that are shaded (or not ...
2
votes
1answer
10k views

Filter rows in Google spreadsheets [duplicate]

I have a Google spreadsheet. There is a colour column. Every row has a colour, Red, Yellow, Green etc. I want to be able to filter out say all rows that are Red, all rows that are yellow, all rows ...
6
votes
3answers
2k views

Format credit card numbers as four digit blocks in Google Sheets

I have this credit card number in a Google Sheets of the following format: XXXXXXXXXXXXXXXX How can I make it display like XXXX XXXX XXXX XXXX or XXXX-XXXX-XXXX-XXXX where they're in four digit ...
-1
votes
1answer
3k views

Google Docs ImportXML can't scrape anymore?

ImportXML lets you get information from filetypes such as xml, html, csv, tsv, as well as RSS and Atom feeds. This example in Google Docs Help for importXml function - =importXml("toysrus.com";; "/...
3
votes
1answer
737 views

In Google Spreadsheets the xPath method with Google Maps don't work anymore?

I've used this query to calculate distance between two locations, now it won't work anymore, and returns the error xPath query didn't get any result (translated from Italian, I don't know if is the ...
9
votes
2answers
20k views

Sum column only if another column has an empty value

I am trying to get a sum of column values in a Google Spreadsheet only if there is no value in the adjacent column. Consider the following example: A | B ------- 40 | 50 20 | 20 30 | 10 | ...
1
vote
6answers
5k views

Sum of the values of the 10 cells above me?

I'd like a formula for Google Sheets that will return the sum of the values of the ten cells above the current cell. That is, if I'm on C31, I want the equivalent of =SUM(C20:C30). However, I want ...
4
votes
2answers
13k views

How can I calculate how often one string appears in a column compared to another string?

I have a column in my Google Spreadsheet which contains either OK or NOK (acceptance testing). How can I calculate the percentage for how often OK appears compared to NOK.