Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

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.

3
votes
1answer
3k views

Copy data from previous sheet

I have a spreadsheet document where I register tournament results with a sheet for each tournament date. I want to have a cumulative column to show cumulative information. I have already achieved this ...
3
votes
1answer
1k views

How can I create a formula in Google Spreadsheets that uses a date format

How do I subtract days from a cell that has dates? For example, what's a formula that will take 3/2/2011 and return this value 2/28/2011? Is this possible in Google Spreadsheets?
2
votes
2answers
3k views

Is there a way to upload a .csv file to Google Docs using a Google Sheets as a template?

I am generating a .CSV file with an application. I then upload the .CSV file to Google Docs, which creates a Google Docs Spreadsheet which is "plain and white". I then have to manually format the .CSV ...
2
votes
4answers
14k views

How to collate data in Google Spreadsheets?

I have two sheets with data in single Google Spreadsheets document: SheetA: Date Name CounterA 22.02.2011 Beta 101 22.02.2011 Alpha 2 21.02.2011 Alpha 1 SheetB: Date Name ...
14
votes
2answers
11k views

Sum on condition?

I have set up a form with Google Spreadsheet where I want to collect grocery expenses for different people. How can I sum the expenses for different persons? Each row has a column with the person's ...
7
votes
1answer
4k views

Type testing in Google spreadsheets

Is it possible to test for integers or strings in a Google Spreadsheet and respond to each differently?
3
votes
4answers
6k views

Copy image from Google Sheets to Google Docs

There's an image in a Google Shets that I would like to insert into a Google Docs. How can I transfer the image from the Sheets to the document? I can right-click on the image and select "Copy", ...
0
votes
3answers
8k views

Parse HTML through Google Spreadsheet using scripting

I'm trying to speed up the process of getting values from an HTML page to a Google Docs Spreadsheet through scripting. I remember Microsoft Excel had the import data function, but I don't own a copy ...
8
votes
3answers
27k views

Get column number by referencing column title

In Google Sheets I have a number of columns that I have given titles to in Row 1. Using a formula, I want to return the column number (i.e. 5, 6, 7, ...) for the column that has a specific title. For ...
9
votes
4answers
12k views

Is it possible to hide row numbers in a Google Sheets?

When looking at a numbered list that starts at the number 1, the row numbers are potentially confusing. (Assuming one has row 1 as column heading labels, item 1 will be on row 2, item 2 on row 3, and ...
0
votes
1answer
6k views

Automatically calculate overtime hours

I'm using Google Sheets to keep track of time with the bimonthly timesheet template. What would be useful is if the spreadsheet could start adding hours to another column when the total hours worked ...
2
votes
1answer
25k views

Google Spreadsheet formula, MINUS?

I have a spreadsheet with 3 columns: | ITEM | COST | SUBTOTAL | Subtotal, for example, is 1000 to start, like: -------------------------- | ITEM | COST | SUBTOTAL | -------------------------- | ...
1
vote
3answers
8k views

How can I retrieve records from a Google Spreadsheet in JSON format? [closed]

I'm trying to retrieve data from Google Spreadsheets with no success. I've gone through this: http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#RetrievingTables and this: ...
1
vote
1answer
416 views

Google Spreadsheets Data Management

I am looking for a way to manage the input of some data in Google Spreadsheets. Before I roll up my sleeves and write a new UI using the Google API I thought I would see if there was anything out ...
-2
votes
2answers
821 views

Not getting sub date granularity in Google Sheets trend lines

My data is in variable increments, anywhere from six to eight hours apart like so: 1-1-2011 00:00:00 100 1-2-2011 00:00:00 103 1-3-2011 00:00:00 107 1-4-2011 00:00:00 109 1-4-...
4
votes
1answer
1k views

How to toggle every other column between visible/hidden in my Google Sheets?

I have a Google spreadsheet with my monthly balance sheet for my company. I keep track of estimated and actual like this: Expense | Jan11 Actual | Jan11 Estimate | Dec10 Actual | Dec10 Estimate | etc....
4
votes
2answers
7k views

How can I use Google's functions in a spreadsheet's script?

When I use a function such as =importXml("http://www.google.com", "//a/@href") in a Google Spreadsheet cell, it works just fine. However, when I try to use the same function in a script, it gives an ...
2
votes
1answer
11k views

Put Google search inside Google Sheets cell

Is there a way to put a Google search widget inside a cell of a Google Sheets? I want to avoid my users having to hit Ctrl + F every time they search. Would be very cool to have a Google Docs App ...
5
votes
1answer
2k views

Charts for black and white printing?

I have a couple of column charts in Google Sheets that have several columns of data. I would like to print them out in just black and white, since they are part of a larger report that doesn't need ...
3
votes
1answer
7k views

What's the biggest .csv file you can import into a Google Sheets?

I'm trying to upload/import a 7 MB .csv file to Google Sheets without much luck. What's the largest file size that you can import?
34
votes
11answers
41k views

How to calculate weighted averages in Google Sheets?

I have a Google Sheets where products are listed as rows and attributes as columns. Each product's attribute is rated on a scale of 1-10. My last column is an average of these values (i.e. =Average(B2:...
39
votes
12answers
43k views

How to pass a range into a custom function in Google Spreadsheets?

I want to create a function which takes in a range... something like: function myFunction(range) { var firstColumn = range.getColumn(); // loop over the range } A cell would reference it using: ...
5
votes
2answers
26k views

Is it possible to use a loop in Google Spreadsheets?

I have the following document: +---+-----------+ | | A | +---+-----------+ | 1 | Foo (100) | | 2 | Bar (30) | | 3 | Baz (50) | +---+-----------+ I'd like to use a REGEXEXTRACT to extract ...
2
votes
1answer
462 views

How to plot time series graph with categories?

I've data in Google Spreadsheets, with columns as follows: DD.MM.YYYY CATEGORY COUNTER_1 COUNTER_2 For example: 22.12.2010 APPLES 1 20 23.12.2010 APPLES 100 20 23.12.2010 ORANGES 32 12 I need ...
11
votes
2answers
17k views

How to label points in a scatter plot?

I've tried using the built-in scatter plot chart and the Google scatter plot gadget. I haven't been able to figure out how to get either one to allow me to label the points in the chart. I have data ...
15
votes
3answers
75k views

How do I lookup a value from another sheet in Google Docs?

I have a page which looks like this: ╔═══╦════════╦══════╦═══╗ ║ A ║ B ║ C ║ D ║ ╠═══╬════════╬══════╬═══╣ ║ 1 ║ User A ║ 144 ║ ║ ║ 2 ║ User B ║ 5478 ║ ║ ║ 3 ║ User A ║ 2156 ║ ║ ╚═══╩══...
6
votes
2answers
5k views

How do I add a hyperlink to a cell in Google Spreadsheets?

In Excel, I can easily add a hyperlink to a cell by choosing the corresponding toolbar button. How do I do the same thing in a Google Doc spreadsheet? I don't see any corresponding toolbar button or ...
3
votes
3answers
2k views

How exactly does sharing work in Google Drive?

I have an Excel spreadsheet I made to track my own scores for an Xbox game I play (TrialsHD, if you are curious). I've made it available to other players so they can track their own scores, but it ...
9
votes
1answer
8k views

Cross-reference sheets on Google Spreadsheets

I can just do B12 to reference a field on the same sheet I'm working on, but how do I reference B12 on another sheet?
3
votes
1answer
2k views

How to extract information from the subject line and add to Google Sheets?

I'd like to be able to extract tasks from the subject line of an email in Gmail (using some sort of custom syntax) and then insert the task as a row in a shared Google Sheets. Currently the best way ...
40
votes
4answers
81k views

How to add days to a date

I'm using a Google Spreadsheet to perform calculations and I'd like to do some simple date math. For example, TODAY() add 7 days. I don't see any functions to do it and some quick stabs in the ...
57
votes
4answers
150k views

Formatting a cell with word wrap without switching modes?

How can one format a cell to have word wrap in Google Sheets? I've tried Shift+Enter, but that doesn't seem to work. One thing I know works is to place the spreadsheet into List Mode and then if you ...
2
votes
1answer
3k views

In Google Sheets in List View, can you add rows to the bottom instead of the top? [closed]

I've been using Google Sheets in List View (View->List View on the menu bar) as I have a fixed number of columns in my spreadsheet. I'd like to add new rows to the end of the List instead of at the ...
-1
votes
1answer
793 views

Writing a spreadsheet script

The above link is a picture of the Google Docs spreadsheet that I am working on. Basically it is a sale ticket for a small appliance parts store. I need to be able to select a range of rows and have ...
4
votes
1answer
3k views

Pass Data to Form to Store in Google Spreadsheet

I am collecting addresses in a Google Spreadsheet using a web form. But I am collecting those addresses for multiple purposes. I would like to store an "identifier" in the spreadsheet which I can pass ...
4
votes
2answers
7k views

Password protect a Google Sheets

Is there a way to secure a Google Sheets?
9
votes
8answers
39k views

How to lock/protect cells in Google Spreadsheets

I created very simple spreadsheet application in Google Spreadsheet for my mom. But I really need to lock/protect the cell with formulas so she cannot edit them. Every time I told her to try new ...
15
votes
1answer
19k views

How do I specify “everything except the top row” as a range?

I have Sheet1, which is automatically being updated via form entries. I'm making Sheet2, which is a self-sorting version of Sheet1 (it intended to be a copy of Sheet1 but sorted by a column). Ideally ...
1
vote
1answer
100 views

How to dash a whole row if a cell is OK?

I have a cell that can be OK, ERROR or WARNING. When it is changed to OK I want to modify the whole row in some way. I was thinking about to dash all cells. Is it possible? Or is there something I ...
2
votes
2answers
1k views

How can I chart based on day and month using dates in Google Sheets?

I have a Google spreadsheet that has values like: 1/1/10 0 1/2/10 43 1/3/10 17 etc. I want to be able to visualize the data on a day-by-day basis and on a month by month basis. I managed to create ...
9
votes
2answers
30k views

Inserting a New Sheet in a Google Spreadsheet

How can I insert a new blank sheet (a new tab) into an existing Google Spreadsheet? I'm able to duplicate those that already exist on the bottom, but I can't find the menu option to insert a sheet. ...
3
votes
2answers
263 views

What would I use to write a formula only once and have it work on multiple sheets?

I want to write a formula once in one sheet and have others call that formula and do calculation in their own sheet. I want to do this because calculations need to be done in 100 other sheets within ...
-1
votes
1answer
2k views

Embedding an Excel-like spreadsheet with formula calculations? [closed]

I wish to create an Excel-like spreadsheet which gets a few simple inputs (let's say three cells with the input of a number) where then a fourth cell will calculate the sum of the square of the three ...
2
votes
1answer
797 views

How can I make tabs show in Google Spreadsheets?

I copy code from an editor into a Google Spreadsheets cell. Unfortunately, it left-aligns the code making it hard to read. However, when I double-click on it to edit it, the tabs are still there (...
50
votes
4answers
149k views

How can I make some data on a Google Sheets auto-sorting?

Let's say I want Google Sheets to automagically sort some data, for example column C. How do I do that? I know that I can manually sort data by right-clicking and selecting Sort data, but this is ...
3
votes
2answers
6k views

Laying out dates in Google Sheets charts

I have a Google Sheets with measurements made at different dates. The data looks like this: 2010-09-01 80 2010-09-02 79 2010-09-10 81 I want the points that make up the data to be laid out so that ...
10
votes
2answers
2k views

How to do a simple set operation in Google docs spreadsheet?

Suppose I have some names in cells B1:B3, and a full list of all the names in A1:A4, is it possible for me to generate a list of names in A1:A4 that don't appear in B1:B3? E.g. Cells A1:A4 have "A", "...
3
votes
3answers
8k views

Why don't Google Sheets graphs lay out my points correcty?

When I try to make a simple graph of some points in a Google Sheets said points do not end up where I want them to in the graph. Using data like this I expect a straight line in my graph: col A col ...
25
votes
9answers
36k views

Can I change cell padding in Google Spreadsheets?

I don't want to stretch all rows and columns to make them more readable than they are by default...
4
votes
2answers
3k views

Google Spreadsheet: Use cell referencing in “Change Color Based on Rules”

In "Change Color Based on Rules", one can specify the text color to be any color as long as certain rules are followed: My question is, instead of hard-coding the values, can I use cell reference? In ...