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

learn more… | top users | synonyms (1)

0
votes
1answer
9 views

How to convert data table in specific format (JSON)

How do I convert my data table in the below format? Do I need to create it manually or is there any other way? { "COLUMNS": ["COL1", "COL2", "COL3", "COL4"], "ROWS": [ ...
1
vote
2answers
27 views

Google sheets: is CHOOSE different from Excel?

I need to select randomly between two strings, say "A" and "B". I do =CHOOSE(RANDBETWEEN(1,2), "one", "two") It doesn't work. Neither =CHOOSE(1, "one", "two") It shows ERROR.
1
vote
3answers
51 views

Google sheet, lookup a value to get whole column

Here is my problem: I have a table starting in A1 with D D C B A 1 2 3 4 5 6 7 8 I want a function that returns me the column of data of whatever letter give it. ie input:=function("C") output: ...
0
votes
1answer
20 views

Counting specific words

I want to count how many times in a column certain words from a predefined set appear. In this example those specific words will be days of the week. For example: Mon =3 Tues ...
1
vote
0answers
16 views

How do I avoid cells being printed as “Loading…”?

When I print (or export to PDF), some cells has value Loading... instead of actual value. It looks like Google Spreadsheet was still calculating values when it converted document to a PDF, although ...
-1
votes
1answer
44 views

Google Sheets Suffix To Column Cells

I'm editing a spreadsheet of product data that has a category column. I'd like to create a new "Left Handed" as well as "Right Handed" category in addition to each products current categories. I've ...
0
votes
1answer
29 views

How to mix numbers formula in text format to formula format? [closed]

I have formulas that created 8/39 5/39 4/39 Now in C1 I want the sum of them and in D1 percentage of it. I cant execute slash from text. =A1+B1+C1 doesn't work.
0
votes
1answer
24 views

Average with parametric cell in Google Spreadsheet

I'm desperately looking for a way to write a function like this: =SUM(A1:CELL(B1))/COUNT(A1:CELL(B1)) where CELL(B1) contains a text, for example "A37". In this way, I can do the average only in ...
2
votes
1answer
30 views

Run script when clicked on picture in published Google spreadsheet?

I used the script underneath in Google Spreadsheet and added an image that when clicked upon runs the GOTOSHEETONE function to navigate to sheet: SHEETONE. So far so good. However, when I publish my ...
1
vote
1answer
24 views

How can I use a single column as a normalized data point with multiple choices in Google Sheets?

I find I often want to have a column that is meant to be one or more choices from a fixed set. E.g. a Codebase field in a dev roadmap spreadsheet, which might have choices like {DB, API, JS, CSS, ...
1
vote
2answers
20 views

How to get a Google spreadsheet cell to display a number as 4 digits, even if it's less than that

This is a formatting issue. Basically, I would like to get all the numbers I enter into a column become 4 digits in the other column ("output column"), with 0's in front as applicable. E.g. when I ...
1
vote
1answer
24 views

Google spreadsheet sort auto update

I have this table, which is a ranking, the values are updated automatically (data gathered from other files). What I want is that every time this values are updated the ranking automatically updates ...
0
votes
1answer
7 views

Google Sheets: Evaluating each cell in a row based on the cell adjacent to it [duplicate]

I'm trying to set up a spreadsheet the will color the background of a cell based on the text within another cell. This would have to be set up in a row so that if (for example) B2: contained "done", ...
2
votes
1answer
18 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
39 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
27 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
24 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
26 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
41 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
52 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
80 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
41 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
46 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
43 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
24 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
36 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
72 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
58 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
51 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
103 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
33 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
35 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
66 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
35 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
21 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
26 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
43 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 ...
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
18 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
22 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
41 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
47 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 ...