Tagged Questions

One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (1)

0
votes
1answer
2k views

How do I get an onEdit script to run on a Google Spreadsheet for all users

I created an onEdit script which re-sorts a Google Spreadsheet every time a cell is edited. The Google Spreadsheet is shared amongst multiple users and I'd like the script to run for all users, every ...
7
votes
2answers
379 views

How to copy from Google Spreadsheet without it editing the source data

Every time I copy and paste from a Google Spreadsheet, it adds a double quote before and after everything, and duplicates every single double quote. For example, if I copy: This is a "string" of ...
2
votes
1answer
1k views

Grouping Columns in Google Spreadsheets

I have a Google Spreadsheet that has 6 or 7 columns that are all related. I would like to group them all under one header, to show this relation. Each column would additionally have its own additional ...
2
votes
1answer
217 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
1answer
359 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 ...
2
votes
3answers
1k views

Google SpreadSheet conditional formatting based on number of characters

In Google Spreadsheet, is there a way to conditionally format a cell if it has more than 30 characters?
0
votes
1answer
589 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 ...
0
votes
1answer
1k 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 ...
2
votes
1answer
762 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
1answer
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: ...
1
vote
1answer
936 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, ...
3
votes
0answers
841 views

Google Spreadsheets: 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 ...
2
votes
1answer
323 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 ...
3
votes
1answer
2k 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) ; ...
3
votes
1answer
240 views

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

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? I've created a youtube video to demonstrate (click ...
1
vote
0answers
493 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 ...
1
vote
1answer
233 views

2 axis line chart in Google Spreadsheets

I've set up a Google Spreadsheet to compare one set of stats against another. The stats are for traffic vs. an action the actions only happen once a day and not every day were as the traffic has more ...
2
votes
1answer
507 views

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

So basically 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 ...
0
votes
1answer
328 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 ...
0
votes
1answer
82 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 ...
1
vote
1answer
2k 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 ...
1
vote
3answers
1k 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: intersection of two sets
5
votes
2answers
5k views

Google Spreadsheets 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 ...
0
votes
2answers
1k 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 ...
1
vote
3answers
4k views

How to auto increment a field in Google Spreadsheet

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.
1
vote
2answers
423 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 | ...
4
votes
2answers
2k views

Google Spreadsheets formulas: 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 ...
1
vote
1answer
1k 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 = ...
2
votes
1answer
515 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 ...
8
votes
1answer
542 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| ...
5
votes
5answers
4k 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)?

Would really appreciate some help from any experts in Google Forms. 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 ...
2
votes
3answers
1k views

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

EDIT: Self-Answered 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 ...
2
votes
2answers
956 views

Count Columns by Condition in Google Drive Spreadsheet

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 ...
1
vote
0answers
89 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 ...
2
votes
3answers
8k 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
1answer
1k 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
2k views

How to view or edit Google Spreadsheet offline?

I have followed the instructions to setup Offline mode access to Google Drive and it worked well. Surprisingly Google Spreadsheets do not open on offline mode, giving an "internal error", however can ...
7
votes
3answers
15k 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 ...
2
votes
1answer
2k 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.
5
votes
2answers
2k views

Concatenating only filled cells

I currently have a spreadsheet to keep track of scores in a card game. There can be between 2 and 5 players. I have the following: | Players | |----------| | Dave | | Paul | | John | | ...
9
votes
1answer
14k 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 ...
3
votes
1answer
3k 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)) ...
3
votes
2answers
832 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: ...
0
votes
0answers
458 views

Google spreadsheets - “=ImportHtml DIV” how to?

I play Runescape and I want to make a tool that people can use to make profit in the game. My problem is that I am not sure exactly how do use a command to pull data text from a div element. The ...
2
votes
1answer
1k 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
267 views

google spreadsheet question about data linking between sheets

Here is my spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AgmD6-Z4d2aVdE9pR095OTJrVE1wS3FIZzZpVFRJMmc#gid=4 What I want to happen is I enter data in the sheets marked Head, Neck, Shoulders ...
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 ...
3
votes
2answers
3k 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 ...
1
vote
1answer
3k 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 ...
0
votes
0answers
16 views

Google Spreadsheet: reference seperate spreadsheet [duplicate]

Possible Duplicate: How do I link a cell in Google Spreadsheets to a cell in another document? Is it possible with a Google Spreadsheet to reference a cell to another spreadsheet using a ...