A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

3
votes
1answer
1k views

How to lock a cell based on date in Google Spreadsheets

I have set up a simple spreadsheet with cells containing a drop down list of Yes and No. I want to lock these cells based on date, so that all the people who I am sharing the sheet with will not be ...
0
votes
2answers
126 views

Spreadsheet: function to watch row for changes

Is there a spreadsheet function that can watch a row and update a cell with the current date when any cell in that row changes? If not for the entire row then perhaps just watch a single cell?
6
votes
2answers
606 views

Custom data transpose?

I have an export for a group that composed of student/mentors. Everyone in the group is both a student and a mentor. I need to transform the data to have mentors with assigned students rather than ...
3
votes
1answer
540 views

How to anchor all the cells on the page in Google Spreadsheet

To anchor cells in a Google spreadsheet so that you can drag the values/cells/formula, with one or more of the linked cells/values remaining constant you can follow these steps: http://productforums....
3
votes
1answer
2k views

Get Row-Number of a MAX(Range) Value?

I have a spreadsheet with several items, their volumes and margins. I have no problem in finding the maximum margin with =MAX(Range). What I need to find is the row number of that value. Let's say I ...
2
votes
1answer
44 views

Can I make modifiable part of the web address passed through in a Google Spreadsheets =ImportHtml function?

Wondering if there is a way to modify part of the web address (passed through in double quotes) in a =ImportHtml function in Google Spreadsheets? eg. www.schoolgrades.com/tom www.schoolgrades.com/...
1
vote
1answer
1k views

How to use triggers with functions that require parameters from the spreadsheet?

I found a simple and great solution to retrieve JSON into a Google spreadsheet. I want the function ImportJSON to update regularly via a trigger (event or time-based, I still haven't decided what I ...
2
votes
1answer
8k views

Google Spreadsheet - How to filter, SUM and display multiple values with multiple conditions added daily?

I have a spreadsheet that collects dozens of payments made daily through different means. For example, utilities bills, credit cards bills, personnel fees, expenses, etc. They are made in cash, by ...
8
votes
2answers
13k views

Count the number of words in a string in Google Spreadsheet

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-build Google spreadsheet function I may use?
0
votes
2answers
338 views

Calculating time worked per day

How do I add time like 8:20am-12:00pm and then after lunch break 12:30pm-5:02pm to end of day to get hours worked per day? I was using =(B2-A2*24)+(D2-A2*24) but that doesn't work
3
votes
1answer
3k views

How can you add a conditional statement to a =QUERY function

How can I add a conditional statement (similar to an if elseif in .php) to the query listed below so that is will return columns D and E from Sheet2 if column C in the responses sheet == 1st or ...
4
votes
2answers
2k views

How do I automatically expand a =QUERY when receiving submissions from a Google Form?

The Query below 'works' on a line by line basis (when manually filled down), but it does not 'autofill' down when new data is added to the 'responses sheet'. The data being added to the 'responses ...
0
votes
1answer
1k views

Generating charts from Google Form Data via Google Spreadsheets

I have two colums that are generated from a google form: I would like to create two pie charts, one for City A and one for City B, showing the percentages of male and females from those cities. I ...
6
votes
1answer
3k views

Publish spreadsheet as Atom/RSS not possible in Google Apps for Business

I am trying to publish a Google spreadsheet as an RSS feed using Google Chrome. When I go to File → Publish to web and then go to Get a link to the published data, the option Atom/RSS feed is not ...
1
vote
1answer
126 views

Is there a way to count how many entries in a given column have a particular value?

I have a spreadsheet which keeps track of duels between two players in a game. Each row has the number of the match and the winner, given by their initial, X or Y. I want to know if it's possible to ...
1
vote
0answers
61 views

Adding and deleting sheets in Google Spreadsheets

How can I restrict users from adding new and deleting existing sheets in Google Spreadsheets? Only the owner of the spreadsheet should be able to do that. Other users should not be able to do that.
1
vote
0answers
18 views

Find out the folder path of the current doc in Google Docs [duplicate]

I am able to search a file and open it. But I am not able to locate the folder which has this file. There is not Properties in the File menu.
2
votes
1answer
645 views

Change contents of cell based on date range in Google spreadsheet

I’m trying to get a cell to change its value based on a date range. Let's say I have this list: A B date1 10 date2 25 date3 50 date4 85 On date1 I will have 10 items, date2 I ...
1
vote
1answer
161 views

How do I copy rows with certain criteria from multiple spreadsheets into one spreadsheet

I made a Google Form for assigning a task to myself or a colleague. For Example: spreadsheet name: Science, Column 1=Name 2=task 3=due date spreadsheet name: Mathematics, Column 1=Name 2=task ...
2
votes
2answers
167 views

How to do sum(max(…))?

Here is my raw data: Site IP Visits Age (in hours) 1 uu.com 1.2.3.4 10 3 2 vv.com 2.9.7.5 2 4 3 kk.com 2.9.7.5 8 24 4 uu.com 1.2....
1
vote
1answer
719 views

Index-Match on Arrayformula

I'm making a database in google drive. In one column I have abbreviations of a number of departments someone is in, but people can be linked to multiple departments. The abbreviations are comma-...
2
votes
1answer
2k views

How do I prevent that references in my formula change when I copy the formula to a new cell?

I'm trying to use submitted Google Form data in one spreadsheet to locate and retrieve data from another spreadsheet (tab in same document). The Google Spreadsheet file is here. The =QUERY statement ...
2
votes
1answer
2k views

Creating duplicate Google spreadsheets that filter data from a master sheet and update rows on master sheet

Basically, I have a huge list of data on Google Docs spreadsheets, and a bunch of people that read it. They are not tech savvy. I am trying to create duplicate sheets on a Google spreadsheet that are ...
2
votes
1answer
340 views

IF statement with ranges

How do I make it so that if any number in the range C6:D11 on PAGE is less than zero, output "Some words" and if not, output "Some other words"? I tried using =IF(PAGE!C6:D11 < 0, "Some words", "...
2
votes
2answers
1k views

How to automatically add spaces in between each character in a text string?

Teachers I work with enter 4 scores for a student’s assessment in a single cell, like this: 2113, with no breaks or spaces. How can I separate those characters so as to be able to create a chart of ...
0
votes
1answer
81 views

What is the correct syntax for the IF function?

I'm trying to make a cell output "Some words" if the number cell in B10 in a page named "Page" is greater than or equal to 3. If not, it should output "Some other words". I tried =IF("'PAGE'!B10≤3", "...
1
vote
0answers
166 views

Importing only certain rows

I am attempting to create a spreadsheet based off another spreadsheet. I only wish to import certain rows based on the second column of the first spreadsheet. If that column contains certain users ...
4
votes
2answers
4k views

Add a 'creation date' value when a new line is edited

I'm not a programmer or coder, so please be gentle with me :-) I have a Google Spreadsheet that is used by different people to add tasks for my team. Here's an example of what it looks like: https://...
1
vote
1answer
1k views

Script error trying to populate form drop down menu from spreadsheet

We are trying to link a list on a Google Spreadsheet to a drop down menu on a Google Form. The twist is that this Google Spreadsheet list will be continuously updated and needs to automatically ...
3
votes
1answer
863 views

How to set permissions for all collaborators in a protected range, or set default permissions?

I find the "protect range" function in Google Spreadsheets very useful, but I have one problem: I often need to set all of the collaborators' permissions to, for example, "Can comment." It is very ...
1
vote
1answer
1k views

Google Spreadsheet: Count values from last 30 days

Here is a sample spreadsheet you can view and edit: https://docs.google.com/spreadsheet/ccc?key=0AnWwBCPp8kISdGc4bExyYV8xOTVRbnRkaXNQSzBWalE&usp=sharing. How can I count the number of successes ...
1
vote
1answer
2k views

Insert column across multiple sheets

I have a Google spreadsheet with multiple sheets. All the sheets have the same header row, but different data (it's a month by month look at some stats). Is there a way to insert a column on one ...
4
votes
1answer
5k views

Count the number of non-blanks that have unique value in other column

I have a large spreadsheet of customer data. I've set up a sample spreadsheet with some simple fake data. You can view the sample data (with edit privileges). I need a formula to be able to count ...
0
votes
1answer
67 views

Can't figure out SUM formula

This is the formula I am trying to do: =SUM(D9:D14)*2.55, (D16:D23)*3.90, (D25:D32)*6.40, (D34:D41)*10.60, (D43:D46)*10.50, (D48:D51)*6, (D53:D59)*3, (D61:D64)*1.8,(D66:...
2
votes
1answer
988 views

Google Spreadsheet not loading

I have a Google Spreadsheet with a lot of very important data and some scripts that where working well. At some point, the browser crash and I reload the page. After that, I can't access that (only ...
3
votes
2answers
594 views

Google form to spreadsheet, create new column

I created a Google form and a spreadsheet associated that form. On the spreadsheet there are two columns by default. A is Timestamp and B is Untitled question. Now I want to create another column in C ...
2
votes
1answer
1k views

Get YEAR() and MONTH() from a string like “September 2013” in Google spreadsheet

I have a sheet with column A listing the month of the year in human readable format e.g. "December 2013", and column B listing a calculated value of bills issued in that month (from another sheet). I ...
1
vote
1answer
408 views

How to convert USD to NIS in Google Spreadsheets?

I need a function that converts American Dollars to New Israeli Shekels in a Google Spreadsheet. Is there such a function, or a viable workaround?
2
votes
1answer
271 views

Flatten (pivot) table into a three column array on a Google Spreadsheet

I have a table which looks like this: A B C D 1 red blue green white 2 blue red white yellow 3 red blue green white 4 red blue green white and would like to get a ...
1
vote
1answer
63 views

Add columns content if the header is selected as 1 or 0

Trying to add across horizontally - if the column header is selected as being 1 - then the values of the column are to be added to the adjacent one that has 1 as a header. Ff the column header is ...
3
votes
2answers
2k views

How to do a Google Spreadsheet equivalent to SQL's JOIN and SUM( )?

I have this table: | A | B | |---------|-----| | grocery | $10 | | clothes | $40 | | grocery | $19 | etc. So I'd like to have a column with an aggregation of the categories like grocery ...
2
votes
0answers
759 views

Apps Script Webapp accessing Google Spreadsheet gets #VALUE! on every cell

I have a Google Spreadsheet with a couple of sheets that get data from other spreadsheets via ImportRange function and then calculate numeric values based on that data. Then, I assembled a code ...
1
vote
0answers
326 views

setDataValidation in google script not working

Have a problem with setting data validation rules in a google spreadsheet using apps script. Below are two functions, one is an onChange function which will be triggered whenever a user changes the ...
2
votes
1answer
529 views

Error using “sumif” together with “address” function

Please see the data below: What I want to do is to write a formula in column D to sum up the order quantity (column C) starting from the row no. specified in column F until current row, for the ...
4
votes
1answer
3k views

Google Spreadsheet: How to calculate the next Monday after a given date?

I am looking for a formula that can look at a cell value that contains a date and return the first Monday to occur after that date. Examples (MM/dd/yyyy format): 10/22/2013 (Tuesday) would return 10/...
4
votes
2answers
7k views

How do I implement a script that creates a unique ID for a Google form submission?

I need to create a unique ID upon submission of a form which will be used as a project number for tracking. The number needs to be unique but not based on rows or other changeable data because ...
3
votes
1answer
72 views

Repeating functions inside “if”

I was creating a spreadsheet the other day and I stumbled across the following "problem". I wanted to test the result of a function against a value and if that was true, display the result of the ...
2
votes
1answer
180 views

Geocode in Google spreadsheets, keep results

I have a form for internships and I am passing over the address of the internship provider to a function which gives me latitude and longitude for it, which is then consumed by a webmap using tabletop ...
5
votes
1answer
1k views

Auto-sort column Google Spreadsheet on incoming Google Form response

How can I get the Google Spreadsheet to automatically order the incoming form responses alphabetically (2nd column)? I know how to do this manually or by going in and editing a cell, but cannot get ...
1
vote
1answer
68 views

Finding the most important contributor to a column in Google Spreadsheet

I have a column in a google spreadsheet that's calculated as a function of a few other variables (columns). Is there a function that I can use to find which of the variables has the most influence ...