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 a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.

Filter by
Sorted by
Tagged with
2
votes
1answer
2k views

How to make a private edit on a shared Google Spreadsheet

I'd like to make some notes/mark some cells privately on a shared Google Spreadsheet in Google Drive. Is this possible?
2
votes
2answers
5k views

Show 'sum' when multiple cells selected in Google Spreadsheet

If I select multiple cells in a Google Spreadsheet, sum or count appears at the lowerright corner according to the data type. Sum is shown for numbers, and count is shown for characters, dates or ...
10
votes
4answers
45k views

Conditional formatting based on number of characters

In Google Spreadsheets, is there a way to conditionally format a cell if it has more than 30 characters?
0
votes
1answer
1k views

Apply conditional formatting rules to Google spreadsheet column past set limit (10)

Limit to conditional formatting appears to be 10 per column. Any work around except creating another column next to it for those additional rules or functions, then copy/paste data from one column to ...
-1
votes
1answer
2k views

Password protected online database or spreadsheet [closed]

I have a client that wants a simple online database or spreadsheet solution. Requirements Custom fields and field types (text, images) CRUD contents through a web GUI Password protected (single user ...
2
votes
1answer
2k views

Date format in spreadsheet (google doc)

I want to create spreadsheets to record the money I spend each year. Each file is named as Cost-2012, Cost-2013,...etc. Inside each file, there are in fact 12 sheets, which are named as 01, 02, ..., ...
3
votes
2answers
1k views

Google Apps Spreadheet SUMPRODUCT issue

I have a table with the headings Status | Currency | Total In the rows I have a variation of statuses and currencies. Using Google Spreadsheet I added this: =SUMPRODUCT((A1:A10="Complete"),(B1:B10=...
5
votes
3answers
6k views

Sorting Google spreadsheet by month and day but not year

I made a Google spreadsheet with a list of birth dates, anniversaries, etc. that I would like to sort by when they occur in the year, not when they first happened. If you sort by the date normally, it ...
3
votes
1answer
2k views

Add unique QR code field to a spreadsheet based on a form in google docs using Script Trigger

At the end of each row filled in, using a custom Google Form, I'm adding three more columns: Date of creation Unique ID QR code (it is a list of computers) Unique ID and QR code are unique. QR code ...
2
votes
2answers
632 views

How get last added text from column into different sheet

How can I get the last added plain text from a column in sheet1, which also has empty rows, into a cell of a sheet2? With help I managed to do this with the last added date, but with plain text I am ...
9
votes
2answers
25k views

Get last date from a column on a different sheet

I'm looking for help on the following: How do I get the last date from a column in sheet1 returned in a cell of sheet2 I tried it with: =FILTER(Sheet1!A:A ; ROW(A:A) =MAX( FILTER(ROW(A:A) ; NOT(...
2
votes
1answer
1k views

In Google Spreadsheets how do I put in a formula in after a transpose? [closed]

In my spreadsheet the transpose formula fills in a deliberately variable number of column cells, how do I fill in a cell after a transpose formula?
2
votes
1answer
3k views

Sorting on a web-published Google Spreadsheet

I have a Google Spreadsheet that is published to the web and embedded in a website. This spreadsheet has data about hotel rooms, such as hotel name, number of rooms, date in and out, etc. I would ...
19
votes
3answers
63k views

2-axis line chart in Google Sheets

I've set up a Google Sheets to compare one set of statistics against another. The statistics are for traffic vs. an action. The actions only happen once a day and not every day whereas the traffic has ...
5
votes
2answers
11k views

How to make a ratio win:total with only integers and least terms

I am trying to find a way to show a simple ratio of wins vs total games. For example: 2 wins on 5 games would give me a ratio of 2:5 5 wins on 15 games: 1:3 8 wins on 18 games: 4:9 As a nice to ...
0
votes
1answer
956 views

How to read a multiple values in cell in each row (separated by comma) in a Google Spreadsheet

I have problem in reading cell with multiple values and comparing the each value with column from a-z if match found then set value of the cell has '1'. Problem: I need to take multiple company names ...
1
vote
1answer
126 views

Adding a few lines of information to a grid

I have made out an 8 column, 21 lines, informative health chart with grid lines. I would like to add an afterthought running from left to right UNDER the grid lines that will PRINT out with the ...
17
votes
2answers
33k views

Pull CSV data from URL to Google Spreadsheet

I have a site which allows authenticated users (read: in-house users) to download certain data from the site in CSV format, e.g. http://example.com/activities.csv . Can I create a Google Spreadsheet ...
20
votes
4answers
29k 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 ...
5
votes
2answers
9k views

Filter() - referencing source array in criteria

The filter() function looks through a source array, and filters it according to condition =FILTER(A:A,ISNUMBER(A:A)). My source array is a rather long formula, so I end up repeating the same formula ...
6
votes
3answers
14k views

how do I get named ranges in Google Spreadsheets to be more reliable/current?

I'd like to pull values from a named range into a calculation. I have this function: function getNamedRange(n){ SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n); } Seems pretty simple. I ...
14
votes
3answers
76k views

How to auto increment a field

In my spreadsheet I have a column called S+ No. How to auto increment this for each row, like: S1,S2,S3,S4 In short, how to make it dynamic?
2
votes
3answers
6k views

Categorize cells in Google Spreadsheets

I'm using Google Spreadsheets to manage my shared expenses with my girlfriend. It looks like this: | Who | What | Category | When | Amount | | Gui13 | Vegetables | Meals | ...
7
votes
3answers
12k views

Compare each cell of two rows

I am working on a Google Doc spreadsheet using a not-so-simple formula. The formula works but is not as I want it. What I want to achieve is: "Compare each cell from T3 to AM3 to each cell from T2 ...
12
votes
2answers
46k views

How can I apply a script to only one specific sheet within a spreadsheet?

I have a spreadsheet with two sheets called Robin and Lucy. I've made/found/mangled a script to sort the data on the sheet each time I add some data to column A: function onEdit(event){ var sheet =...
5
votes
1answer
6k views

How to get the average of the last four values of a row

I have a spreadsheet that looks like this. It basically tracks the ratings of each show. As you can see each row as a varying number of values. What I want to be able to do is to get the avg for ...
16
votes
3answers
6k views

How do I decimal-align numbers in Google Sheets

In Google Sheets, I want to decimal-align numbers without adding trailing zeros in the fractional part. Instead of: I want: +------+ +------+ | 56.0| | 56 | | 27.0| | 27 | | 83.0| ...
10
votes
7answers
21k 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 ...
2
votes
3answers
4k views

How do I add isblank(ref) criteria to arrayformula?

This is an improvement of work from this post. I have an array formula that is similar to this image: It sums column A when the date in F matches the due date (C). What I want to do is only sum A ...
1
vote
2answers
1k views

Count columns by condition

I have a column (A) that contains a series of dates in the format mm/dd/yyyy. I want to run a count to return how many of them were in 2013. Is this possible? I tried using this formula, but it didn'...
2
votes
1answer
270 views

In a Google Sheets chart how do I prevent "Other" in the legend?

When there are over 8 series in a chart (this is true at least for a pie chart) - Sheets combines all other series above the 8th into "Other" this is really counter productive when there are only 9 ...
5
votes
4answers
42k views

COUNTIF but when multiple conditions are met

I'm trying to create a GDocs spreadsheet to collate data from a survey. I'm fairly new to the advanced features of GDocs/Excel but here's what I'd like to achieve. I've tried Googling but it's a tough ...
1
vote
2answers
5k views

How to split, trim, and sum up numbers in spreadsheet text cells?

I have a series of cells in a Google Spreadsheet containing string such as: A1 = "X5.1 Y4.3 Z2.8" B1 = "X2.5 Y1.9 Z4.8" C1 = "X6.2 Y1.9 Z3.4" I need to get the sum of all the numbers that appear ...
3
votes
1answer
3k views

How to view or edit Google Sheets offline?

I have followed the instructions to setup Offline mode access to Google Drive and it worked well. Surprisingly Google Sheets do not open on offline mode, giving an "internal error", however can open ...
20
votes
7answers
79k 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 ...
40
votes
7answers
55k views

Hot keys to switch between tabs?

Does anyone know if there´s a shortcut to switch between tabs in Google Spreadsheets? If you use CTRL+PgUp / CTRL+PgDown like in Excel you switch between Chrome or Firefox's tabs instead.
29
votes
5answers
46k views

Concatenating only filled cells

I currently have a spreadsheet to keep track of scores in a card game. There can be between two and five players. I have the following: | Players | |----------| | Dave | | Paul | | John ...
26
votes
2answers
88k views

IF OR Statements

How do you do an IF ... OR ... THEN statement in a Google spreadsheet? In PHP I would do this: if($C7 == "U.S.A." || $C7 == "Canada"){ ... How to do the same as a spreadsheet formula? At the ...
8
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)) ==&...
6
votes
2answers
5k views

Protect Formatting (Validation)

I have a very complicated tool that I developed that produces a mass amount of information. Technically, the whole sheet should be a database drive website. However, it is a spreadsheet: ...
2
votes
1answer
2k views

Calculate monthly sum() on pivote table

I have a Google Form. Upon submit following fields are populated: Timestamp Product Description Unit Price Quanitity I have generated a pivot table from this data, in a Google Spreadsheet. The ...
2
votes
1answer
335 views

Data linking between sheets

Here is my spreadsheet. What I want to happen is: I enter data in the sheets marked Head, Neck, Shoulders etc.and that data to then be put in specific areas of LFR and Normal based on what is in the ...
2
votes
1answer
3k views

Google spreadsheet sync with gCalendar

I create a GTD system based on excel, whose link I report here. The advantage of having this gtd is that I can share it with my european colleagues without the need of having a dedicated software ...
9
votes
6answers
25k views

Split content of one row into multiple rows?

I have a table where each row contains data like this: Team name | First member | Second member | Third member | Team description Would it be possible to transform/break this data so that each row ...
3
votes
3answers
7k views

How can I import Google Calendar events into a Google Spreadsheet?

I just need rows with name, date/time, and location. Ideally it should be done automatically every n hours or at every event added. I found a script solution on Stack Overflow, but is there any ...
5
votes
1answer
340 views

Auto Sort Sheet on second entry

I loved the script put forward by w0lf mostly it did the trick however I cannot figure out a way to have it sort only when I enter data in the second column of 4 columns it does it when I enter data ...
2
votes
2answers
2k views

Is there a filter delimiter in Google Spreadsheets?

I'm trying to build a list of servers in a Google Spreadsheet. Each server has multiple services, and I would like to be able to select a single service and see all the servers providing that service. ...
2
votes
1answer
10k views

Add headers to Google Spreadsheet chart [closed]

I have a chart in a spreadsheet. If I click on it and select 'advanced edit' > Start, it offers to 'Use row 12 as headers'. How can I use a different row for the header names? My data has rows for the ...
4
votes
2answers
18k views

More Efficient Filtering Script

I'm exactly one day into writing Google scripts. I've managed to write one that will filter data in a spreadsheet with 24 columns and, for now, about 900 rows. The problem is, it's slow. Any ...
5
votes
1answer
9k views

filter or sort Google Spreadsheet based on condition

I have an 8 column spreadsheet. I want to sort/filter based upon the value of the 4th column. In a separate sheet within the same spreadsheet, I want to print a list of each row matching a speific ...

1
136 137
138
139 140
146