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

learn more… | top users | synonyms (2)

93
votes
6answers
252k 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?
61
votes
6answers
109k 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 ...
22
votes
8answers
47k 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?
19
votes
6answers
39k 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?
10
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
1answer
38k 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 ...
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 ...
26
votes
4answers
72k 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 ...
17
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 ...
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: https://...
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
388 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 ...
53
votes
4answers
45k 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?
9
votes
6answers
23k 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 ...
12
votes
2answers
47k 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. ...
4
votes
1answer
495 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 ...
38
votes
16answers
52k 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 ...
6
votes
3answers
11k 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?
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
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 ...
7
votes
4answers
26k 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 ...
2
votes
1answer
73 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? ...
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 ...
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 ...
6
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?
16
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, ...
19
votes
3answers
30k 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 ...
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 ...
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
9k 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 ...
5
votes
3answers
4k 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
2answers
1k views

Can you do a UNION SELECT in a Google Sheet?

I've got a Google Sheet linked to a form which collects replies from three different people on the same subject using the same form and editing three times. This creates a sheet which contains three ...
5
votes
1answer
629 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: ...
4
votes
4answers
180 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: +-----+...
4
votes
2answers
4k 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?
3
votes
1answer
135 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 ...
3
votes
2answers
344 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 ...
2
votes
1answer
4k 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 ...
9
votes
2answers
9k 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?
4
votes
3answers
8k views

How can I make some data Autosort in a spreadsheet on edit?

I am working with a simple spreadsheet that has name/lastnames in it. I need for the spreadsheet to automatically sort the entry alphabetically by the last name (column B). I have used the following ...
3
votes
1answer
2k views

Google Spreadsheets Scripts Gallery menu item missing

How do we access the Scripts Gallery in Google Spreadsheets? Documentation around the web says that it should be in Tools -> Scripts Gallery. But I only see Script Manager and Script Editor. Going ...
3
votes
1answer
464 views

Google Spreadsheets: IF not working

I have problems with my IF command in Google Sheets. If gives #ERROR! all the time. See this simple test: Not even if I put in the number directly and avoid references: Am I using the IF wrong? ...
0
votes
1answer
1k views

How can I record the date/time when a new row is added?

I want to add a date value when a new row is added in Google spreadsheet. I have seen the script on Add a 'creation date' value when a new line is edited, but I was not able to modify the code to my ...
31
votes
2answers
117k 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: ...
13
votes
3answers
34k 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 ...
19
votes
13answers
38k views

How to set a Google Docs Spreadsheet cell format to bare text?

I am to store international standard phone numbers (starting with a plus sign followed, usually, by plain numbers) in a Google spreadsheet column. An example of such a phone is +420123456789 When I ...
36
votes
4answers
71k 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 ...
27
votes
2answers
35k 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 ...
18
votes
6answers
113k views

Checkbox function to automatically add to total

I'm doing my club's accounting in a Google spreadsheet. I am looking for a way to put a checkbox at the beginning of a line in order to tick off the members who paid their dues and then have a ...