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

learn more… | top users | synonyms (2)

92
votes
6answers
245k views

How do I link a cell in Google Spreadsheets to a cell in another document?

I have a monthly spreadsheet that relies on figures from the previous month. I'd like to import these values dynamically rather than cutting and pasting them. Is this possible? How do I do it?
60
votes
6answers
108k views

Google Spreadsheets conditional formatting based on another cell's content

How can I set conditional formatting on a particular cell that depends on another cell's value? A | B 1 | x 2 | 3 | 1 | x 1 | x 4 | 8 | // X can be any value and is here merely to // mark the ...
21
votes
8answers
46k views

Is there any way to create a timestamp in a Google Spreadsheet?

I use PSPad as a text editor, which allows you to press Alt + D to insert a timestamp, e.g.: 2010-07-17 23:45:44 Is there a way to do this in a Google Spreadsheet?
18
votes
6answers
38k views

Can I add an autoincrement field to a Google Spreadsheet based on a Google Form?

Is it possible in Google Forms to give a unique value to each row it inserts into the spreadsheet as well as the timestamp?
4
votes
2answers
4k views

Add a 'creation date' value when a new line is edited

I'm not a programmer or coder, so please be gentle with me :-) I have a Google Spreadsheet that is used by different people to add tasks for my team. Here's an example of what it looks like: ...
53
votes
4answers
43k views

Embed Google Spreadsheet in Google Document

Is it possible to embed a Google Spreadsheet into a Google Document à la Microsoft Excel and Microsoft Word? How about a chart from a Spreadsheet?
36
votes
15answers
50k views

In a Google Spreadsheet, how can I force a row to be a certain height?

In a Google Doc spreadsheet I would like to have each row the same height no matter how much text is in each cell. I have tried "resize row" but no matter how many pixels I put in, the row expands ...
25
votes
4answers
70k views

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

Let's say I want Google Spreadsheets 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 ...
8
votes
1answer
36k views

Sum data based on cell color in Google Spreadsheets

I have seen this question and altough I face the same problem, the answer to that question is not helpful to my case. I have large spreadsheets which are changed frequently. So, according to the ...
9
votes
3answers
8k views

Cell reference with colour formatting

Is it possible to reference a cell in Google Spreadsheets so that cell where it gets displayed also displays it using the same text and cell colour formatting? =A1 Will only reference dell's value. ...
9
votes
5answers
22k views

How can I get Google Sheets to auto-update a reference to another sheet?

I am using an external application to enter data into a Google Spreadsheet. This workbook is then referenced by a separate workbook (with the IMPORTRANGE(spreadsheet_key, range_string) function) that ...
15
votes
2answers
6k views

Cumulative Sum without Script

For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
11
votes
4answers
13k views

How Do I Combine Columns In Google Spreadsheets? [duplicate]

I need to combine multiple columns into a single column, so: one four seven two five eight three six nine Becomes: one two three four five six seven eight nine
5
votes
1answer
18k views

Google Spreadsheet Timestamp? [duplicate]

I'm looking for some help with my Daily Work Log spreadsheet. I've search and read many different version of making a "TimeStamp" in Google spreadsheet. And it seems I don't have enough experience for ...
3
votes
1answer
2k views

How do I Sum Thoughout Different Sheets

How do I create a reference to a defined interval in several sheets? Something like this: =SUM(Sheet1:Sheet2!A1)
1
vote
1answer
348 views

Add timestamp to two different columns in Google Spreadsheets

Need help in adding another function using the below script. I would like to have a timestamp "start" and "end" on two different columns, whenever the other two separate columns are edited. Based on ...
13
votes
3answers
33k views

How do I mail merge from Google Spreadsheet to a Google Document?

With Microsoft Excel and Microsoft Word, it is easy to merge rows from a spreadsheet into pages in a Word file. This was traditionally used to make paper mailings. How can I do the same with Google ...
34
votes
4answers
70k views

Google Spreadsheet formatting a cell with wordwrap without switching modes?

How can one format a cell to have wordwrap in Google Spreadsheets? 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 ...
10
votes
1answer
45k views

'Lock' part of formula when copying from one cell to another

I have the following formula in a Google Spreadsheet: =IF(I11="VSS";G11;IF(I11="MCS";0;G11*Totais!C30)) I would like to freeze Totais!C30 when I click on bottom right of cell and drag down. ...
8
votes
3answers
7k views

Pass range in Google Apps script custom function without using A1 notation

I'm new to Google Apps script and I would like to create a function for a spreadsheet that sums cell values when the cells meet certain criteria, such as the background color. Moreover, I'd like to ...
4
votes
1answer
386 views

Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script

I'm trying to make the edit URL for a Google Form automatically populate in the response Google Sheet that it's attached to. I've seen this already and want to use it, but I'm having issues trying to ...
4
votes
4answers
152 views

Split a column of strings with a single formula returning an array

I have a column of strings like this: +---------+ | a bb | | ccc d | | ee ffff | +---------+ (exactly one space in each string), which I'd like to split by the space into two columns: ...
2
votes
1answer
3k views

I need to hide/show a group of columns in Google Sheets in a simple way

I need to hide and show in a easy way a group of columns in Google Sheets. In Excel I used a Macro with a button, but in Sheets I cannot put a button. How can I add buttons with custom functions in ...
26
votes
2answers
34k views

How to convert rows into columns in Google Spreadsheets?

I have a spreadsheet in Google Docs with two data rows and lots of columns like so: Is it possible to easily convert these rows into two columns and lots of rows (obviously retaining all ...
6
votes
3answers
10k views

Are Excel formulas and Google Spreadsheet formulas the same?

I want to be sure all my Google Spreadsheet work can be converted OK to Excel. How many of the Excel formulas match the formulas of Google spreadsheets?
3
votes
4answers
5k views

How to get the intersection of two sets

I have two lists (unique sets) in a spreadsheet and I want to get the intersection of the two. Is there an easy way to do this? Here's an example of what I want to achieve: List 1 List 2 ...
5
votes
3answers
3k views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with ...
5
votes
3answers
25k views

How to automatically insert a new row and retain functions/formulas from last row?

I have a table with cells that have functions/formulas, like this one: I need a script that creates a new row, copying with it the functions/formulas of the last used row. I find this script which ...
4
votes
2answers
3k views

Google Sheets Function to get a shortened URL (from Bit.ly or goo.gl etc.)

I have a Google spreadsheet that builds Google Analytics links. I'd like to shorten those links. I tried the Bit.ly example but even their example does not work. How can I do this?
1
vote
2answers
2k views

Relative copy conditional formatting that references other worksheet

I have a group of worksheets, one of which has 'default' values while the others should only have values if they differs from the default worksheet. To facilitate this, I want to use conditional ...
1
vote
2answers
1k views

Google spreadsheet sorting messes up cell comments

I'm running into this weird problem with Google spreadsheets: if the cells have comments on them and I sort the spreadsheet, the rows sort but the comments stay in the same position, and don't move ...
5
votes
1answer
13k views

Google spreadsheet: Importing only certain rows

Regarding Google spreadsheets, document 1 has many rows of data. How do I import into document 2 only those rows that have a certain value in column C?
29
votes
2answers
113k 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: ...
15
votes
2answers
7k views

Complete reference documentation for Google Spreadsheets formulae?

As a programmer, I find myself somewhat frustrated when trying to use Google Spreadsheets, because there seems to be no definitive description of the syntax that I can use for formulae. For instance, ...
14
votes
1answer
21k views

Google Spreadsheet: FILTER(condition OR condition) syntax?

I am using FILTER() to display a value from Sheet 2 where the Name columns match. My problem is where I want FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal"), as "OR" results ...
14
votes
1answer
7k views

Summing a column, filtered based on another column in Google Spreadsheet

I have the following data in a Google Spreadsheet: +------+---------+ | Time | Include | +------+---------+ | 0:30 | No | | 1:00 | Yes | | 0:30 | Yes | | 0:30 | No | | 0:30 | Yes ...
18
votes
3answers
29k views

Auto-updating column in Google Spreadsheet showing last modify date

Trying to figure out a way to have column two auto-update to show a timestamp of the last update. I realize the doc shows a timestamp of the last modification, but we have a lot of clients accessing ...
7
votes
5answers
27k 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 ...
12
votes
1answer
8k 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 ...
8
votes
5answers
8k views

Google Spreadsheets: how to specify entire sheet as range?

The best workaround I have found so far is: worksheet_name!$A$1:$YY but ideally I would like to be able to simply write, e.g.: worksheet_name! So, does anyone know: is there a syntax for ...
6
votes
6answers
14k views

Google Spreadsheets function to find distance between cities

I'm working with Google Docs and want to create a spreadsheet of the following form: Point A Point B Mileage Los Angeles Miami 292100 Palo Alto San Francisco ...
5
votes
1answer
579 views

Formula for smart, scale-aware number-formatting in Google Sheets? (e.g. 1,024 → 1k, 1,816,724 → 1.8M)

Any recipes out there for a "smart" number formatting formula for Google Sheets that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method? I would expect to enter a number like: ...
3
votes
2answers
318 views

Ways to learn google apps script language? [closed]

I've been looking for a good way to automate my google docs and sheets on drive, buy haven't found specific answers or even general ones I can modify to work for my specific requirements. I'm doing ...
3
votes
1answer
124 views

How to Create a Google Sheets Script for Reporting 26-Week New Highs using GOOGLEFINANCE Function?

I'm looking to use GoogleSheets to display ticker symbols and prices in real-time of stocks that have created new 26-week highs, i.e. today's highest trading price is greater than any trading price ...
2
votes
1answer
69 views

Display most recent comments from column connected to date and username in Google Sheets

In Google Sheets I have three columns: Timestamp, Username and Comment. How can I search a column for the most recent comment of a username and overwrite the most recent comment, in another cell? ...
2
votes
1answer
391 views

Updating logging sheet from another sheet doesn't work after inserting code offset

I am trying to log the data, formulas and times of cell edits from one sheet into a logging sheet. The problem I am having is that it only seems to record changes made in the logging sheet. This has ...
2
votes
1answer
4k views

Date and time stamp automatically added in column [duplicate]

We have a barcode scanner that we use to track student assignments. I am trying to create a Google Spreadsheet that tracks the assignments for each subject. I have been using the following script: ...
9
votes
2answers
8k views

How can I change the input format for dates in Google Docs' spreadsheet?

The input string for dates in Google Docs's spreadsheet app seems to be set to en_US, which is quite unnatural to me. How can I change the input format?
8
votes
2answers
4k views

Styling Text Within a Cell in Google Spreadsheets

In Excel it is possible to style text within a cell, by pressing Crtl+1. You can create for example the following sequence: 1,2,3,4. Is that possible in Google Spreadsheets?
6
votes
2answers
4k 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 ...