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

learn more… | top users | synonyms (2)

89
votes
6answers
222k 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?
59
votes
6answers
104k 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 ...
18
votes
6answers
34k 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
3k 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: ...
43
votes
3answers
36k 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?
33
votes
14answers
45k 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 ...
19
votes
4answers
63k 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 ...
19
votes
8answers
42k 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?
9
votes
3answers
7k 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. ...
8
votes
5answers
17k 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 ...
14
votes
2answers
5k 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 ...
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)
0
votes
1answer
235 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 ...
12
votes
3answers
30k 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
3answers
67k 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 ...
8
votes
1answer
37k 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. ...
9
votes
4answers
11k views

How Do I Combine Columns In Google Spreadsheets?

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
7
votes
3answers
6k 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
1answer
31k 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
16k views

Google Spreadsheet Timestamp?

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 ...
24
votes
2answers
32k 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 ...
5
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
3answers
4k 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 ...
1
vote
2answers
1k 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 ...
4
votes
1answer
11k 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?
25
votes
2answers
98k 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
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, ...
11
votes
1answer
17k 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 ...
12
votes
1answer
6k 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 ...
6
votes
5answers
24k 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 ...
10
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 ...
7
votes
4answers
7k 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
1answer
328 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
218 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
91 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
339 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
3k 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: ...
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
6answers
13k 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 ...
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 ...
4
votes
3answers
61 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
3answers
2k 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 ...
3
votes
1answer
1k 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
373 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? ...
3
votes
2answers
1k views

Google Spreadsheet: Finding the SUM of an array where the size of the array is dependent on a cell's contents

Here is a simplified example of what I want: sheet 1 sheet 2 The yellow highlighted areas are areas important to the formula. Essentially Column C finds the row on Sheet 2 with the same Item as ...
3
votes
2answers
5k views

Is is possible to have part of a google doc hidden publicly but able to be edited and “pushed” to LIVE?

So I have a public schedule for my business, but need to edit (which sometimes takes a few days) and then make "public"... Can I do that? I know I can "hide" but when I reopen, it's live and everyone ...
0
votes
1answer
969 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 ...
55
votes
1answer
79k 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?
9
votes
2answers
27k views

Google spreadsheet—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 ...