Questions tagged [google-sheets]

For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.

Filter by
Sorted by
Tagged with
4 votes
2 answers

How to share a Google Sheets demo spreadsheet

I find a lot of -especially new- users on the site are having difficulties on sharing a demo sheet so they can be easily helped if any at all. So, what is the right way to share a demo test sheet?
marikamitsos's user avatar
  • 12.5k
15 votes
2 answers

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 ...
Rubén's user avatar
  • 43.9k
1 vote
1 answer

Finding the longest time duration in Google Sheets

I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time. =LARGE(...
Marcus's user avatar
  • 21
203 votes
8 answers

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

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?
Jeff Yates's user avatar
  • 2,565
74 votes
8 answers

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 ...
Robert Koritnik's user avatar
22 votes
3 answers

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 ...
kirk tab's user avatar
  • 331
44 votes
3 answers

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 ...
RichGonzalez's user avatar
110 votes
14 answers

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 ...
Stefano Palazzo's user avatar
41 votes
11 answers

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?
Edward Tanguay's user avatar
16 votes
2 answers

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 ...
E.Beach's user avatar
  • 263
91 votes
10 answers

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 ...
user avatar
42 votes
6 answers

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 ...
Jacob Jan Tuinstra's user avatar
20 votes
3 answers

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 ...
Robert Koritnik's user avatar
17 votes
1 answer

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 ...
Arvind's user avatar
  • 433
21 votes
4 answers

Extract the link text and URL from a hyperlinked cell

Suppose I have a hyperlink in cell A1: =hyperlink("", "Stack Exchange") Elsewhere in the sheet, I'd like to have formulas that get the link text and URL from A1, separately. I found ...
user avatar
21 votes
7 answers

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?
Toby Allen's user avatar
8 votes
5 answers

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: +-----+-...
user avatar
5 votes
3 answers

Recalculate google sheet on demand:

I have the following formula in a sheet: =if(E1="HOLD",,query(Cust_Orders!B6:Z5000,"Select Y,G,I,H,K where H>0 ")) With E1 being a drop down with values HOLD and FETCH The recalculation when I ...
Sherwood Botsford's user avatar
4 votes
3 answers

Forms Data Manipulation In Google Sheets

I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in ...
Mr. B's user avatar
  • 2,827
2 votes
1 answer

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() ...
Charles's user avatar
  • 21
2 votes
1 answer

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 ...
Fernan's user avatar
  • 43
7 votes
3 answers

Unable to parse query string for function Query, when using IMPORTRANGE

This is the problem I'm seeing: Error - Unable to parse query string for function QUERY parameter 2: NO_COLUMND The formula is of the form: =query(importange("...", "Sheet!A:V"), "select D where ...
jas9531's user avatar
  • 107
84 votes
5 answers

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?
ale's user avatar
  • 52.1k
77 votes
15 answers

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 ...
Edward Tanguay's user avatar
17 votes
1 answer

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 ...
Weslei's user avatar
  • 1,315
13 votes
6 answers

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://...
Jonny's user avatar
  • 421
13 votes
1 answer

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 ...
nicks's user avatar
  • 141
1 vote
1 answer

Hide/Unhide rows based on values in particular cells

I am trying to Hide/Unhide rows based on values in particular cells, have the following code which works in isolation but not together. Below only makes B121 be active while B55 stops working. If i ...
user275230's user avatar
48 votes
9 answers

Stop a '+' from generating a formula

I'm having a bit of a struggle. I'm creating a spreadsheet which uses plus signs (+) regularly. I want a semi-permanent fix for +'s turning into addition formulae.
David Wheatley's user avatar
34 votes
4 answers

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 ...
Pythonista's Apprentice's user avatar
29 votes
2 answers

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 ...
agold's user avatar
  • 427
16 votes
2 answers

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 ...
Malcolm Lesley's user avatar
12 votes
4 answers

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?
offa's user avatar
  • 155
13 votes
2 answers

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 ...
ale's user avatar
  • 52.1k
9 votes
3 answers

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?
b_dev's user avatar
  • 227
7 votes
3 answers

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....
John Moffitt's user avatar
4 votes
2 answers

Conditionally format row borders

I'd like to have a medium black top border across each row whose A column cell contains text in a Google Spreadsheet. I'd also like to have the same top border across each row whose whole row is ...
gills's user avatar
  • 87
2 votes
1 answer

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 ...
JCr_BL10's user avatar
1 vote
5 answers

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 ...
user0's user avatar
  • 26k
43 votes
4 answers

Display sheet name in Google Spreadsheets

Is there any way to display a sheet name as the result of a function in a Google Spreadsheets (exactly like this question, except in Google Sheets)?
oldrobotsneverrust's user avatar
36 votes
2 answers

'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. ...
Victor Santos's user avatar
20 votes
2 answers

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?
Robert Munteanu's user avatar
20 votes
7 answers

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 ...
Bryce's user avatar
  • 696
19 votes
4 answers

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
Undistraction's user avatar
14 votes
4 answers

Lock the formula of a cell, even if the cells it references change

I have this, in D1: =sum(D3:D) I want D1 to always be the sum of the rest of column D (D2 is a header) I sometimes move a row, from eg 8 to 3, and that causes D1 to change to sum(d4:d). I want ...
Doug Cassidy's user avatar
13 votes
3 answers

Highlight all cells in a range that contain a formula in Google Sheets

I am looking to apply conditional formatting to all the cells in my sheet and highlight cells that contain a formula. I know that I can format cells based on a custom formula, and I know that I can ...
Ryan Kohn's user avatar
  • 418
10 votes
6 answers

What are the refresh characteristics of the Google Spreadsheet Import functions?

The imported data is not always up to date with the source pages when I load the spreadsheet. When this happens, F5 refresh does not help. Nor does Ctl-F5. Apparently there is some kind of caching ...
Chris Noe's user avatar
  • 219
9 votes
4 answers

An arrayformula to find the previous non-empty cell in another column

In Google Spreadsheets, without using Apps Script etc., is it possible to achieve the following with a single formula, such as arrayformula (or maybe mmult or something strange, but no fill-down) at ...
ninjagecko's user avatar
5 votes
2 answers

Google Spreadsheet: Adding formulas to Google Forms responses rows

I have a sheet with form response data in several columns, say A:G. Some of the data are numerical and I'd like to have some calculations done on them, e.g. in columns H through J. I have already ...
Ondrej's user avatar
  • 153
5 votes
2 answers

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, ...
Koen's user avatar
  • 61

2 3 4 5