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

learn more… | top users | synonyms

2
votes
2answers
94 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
vote
1answer
203 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 ...
2
votes
1answer
257 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 ...
1
vote
1answer
677 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 ...
0
votes
0answers
59 views

Spreadsheet Data into a Form

I am building a simulation (a "create your own adventure") for my students in which they use a Google Form to select into which of 5 groups they would place 25 first graders (Round 1). I get the ...
2
votes
1answer
140 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
427 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
66 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", ...
2
votes
0answers
68 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 ...
3
votes
2answers
670 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: ...
0
votes
1answer
685 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 ...
1
vote
1answer
138 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
293 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 ...
2
votes
1answer
445 views

Google Sheets: 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 ...
2
votes
1answer
1k 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
63 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, ...
2
votes
1answer
257 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 ...
1
vote
0answers
227 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
145 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
119 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?
1
vote
0answers
86 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
60 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 ...
0
votes
0answers
52 views

GAS: How to change all values of a neighbor column? (code not working)

I have a Google Apps Script which I want to use to do this: get the text of all cells of column G of my sheet, extract some data from this text (dates embebed in text) and put the result in the column ...
0
votes
0answers
87 views

Prevent sorting within a strict subset of the columns

For Google Docs Spreadsheets, is there any way to prevent sorting from ever being carried out in just a strict subset of the columns, so that data in some of the columns remains stationary while data ...
2
votes
1answer
343 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
438 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 ...
0
votes
0answers
30 views

get editor name, time stamp, and date after every editing

I have a spreadsheet, on that every cell i want the name of user, new time and date on which the editing done. the formate will be "date, time stamp, and update by" Kindly suggest
1
vote
0answers
99 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
152 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
218 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 ...
4
votes
2answers
1k 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 ...
2
votes
1answer
51 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
83 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 ...
0
votes
0answers
71 views

Google Apps Script: how to get data from another sheet from the active spreadsheet? [duplicate]

I have a spreadsheet with several sheets, like this: If I want to use the active sheet in the active spreadsheet, I use this method: var s = SpreadsheetApp.getActiveSheet(); How do I use another ...
4
votes
1answer
451 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
59 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 ...
1
vote
1answer
801 views

Google Spreadsheets Data Validation - Unique Values

I have a google sheets document which contains two sets of data in separate worksheets: Public IP addresses Private IP addresses In my private IPs worksheet I want to create a dropdown which ...
3
votes
1answer
160 views

Is it possible to insert text from a Google spreadsheet into a text box in a Google document?

I need to insert text into a template that contains multiple text boxes in a variety of orientations. I can do that by inserting a text box from Google Drawing into a Google doc and formatting it to ...
2
votes
1answer
1k 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 ...
0
votes
0answers
80 views

Can any Excel spreadsheet function enact a certain $ transfer between $ accounts?

I can't yet find an Excel spreadsheet function to enact a certain transfer of money (between e.g. two Paypal or bank accounts) ; does anyone know of one? (Usage example: Of course, this function ...
6
votes
1answer
160 views

Google says “spreadsheet too large” when it isn’t

I have an XLSX spreadsheet. It has 68251 rows and 94637 cells, and it's on-disk size is 1510842 bytes. When I try to upload it, Google claims that it exceeds the 400,000 cell limit. The cell count ...
1
vote
1answer
2k views

Create a function for an entire column in Google Spreadsheets

I'd like to set a function for an entire column in Google Spreadsheets, and I'd like for that function to get applied whenever I add new rows to the spreadsheet. I know that I can set a function for ...
0
votes
0answers
135 views

Filtering data based on dropdowns in Google Forms

Is it possible for me to have a choose from a list question in a Google Form and have two dropdown boxes? Such that, the list items in the second dropdown box will depend on the item chosen in the ...
2
votes
1answer
445 views

Making all cells in a column links from the values within the cell?

I have a column of cells in a Google Spreadsheet with values such as: 512 2123 2342 3532 What I would like to do is convert all these into a hyperlink and keep the value as the link text: ...
0
votes
2answers
1k views

Counting unique values based on multiple columns

I am working in Google Spreadsheets and trying to do some counting that takes into consideration cell values across multiple cells in each row. Here's my table: |AUTHOR| |ARTICLE| ...
1
vote
2answers
442 views

GDocs: Send Email Notification Based on Timeframe and a Given Email Address From a Cell?

How specifically does the code found in this article need to be modified to allow for a specified email address column to inform who the reminder should go to? I have an example spreadsheet here with ...
3
votes
1answer
502 views

How to add multiple spaces between characters in Google Spreadsheets?

If I want 10 spaces between characters "a" and "b" I press the space bar 10 times, but when I hit "Enter the cell defaults to only displaying 2 spaces. When I click back to edit that cell the display ...
5
votes
2answers
2k 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 ...
1
vote
0answers
45 views

Save mailed Contact Forms as a new row into Google Spreadsheet?

I am using Contact Forms 7 on a website, the forms that I am using are name, checkboxes, phone number, custom message, etc. A lot of people use this form and this always require me to call them up ...
0
votes
0answers
75 views

Sending emails to specified end users

I'm trying to write a script to send emails from a google spreadsheet. My specifications are: Send email upon form submit Only send to email address listed on form I have written a sendEmail ...