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
3
votes
1answer
2k 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
78k 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 ...
38
votes
7answers
52k 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.
24
votes
5answers
35k 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
86k 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 ...
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)) ==&...
5
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
332 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 ...
7
votes
5answers
22k 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 ...
2
votes
2answers
6k 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
307 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
9k 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
17k 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
8k 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
vote
1answer
4k views

SPLIT function in Google Spreadsheets with OR statement

I want to use the split function, but with different delimiters. I thought of something like this: =SPLIT(A:A;OR("-","&")) but it doesn't work. Is it possible to have Boolean statements within a ...
2
votes
1answer
807 views

Script gives 1899 for the timestamp year. How to fix it?

The following code was almost exactly what I was looking for to timestamp the adjacent cell after being edited. However, I need to incorporate the currnet date as well as time. The code below gives a ...
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 ...
2
votes
1answer
11k views

How do I use the INDEX or OFFSET functions in Google Spreadsheets?

I have my spreadsheet set up as follows: first 10 columns full of descriptive data followed by 24 columns of month data (2 columns per month). The last 24 columns are static and the values are ...
7
votes
3answers
43k views

How to distinguish Empty cells from Zero

I want to count a number of specific responses from a form where the response can be left empty, zero or any other number 'd'. The following code: var data = sheet.getRange("B2:L").getValues(); var ...
0
votes
3answers
8k views

How does one insert a new line in a cell in a Google doc spreadsheet? [duplicate]

I saw a question with this earlier but nobody seems to have it answered in a way that helps me. I need to paste text into a Google Docs spreadsheet cell but it needs to have two new lines in it. ...
3
votes
1answer
3k views

Sorting a protected range in Google spreadsheets

I've been trying to make the spreadsheet automatically sort some data, and after some searching I found this post: Automatic sorting. It works just perfect with my permissions as the owner, but when ...
1
vote
1answer
203 views

Automatically add values before and after a value in a Google spreadsheet

I have a library of flags online and would like to have a client-foolproof solution for adding flags. Right now my function looks like this: =image("http://www.website.com/flags/us.png", 3) But I ...
1
vote
1answer
5k views

Set the headers in a Google Drive spreadsheet to move on screen as I scroll down

How do I set the headers in a Google Drive spreadsheet to move on screen as I scroll down the spreadsheet?
0
votes
2answers
5k views

Query multiple Google spreadsheets

I have the following sheets: ListA ListB ListC Note: at each sheet I have a column which contains YES or NO. How do I query all my lists (spreadsheets) where myBooleanColumn equals to YES/NO?
1
vote
1answer
750 views

How to make a query that returns rows if deadline is two weeks / one month / two months from today, etc

I have a spreadsheet that contains several columns with project information. I want to modify: =QUERY( 'Compilation'!A2:L; " select A, B, F, I, L order by A" ; 0 ) so it returns values in L that ...
3
votes
2answers
10k views

Counting minutes/seconds

I am using format-value-time to have cells where I input minutes/seconds, like 13:45 for 13 minutes & 45 seconds. If I have several of these cells, how can I add them all up? Example: Cell1: 00....
6
votes
3answers
23k views

Auto date insert when opposite cell is populated

I am creating a spreadsheet which will contain records of my daily expenditure. Something like the following: A | B ---------- 0 | 50.04 0 | 20.00 0 | 19.99 0 | 2.40 What I am trying to achieve ...
6
votes
4answers
22k views

How can I make some data Autosort in a spreadsheet on edit?

I am working with a simple spreadsheet that has name/lastnames in it. I need for the spreadsheet to automatically sort the entry alphabetically by the last name (column B). I have used the following ...
4
votes
2answers
6k views

Get rid of row in result of Google Spreadsheets query

I use the following formula in my Google Spreadsheet: QUERY(Inkomster!A2:G;"SELECT SUM(C) WHERE G="&B3&" AND F='"&A3&"'") This returns the following (On two different rows that ...
0
votes
1answer
599 views

Get Published Google Doc in JSON

I have a published Google Doc at https://docs.google.com/spreadsheet/pub?key=0AtjON-ccqVTgdDhKeGx0UlZpUWUyLTFZV2szQlJyNnc&output=html that I want to retrieve in JSON. I've been trying this but it’...
3
votes
1answer
2k views

How can we view the whole spreadsheet instead of just the form in Google forms?

I have been given a link to a Google docs form where I was supposed to enter some data. The URI looks something like this: https://docs.google.com/spreadsheet/formResponse?formkey=foo Is there ...
1
vote
1answer
8k views

Google Docs Spreadsheet - Parse Error

Not sure why this formula does not work. I just want the value to be zero if 60-D15 is negative =MAX(60-D15,0) also tried =MAX(D15,0) =MAXA(D15,0) Any clues?
37
votes
4answers
29k 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 ...
1
vote
1answer
260 views

Transpose an address list to rows

I have a good old list with addresses: Because I want to re-arrange the list (and make it more 2013), I need to transpose the list like this: I've tried to use the TRANSPOSE formula, but that doesn'...
8
votes
2answers
9k 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?
-1
votes
1answer
329 views

Filter function overwrites formula with '--'

I am using the filter formula below. The formula works fine the first time, but once it filters the result properly, it corrupts the same formula in next row below with --. =iferror(FILTER(...
6
votes
2answers
3k views

Count Occurrences of Words

I'm having the following set of data: A simple COUNTA would yield n=4. Using the above-mentioned formula will give also n=4. The answer needs to be however n=6: Jacob Jan (1x), Jacob (2x), Jan (3x) ...
1
vote
1answer
2k views

Selecting a sequence of sheets in Google Docs / Spreadsheets

I have a Google spreadsheet where each sheet is a student group, say, Group1, Group2, Group3 etc. Each sheet is organized identically. I would like to summarize some information from all sheets into ...
-1
votes
1answer
1k views

In Google Sheets, how can I fit the data to the cell? [closed]

In some cells on my sheet the data fits the cell. On some rows there is a lot of extra space after the data in each cell. I have tried deleting after at the end of the data, but it does not help. ...
1
vote
4answers
2k views

How can I convert a number according to a range?

I'm working with Google Apps (spreadsheet) and I need to convert results (ranging from 0 to 36) to a maximum of 7 according to the following table. if score is (0-5) result should be 1 if score ...
0
votes
3answers
3k views

Editing of a VLOOKUP formula that references a pivot table

After inserting a VLOOKUP formula in a Google spreadsheet, where the array parameter references columns of a pivot table, how do I edit the formula again? When I enter the formula, it just displays ...
4
votes
2answers
5k views

How to add a custom font size to Google Spreadsheets dropdown menu?

How do I add a custom font size for a Google Spreadsheet dropdown menu default values? There are some defaults like 14pt, 18pt, 24pt and 36pt, but I would like to have a font size like 30pt, is it ...
4
votes
2answers
5k views

Which of the concatenation options are the best to use?

I want to accomplish the following: string = TEST; cell reference = B2, value = ING; RESULT = TESTING Which of the following options are the best to use?: CONCAT: =CONCAT("TEST",B2) & ...
42
votes
3answers
96k 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 ...
3
votes
2answers
145 views

Referencing a Range Name from a different spreadsheet [duplicate]

I would like to create a spreadsheet that contains name, phone number, address and email address of many individuals. I will define the data as a range named "Index". Then I would like to create a ...
3
votes
3answers
4k views

How to apply a function to all the rows preceeding the current one in an arrayformula?

So I have the following situation: In the first column I have certain values, in the second column, given a certain row, I want it to contain the result of a function applied to all the preceeding ...
1
vote
2answers
96 views

Formula to return time difference but not display 0

I have a Google Sheets with start (C) and end (D) timestamps. I am calculating the time difference using =(D1-C1). I have copied the formula to empty columns which returns a 0 time difference. Is ...

1
117 118
119
120 121
126