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

learn more… | top users | synonyms (1)

2
votes
1answer
15 views

SUM a column of values that whenever a cell is empty gets it from a default column

I am trying to sum a column of values but whenever a cell is empty then it sums the value from a default column. Example: +---------+---------+---------+---------+ | Default | A | B | ...
2
votes
0answers
23 views

Can I fill specific cells in my spreadsheet when submitting a Google Form?

I have a Google Spreadsheet that contains a list of dates, and empty cells. For a variety of reasons, I'd like to be able to have people add fill out a Google Form where they select a date, and fill ...
1
vote
1answer
17 views

How can I sum a row in Google Spreadsheet so that the formula isn't overwritten when Google Forms adds a new row?

I'm using Google Forms to collect some data from students. Each time a new student submits a form, the data (4 columns worth) is added to a new row in a Google Spreadsheets, bumping anything ...
-1
votes
1answer
23 views

How can I calculate compound interest with different interest rates?

Assume that I have the following different interest rates for different periods. I would like to calculate a compound interest rate based on these rates for lets say 200 periods. How can I do it ...
0
votes
1answer
25 views

Google Spreadsheet changing cell references on its own

I have created a Google Form with an attached spreadsheet. Within that spreadsheet I have created several sheets to sort the answers to my liking. Sheet 2 is just an exact copy of all the original ...
1
vote
1answer
37 views

Start new line automatically when otherwise content would overflow cell [duplicate]

I don't know when Google changed the version of Spreadsheets. Right now when the cell's text is longer than the cell width the display of this line is no longer what it used to be. I want the display ...
0
votes
1answer
13 views

Google-speadsheet data validation to restrict to Mondays only

I have a field in Google Sheets I labeled "Week of". I want to restrict user input to a date (mm/dd/yyyy) which is a Monday. I can set "Data validation" -> "criteria" to "Date" but how do I limit it ...
2
votes
2answers
44 views

How can I get Google Sheets to treat my form-submitted strings as plain text and preserve leading zeros?

I have Google Forms where users enter information, some of which contains leading zeros (e.g. zip codes, ID numbers). However, when this data is saved to the Google Sheet, it doesn't treat it as ...
-1
votes
1answer
76 views

Google Spreadsheet: add automatically the same formula in each new row of a column. Also with “SUM”

I have this sheet: https://docs.google.com/spreadsheets/d/10YVUO2a-XftaCOX73tOiOwQfu0t80iKPJjQrRDiht1A/edit?usp=sharing In the column "E" I need the same operation as ARRAYFORMULA in column C and D. ...
1
vote
2answers
40 views

query all dates within the date range

I have a Google Spreadsheet that I'm querying and the date is set as dd/mm/yyyy. How can I query between the start and finish (ref cells) of each month, so the query only returns dates between the ...
0
votes
4answers
36 views

How can I append a string to a formula result?

How can I include the result of an equation followed by a piece of information in quotes? For instance if I had the code add numbers from three cells, and wanted to include the result followed by lbs ...
1
vote
1answer
43 views

How do I import text file as-is to Google spreadsheet?

I'm importing a text file with some columns being 000000, 0000, 000000000. Google Docs converts them all to 0 - treating them as numbers. How do I make the columns contains exactly the string that was ...
1
vote
3answers
37 views

How to handle Negative Time Delta in Google Spreadsheets

If I have two cells, one is the starting time and one is the ending time. How do I calculate the time difference if the end time is in the next day? = B1 - A1 gives a negative time. =IF(B1-A1, ...
0
votes
0answers
19 views

Google Docs equation for adding rolling dates and two different column

I'm a pilot that sometimes flies a plane with a single engine and other times I fly a plane with more the one engine. What I would like to do is have an equation that will take these two columns and ...
1
vote
1answer
33 views

Formatting in Google Spreadsheets

I am trying to format in Google Spreadsheets and am unsure how to do the following: If column G is equal to “Yes” then it will format the entire row by changing to font to grey, italic and strike ...
2
votes
3answers
71 views

Code invoked too many times

My goal is pretty simple: take the value in column U and check if it's within particular ranges. If it's 1–100, put a 1 in column A. If it's 101–200, put a 2 in column A. The problem is, I have almost ...
1
vote
1answer
56 views

Have a range refer to a given row even if the row number changes?

I have a table like so: A | B ------------------ 1 || Name | Age 2 || joe | 28 3 || bob | 14 4 || Size | Weight 5 || small| 150 And I have a formula to sort each section by column B: ...
0
votes
0answers
16 views

Saving Certain Columns in Google Spreadsheets

Good morning! I am trying to organize a Google-Spreadsheet to enable "territories" to be viewed in the Filter View. I would like the user to be able to edit the data, but I would like certain ...
1
vote
2answers
45 views

Import current price from website to spreadsheet

As you can probably gather from the title, the goal is to plug the current price of this product into a spreadsheet. Perhaps I'm over-complicating things, but there must be an easy way to do this. ...
1
vote
3answers
94 views

Extract and sum numbers from a cell containing text that contains numbers

Suppose cell A1 contains a string that includes substrings that are either integers or decimals, the decimals being of a form that would match the regex \d+\.\d+ . For example, the contents of cell A1 ...
1
vote
0answers
32 views

How to return a value from another cell / a formula for an IF function in Google Spreadsheet? [closed]

I tried to use the following =IF(AD23<0,-1,AB22), but I get an error message. AB22 and AD23 are number values. Is it not possible to put values from other cells as a return value? In fact, ideally ...
1
vote
1answer
29 views

Lookup/Copy data from different sheets?

I am creating a schedule sheet in Google Spreadsheets that will have a number of different people inputting their own information. I would like the Director to be able to open a single document that ...
1
vote
1answer
31 views

How can I collect the sum of different columns while filtering?

I am creating a travel budget spreadsheet and need to be able to sum different filters (By Month, By Salesperson, By State, etc)...is there a way for me to display the sum of a column based on what is ...
2
votes
1answer
19 views

Google Sheets formula every x rows, assign incrementing cell value

I have a Google Sheet, with the first row container headers (so row references will be -1) and rows are "grouped" by every 7 rows. See below. I've manually typed the numbers into the Image # ...
0
votes
1answer
61 views

How do I add a counter value into a cell once a row is added

I work for a small company that monitors all service calls in an old database. I recently moved this database into a Google Spreadsheet and a Google Form. Once a user submits a ticket, I would like ...
2
votes
1answer
31 views

How to automatically refresh date in Google spreadsheet published to the web?

I am using Google Spreadsheets to maintain a register of all students in my school and track their monthly payments. Each student has a separate Google spreadsheet maintained and in the spreadsheet I ...
2
votes
0answers
20 views

Embedding a Google Visualization Chart inside a Google Spreadsheet

Unfortunately, the embedded charts in Google Spreadsheets do not have the customization I want. I would like to use a Google Visualization Chart instead. The documentation discusses how to use a ...
0
votes
1answer
25 views

Script that adds new row to multiple sheets in Google Spreadsheets

I want to add a script in Google Spreadsheets that adds new row on the second line of every sheet within a Google Spreadsheet that contains multiple spreadsheets. This will need to happen every day.
0
votes
2answers
42 views

Getting the second, third or fourth largest elements

I have tried the formula given here: Second highest value which is the following =INDEX(SORT(UNIQUE(A1:A30),1,FALSE),1,0) however it will highlight everything that's not already highlighted. I'm ...
-1
votes
0answers
20 views

How to contour data from a Google Spreadsheet?

Google has a lot of cool things such as Spreadsheet Mapper, Fusion Tables and Google sites. I have a Google Spreadsheet with data in the following format: latitude, longitude, humidity How ...
0
votes
0answers
22 views

Strange wrong result in Google Sheet using ImportHTML

Please consider the following formula: =IMPORTHTML("http://www.cvr.dk/Site/Forms/PublicService/DisplayCompany.aspx?cvrnr=30436709";"table") Strangely I get this value While on the web pages I ...
2
votes
1answer
18 views

Google Docs sheets: How to refer values in other column by key? (like SQL join)

I have a sheet/document with item number and it's price. In an invoice, I have: | Number | Qty | Price | ... | Total price | 21 | 3 | ??? | ... | = C2 * B2 I'd like the Price column to be ...
1
vote
1answer
15 views

In Google Spreadsheet, how do I make cells take a random string value from a list of strings?

Suppose I have a list of strings {apple, pear, banana, orange}. How can I have each cell in a column of cells have a random value from that list?
0
votes
1answer
21 views

Format day of the week

In Google Docs, what would a script look like that would identify days of the week (i.e., monday, wednesday, friday) and then change the background color of the corresponding cells?
0
votes
1answer
35 views

Can Google Spreadsheets handle 10 million rows?

I have a data file with 14 columns and 9,523,125 rows. Can a Google Spreadsheet handle at least 9,523,125 rows?
0
votes
0answers
12 views

Don't show current users in embedded Google doc

Is there a way to hide current users from an embedded Google spreadsheet (simple iframe)? A cursory Google search doesn't seem to bring up anything.
0
votes
2answers
44 views

How can I make Google Sheets tell me how many of each date are in a column?

I have a column in my spreadsheet that contains only Date values, like: 2014-05-17 2014-06-19 2014-07-16 2014-07-16 2014-07-21 2014-07-21 2014-07-21 2014-07-21 2014-07-21 2014-06-17 2014-06-24 ...
0
votes
1answer
25 views

How do I accomplish a vlookup if my key range is discontinuous?

So, I've got a chart (an EXP chart for an RPG) with several break points in it. One column is how many EXP is required to get to a level, the other column is what level you get to be given that EXP ...
0
votes
0answers
14 views

How to choose custom colors for Google Spreadsheet charts?

When selecting the color for a line in a chart, I see 80 choices of colors. Is there a way to add new colors or set a color using RGB, HSV or HTML colors?
0
votes
1answer
17 views

how to get count of rows in the spreadsheet?

In google spreadsheets, after typing in a list of items through cells A1 to A7, I went off to another cell and typed the following formula =COUNT(A1:A7) It reports zero, instead of seven. I've ...
0
votes
0answers
17 views

Display Sheet Name in Google Sheet

How can the current sheet's name be displayed as the result of a function in a Google Drive Sheet? I know this seems like a duplicate of Display Sheet Name in Google Spreadsheet but the answer to ...
0
votes
0answers
25 views

Break line in Google spreadsheet automatically

I have a document in Google Spreadsheets and I want when I enter a bigger text for it to fill the cell and after jump on the second cell and so on. Is there a way to do that?
0
votes
1answer
14 views

Trying to query one sheet to inform another

Someone at the google+ apps script forum suggested I ask you guys about this project I'm working on. See the whole (pretty short) thread here: ...
1
vote
0answers
39 views

Retrieve data from another sheet based on key value without empty rows

This seems pretty straight forward, and I've seen some answers that come close, but I'm having trouble nailing down one specific requirement. ...
0
votes
1answer
56 views

Google Docs Spreadsheet and Zapier

My client has a Wordpress site that uses Gravity forms. I'm using Zapier so that each time a new submission is entered into the form, it adds this information to as a new row in a Google Spreadsheet ...
2
votes
1answer
52 views

Sum the product of two columns in Google Spreadsheets

Here's the situation: ITEM COST CUST1 CUST2 CUST3 TQTY foo $0.5 1 0 0.5 1.5 baz $1.0 2 1 0 3 bar $1.5 0.5 0 0.3 0.8 SUBT $2.75 $1.00 ...
1
vote
1answer
8 views

How to get formulas to do the calculations

I have a column from A1:A398 in a Google spreadsheet. Each cell either has an "f" or is blank. I want to count how many of each there are. In row 399, 400 and 401 I put the following formulas ...
1
vote
0answers
29 views

Trello API: How to remove a member from an organization using Google Apps Script (GAS)

In a Google Spreadsheet, I have a list of Trello usernames that I want to remove from my Trello organization. How can I use the Trello API to remove all these members from my organization? I found ...
1
vote
1answer
29 views

How to parse a date and time in the format 2012-12-12T00:00:00+00:00?

I have a CSV file with dates in the following format: 2012-06-20T00:00:00+00:00 Is it possible to parse a date or date and time in this format? The goal is to turn it into a date and time that ...
0
votes
0answers
23 views

How do you refresh a filter view in Google Sheets?

Right now it seems like the easiest way to refresh a filter view in google sheets is to remove the filter view and then apply it again. What is the fastest way to refresh a filter view in Google ...