We’re rewarding the question askers & reputations are being recalculated! Read more.

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
10
votes
1answer
22k views

Add a script trigger to Google Sheet that will work in Android mobile app

I have a Google sheet that includes a script that should be run when clicking a button. The recommended way to do this (as far as I can tell) is to insert an image and then attach a script to the ...
10
votes
7answers
49k views

IMPORTRANGE with preserved formatting

I am importing a certain range of data from a master sheet and would like to maintain the formatting for those cells. It seems as though this only works within a sheet: Cell reference with colour ...
10
votes
3answers
18k views

How can I alphabetize a unique result in Google Spreadsheets?

I'm generating a list based on using the UNIQUE function. How can I get the output to be alphabetized automatically?
10
votes
6answers
54k 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
2answers
49k views

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

How can I stop Google Sheets formatting a Forms response as a date?

I have a Google Forms with a question that contains a drop-down box with the following values: - Fewer than 10 - 11-25 - 26-50 - More than 50 If the user selects 11-25, Google Sheets renders the ...
10
votes
2answers
18k views

How can I find duplicates in a column, using formulas?

Has anybody come up with a formula to find duplicates in a column? For example: a b c d b d =somefunction(A1:A6) Output: b d I know there is a =unique() function, it's basically a =not_unique() ...
10
votes
2answers
13k views

AutoFill dates but skip weekends

I need to auto fill the some dates which is easy and I know how to do. However, I do not know how to do this skipping the weekends. I want it to auto fill skipping weekends and jumping to Monday.
10
votes
8answers
50k views

How to make a query with Google Spreadsheet, that checks for a date

I have a range that goes from B4:H124. All the cells in column B are dates. I would like to retrieve the data from a row (B:H), where the DATE equals to the content of a specific cell, lets say Q4. ...
10
votes
1answer
28k 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 ...
10
votes
1answer
5k views

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 ...
10
votes
2answers
14k views

In Google Spreadsheet, how do I make cells take a random string value from a list of strings?

Suppose I have a list of strings: apple, pear, banana, orange How can I have each cell in a column of cells have a random value from that list?
10
votes
1answer
65k views

Find cell with highest value of a cell range

I have a row of percent values. In the last of the row (Column E) I want the text to appear, that is in the very first row above the values: A B C D E ------------------------...
10
votes
3answers
11k 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 ...
10
votes
4answers
17k views

Having units in Google Spreadsheets?

I want to be able to input plain numbers like 200, or 3000 and have the cell display 200g, 3kg, etc... But still be able to add the values in the cells. So if I add the two above I'd get 3.2kg on the ...
10
votes
10answers
50k views

How to force a page break in Google Spreadsheets

I want to force a page break after a certain row of a sheet. Is there some analogue of Google Docs’ (and Excel’s) “insert page break” for Spreadsheets? If there is no “native” way to do this, maybe ...
10
votes
4answers
5k views

“Split” delimited string, preserving blank entries

I am trying to use the SPLIT and JOIN functions in Google Sheets (the new version) to manipulate some data, and I am running into problems because SPLIT is not preserving blank entries. The data ...
10
votes
2answers
2k views

How to do a simple set operation in Google docs spreadsheet?

Suppose I have some names in cells B1:B3, and a full list of all the names in A1:A4, is it possible for me to generate a list of names in A1:A4 that don't appear in B1:B3? E.g. Cells A1:A4 have "A", "...
10
votes
1answer
5k views

How do you refresh a filter view in Google Sheets?

Right now it seems like the easiest way to refresh a filter view in google sheets is to remove the filter view and then apply it again. What is the fastest way to refresh a filter view in Google ...
10
votes
2answers
13k views

Can you specify qualifier and delimiter characters when exporting from Google Spreadsheet?

Can you specify qualifier and delimiter characters when exporting from Google Docs? Right now, when I go to a spreadsheet and do File » Download As, it downloads something that is comma delimited and ...
10
votes
7answers
19k views

Is there a way to link a “Choose from a list” style question in a Google Form to a list of data (e.g. in a spreadsheet)?

Is there a way to link a "Choose from a list" style question in a form to a list of data (e.g. in a spreadsheet)? This would mean that as new options were added to the list in the spreadsheet (or ...
10
votes
2answers
6k views

Script to change individual text color within a cell

I have a Google spreadsheet with hundreds of employee names for a schedule. Each cell has multiple names within that cell that look like this: First Last First Last First Last First Last First Last ...
9
votes
5answers
28k views

How can I refer to two different columns in a COUNTIF function?

I'll try my best to explain. Say I've got a sheet that looks like this (in this contrived example, let's say I'm sending marketing emails to various people, I know their ages and zip codes, and I want ...
9
votes
2answers
20k views

Adding one column to a string to dynamically create a URL in the next column

I have a spreadsheet of product ID codes that I'd like to turn into links, and from everything I've read, the concat or concatenate functions in Google Sheets should work for that, but so far ...
9
votes
8answers
40k views

How to lock/protect cells in Google Spreadsheets

I created very simple spreadsheet application in Google Spreadsheet for my mom. But I really need to lock/protect the cell with formulas so she cannot edit them. Every time I told her to try new ...
9
votes
3answers
10k views

How do I prepend a string to a column of cells in Google Spreadsheets?

I imagine this is laughably simple but I just don't know what to do. I have a column (A) of cells with strings in them and I want to simply prepend each string in every cell with a web address: A ---...
9
votes
3answers
17k views

How to interpolate data in a range in Google Sheets

I have an array with data: X Y 3 50 5 60 9 120 11 130 18 90 20 150 The data is entirely non-linear. X is guaranteed to be sorted. Now for any given value,...
9
votes
1answer
23k views

In Google Spreadshets, how can you loop through all available sheets not knowing the total count?

I'm keeping stats for my daughter's baseball team. I have a sheet for each game, each one identical in structure. I want to create a summary sheet that totals numbers from the other sheets. How ...
9
votes
2answers
41k views

Basic date manipulation in Google script

I would like to define a custom function in Google Sheet. This function should do something very simple, yet I have not managed to find how to do it. I am very confused with all the answers I read ...
9
votes
7answers
9k views

Keyboard shortcut to open a drop-down list for cell values in Google Docs spreadsheets

I'm working in a Google Docs spreadsheet where I've set up validation lists for a column. When I tab into that column as I'm editing a row, the drop-down handle appears: In order to access values ...
9
votes
5answers
11k views

Change date format [YYYYMMDD] to [MM/DD/YYYY]

I generated a report that formats date as 20160509 and my Google Sheets locale is already set to United States. I've played around with various options within Format > Number > Date Format but I can't ...
9
votes
4answers
29k views

How to limit the length of data in a cell in Google Sheets?

Is it possible to put a limit to the length of cell content while entering data into a cell using Google Apps Script or any other way?
9
votes
2answers
12k views

What is the equivalent key in Google Spreadsheet to Excel's F2 for editing a cell?

In Excel When you want to edit the highlighted cell, and you don't want to move back to the previous cell when pressing the <- key, you press F2. Pressing F2 ensures that when you're editing the ...
9
votes
1answer
13k views

How to sum only visible cells on Google Sheets?

As per question title, how to sum only the visible cells on Google Spreadsheets?
9
votes
3answers
10k views

Can you link directly to a new copy of a Google Docs or Sheets?

Is it possible to craft a URL (or bookmarklet) so that when clicked it opens a fresh "copy" of an existing Google Docs or Sheets? Apparently it used to be possible by appending &newcopy=true to ...
9
votes
5answers
36k views

Count the number of words in a string in Google Sheets

I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?
9
votes
1answer
17k views

IF function in Google Spreadsheets displays Parse error, what is wrong?

What is wrong with this code? = IF (F3+E3+D3 > 0, F3+E3+D3, 0) I want the cell to contain the sum only if it is positive. If it is negative I want zero to be there. Google Spreadsheet gives me ...
9
votes
2answers
9k views

Google Spreadsheet Sort Two Columns

So I've found the following answer in regards to auto-sorting by one column, which works great How can I make some data on a Google Spreadsheet auto-sorting? However, my use case requires the sorting ...
9
votes
2answers
37k views

Inserting a New Sheet in a Google Spreadsheet

How can I insert a new blank sheet (a new tab) into an existing Google Spreadsheet? I'm able to duplicate those that already exist on the bottom, but I can't find the menu option to insert a sheet. ...
9
votes
1answer
6k views

How to see which formulas reference a given cell in Google Sheets?

Is there a way to see which cells reference a given cell in their formulas, in Google Sheets? For example, if cell C1 contains =SUM(A1:A5), and cell D1 contains =SUM(A5:A8), I'd like to somehow be ...
9
votes
1answer
18k views

Min/Max of a row only if value in other row is X

I'm a bit vague as to what I am looking for/asking about, but here is an example: A | B 22 | 5 15 | 5 2 | 10 19 | 10 5 | 10 =min(A2:A6, if(B2:B6=5, 5)) ==> 15 =min(A2:A6, if(B2:B6=5, 10)) ==&...
9
votes
2answers
5k views

Where does the calculation take place?

In Google Sheets I have quite a large number of relatively complex array formulae in many cells that takes ages to complete calculating. Does the speed of calculation depend on my PC resources, or ...
9
votes
1answer
4k views

How to re-use Google Apps Scripts in new Google Spreadsheet

I'm having an issue. Whenever I create a new spreadsheet, I'm not able to add a script I've already created. The list for recent projects or scripts is always blank. Anyone have a resolution or do I ...
9
votes
4answers
5k views

Is it possible to use named ranges in Google spreadsheet queries so that the columns references are kept up to date?

Using Google Spreadsheets, you can write queries. However, if you have column letters in quotes, then they aren't updated as column order changes. Is there a way to write these queries so they don't ...
9
votes
1answer
22k views

How to use ARRAYFORMULA with QUERY in Google Sheets

I have a query that needs to be run for every row in a list. It works nicely when written for a single row: QUERY(MaraRankData, "select J,I,H,G,F,E where A='"&E3&"' and B='"&...
9
votes
3answers
13k 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 ...
9
votes
2answers
7k views

How to left align currency symbol but right align number?

How to left align dollar sign and right align the number, like Excel currency format? For example: $ 3,546 $ 44,345 Also, is there a way to create a custom format?
9
votes
3answers
10k views

Compare two lists and display missing items in a Google Spreadsheet

I'm trying to find a solution to compare two separate lists (invited - attended) and create a 3rd list (absent). The function must look at the key in "Attended List (D,E,F)" and compare it with the ...
9
votes
1answer
12k 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 ...
9
votes
1answer
16k views

Google Sheets auto-format the highest value in a row of cells

I have a set of data like so: A|B|C|D 1 9|3|5|4 2 7|6|6|6 3 3|6|2|7 4 6|9|4|7 And I would like to auto-format the highest value in each row. So, for instance in row 1, A1 would get the auto-format,...