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. Please consider adding a demo spreadsheet to your question, this will greatly increase the chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
177
votes
6answers
512k 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?
69
votes
9answers
131k views

How can I set conditional formatting on a particular cell that depends on another cell's value?

In my example I would like to conditionally format column B cells. Those marked with x should be formatted according to value in column A (in the example the value is 1): A | B 1 | x 2 | 3 | 1 | x ...
36
votes
3answers
84k 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 ...
31
votes
11answers
98k 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?
10
votes
1answer
26k 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 ...
1
vote
2answers
2k views

How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?

Google Sheets has several import functions IMPORTDATA IMPORTFEED IMPORTHTML IMPORTXML Sometimes the above functions returns errors like #N/A Imported content is empty and would like to be sure that ...
20
votes
6answers
61k 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?
35
votes
3answers
25k 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 ...
72
votes
16answers
96k 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 ...
11
votes
4answers
26k 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 ...
17
votes
3answers
43k views

Extract the link text and URL from a hyperlinked cell

Suppose I have a hyperlink in cell A1: =hyperlink("stackexchange.com", "Stack Exchange") Elsewhere in the sheet, I'd like to have formulas that get the link text and URL from A1, separately. I found ...
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 ...
7
votes
5answers
4k 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
4k views

Dynamic date range query

I'm trying to create two cells that will update my query: A >= date"A1" and A <= date "B1". My goal is to create a search page for the data, where the end user could insert a date or the =today() ...
57
votes
6answers
177k 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 ...
22
votes
4answers
82k 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 ...
14
votes
3answers
29k views

Cell reference with colour formatting

Is it possible to reference a cell in Google Sheets so that cell where it gets displayed also displays it using the same text and cell colour formatting? =A1 Will only reference cell's value. But if ...
14
votes
2answers
79k 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 ...
2
votes
1answer
2k views

Transpose Ranges repeated by row

I would like to convert several range of columns into rows, and keeping the association to the original col/row source Sample source sheet --------------------------------------------------- | Data ...
82
votes
5answers
90k 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?
73
votes
13answers
175k views

How do I calculate a time delta in my Google Spreadsheets?

I've got a Google Spreadsheet that looks roughly like this: Date | Start time | End time | Minutes ------------+-------------+-------------+----------- 1/11/2012 | 11:39 | 12:41 ...
6
votes
4answers
19k views

How to make Google Sheets jump to today's row when opened?

I have a sheet that has many rows to put data. Since there are too many rows, I want to have the cursor jump to a particular row when the sheet is opened. Edit not from OP: "The date column has the ...
10
votes
6answers
36k views

Insert image from Google Drive into Google Sheets

How do I insert/link to images existing in a folder in Google Drive into a Google Sheets spreadsheet? There is the image() function, I imagine using it something like this: image("googledrive://...
9
votes
1answer
10k views

In Google Sheets how do I duplicate a sheet along with its permission

In a Google Spreadsheet called Attendance there is a sheet called Template. The user duplicates this sheet, renames the sheet with current date and uses this sheet to mark attendance for students. The ...
8
votes
3answers
18k 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
2answers
4k views

How to make Google Sheet jump to today's column when opened?

I'm trying to adjust this code so that I can jump to today's date which are all across row 2 in my spreadsheet. function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss....
0
votes
4answers
382 views

multiple IF statements with between number ranges alternative

How to efectively deal with multiple IF-statements and with multiple "between number" ranges in Google Sheets' spreadsheet (e.g. C column based on B column) with a rule set as follows: 01-10 11-20 ...
29
votes
2answers
113k 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. ...
13
votes
2answers
7k 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: ...
17
votes
4answers
29k 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
11
votes
2answers
45k views

Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet

I want to bring in data from a separate sheet and filter it. I can get IMPORTRANGE to work like this: =IMPORTRANGE("URL","A:J) But that doesn't filter. I can only get FILTER to work in the same ...
4
votes
2answers
12k views

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

I have a Google Spreadsheets that is used by different people to add tasks for my team. Here's an example. People can add tasks to column B and I will assign them to the right people in the team, ...
10
votes
2answers
36k views

Convert all text to UPPERCASE in a Google Spreadsheet

I have a spreadsheet I'm sharing with others to collect some data. Each person is just (supposed to be) putting a single character in any of a range of cells. How can I cause those cells to convert ...
16
votes
2answers
19k 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?
2
votes
1answer
2k 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 ...
128
votes
2answers
248k 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?
19
votes
9answers
78k 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 ...
4
votes
1answer
17k views

Can I protect the formatting of a Google Sheets?

I’ve got a Google Sheets that has lots of formatting and formulas, in various columns, some columns are formula driven, and some columns that are user input driven. The spreadsheet is shared with ...
11
votes
3answers
15k views

Concatenate several columns into one in Google Sheets

I want to concatenate/combine columns A, B and C into column D as displayed. The number of filled entries in A, B and C will vary over time, so absolute cell addresses cannot be used. What formula ...
4
votes
2answers
14k views

How do I implement a script that creates a unique ID for a Google form submission?

I need to create a unique ID upon submission of a form which will be used as a project number for tracking. The number needs to be unique but not based on rows or other changeable data because ...
12
votes
2answers
35k views

Importing only certain rows between spreadsheet documents

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?
7
votes
3answers
11k views

Colorize a cell in Google Sheets based on cell data

I'd like to colorize a cell based on its contents. 0 = red, 100 = green, and linearly interpolated between. function LinInt(x){ var ss = SpreadsheetApp.getActiveSheet(); var cell = ss....
7
votes
2answers
29k 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 ...
11
votes
1answer
6k views

Change Google Sheets default date format with other locale

I would like to have the "dd/MM/yyyy" (e.g. 09/03/2017) as default date format for my Google sheets. I know how to do this for one sheet: Format > Number > More formats > More date and time ...
8
votes
3answers
27k 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?
4
votes
1answer
4k views

Combine two columns into a list of all possible combinations of entries

I have two columns such as: a 1 b 2 c and I need to combine them like: a 1 a 2 b 1 b 2 c 1 c 2 Is it possible with a formula?
4
votes
1answer
4k 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)
4
votes
1answer
3k views

Copying data to *and from* another sheet based on the month of a date

This is a follow-up to Copying data to another sheet based on the month of a date, with a different requirement: instead of one-way data transfer (from Master sheet to others), two-directional ...
3
votes
1answer
2k views

Use of importdata with arrayformula

I’m using importdata to retrieve Yahoo stock information, which works fine on individual rows, but when combined with arrayformula to fill down the page when additional symbols are added does not ...
0
votes
2answers
2k 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 ...