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

learn more… | top users | synonyms (1)

2
votes
1answer
24 views

How do you extract a number from a URL as raw text?

So I have a list of Google+ profile links in a Google Sheet https://plus.google.com/112337650380234682548/ How can I extract the number on the end in a seperate column? I've tried ...
1
vote
3answers
44 views

Calculate time values in a single Google Spreadsheet cell

Is there a way to add two times, or durations, in one line in a Google Spreadsheet? Something like: =1:14:25+0:17:12 I know that I can use the time command in-line, and that I can add times from ...
-1
votes
0answers
12 views

Countdown timer in javascript [duplicate]

Can someone check the javascript code and see what can be done. I am struggling to add B2 time to the start time I think i need to convert B2 time to milliseconds and them sum with the start time to ...
0
votes
0answers
8 views

How to get Google Spreadsheets to auto-update a reference to another sheet

Referencing: How can I get Google Sheets to auto-update a reference to another sheet? I am trying to do the EXACT same thing for the EXACT same reason. I assume this is a VERY clever work around but ...
0
votes
0answers
16 views

OR function doesn't work inside FILTER [duplicate]

When I use FILTER(A2:A; OR(C2:C >0; D2:D >0)) I get different range intervals error, but when I remove the OR it doesn't return an error. So why OR function can't be used with FILTER function?
1
vote
1answer
18 views

Google Spreadsheet add rows until marker

I have a spreadsheet of the form ItemA Subitem1 5 Subitem2 4 Subitem3 7 <potential marker> ItemB Subitem 4 I want to sum the values next to Subitem to a total next to ItemA, ItemB, ...
-1
votes
0answers
16 views

automatic date filling in column B when column A has a value in Google Spreadsheets [duplicate]

What function I need to choose so that when in column A I enter a value , the date in column B to fill automatically ? I tried to do this : B2==if(istext(A2),today(),"") The problem is I the date ...
2
votes
1answer
31 views

Consolidate List of Similar Data into Single Row in Google Spreadsheets

I have a Google Spreadsheet that lists donations given by constituents with a unique account number. Several constituents have made more than one donation in a given period of time listed separately. ...
0
votes
0answers
43 views

Countdown timer list

Can someone check the javascript code and see what can be done. I am struggling to add B2 time to the start time I think i need to convert B2 time to milliseconds and them sum with the start time to ...
1
vote
1answer
24 views

See last modification in Google Drive/Spreadsheets

In my Google Drive I see when a spreadsheet was modified: How to go about seeing what the modification was?
0
votes
0answers
6 views

Keep track of printing in Google Sheets

In Google Sheets is there a function that will record and update a field every time a file is printed? Couldn't find anything in the docs. I'm trying to automate a version number for printed hard ...
1
vote
1answer
27 views

Conditional Cumulative Sum using Arrayformula

I saw a nice solution for the cumulative sum problem here: Cumulative Sum without Script My question is how do you make this conditional? I mean cumulatively sum only numbers that belong to a certain ...
-2
votes
0answers
25 views

Create map with custom markers from a spreadsheet [closed]

does anyone of you know if a (free) service or script exists that creates a map (google maps, bing maps, openstreetview...) from a spreadsheet (csv, google spreadsheet...) with custom markers? In the ...
4
votes
2answers
64 views

Google Spreadsheets Row Insert Reference Updating Behavior

I have a Google Spreadsheet with one column and two rows: cell A1: 2 cell A2: =A1 (displays as "2" because of the reference to A1) If I insert a row between these two rows (by right-clicking on row ...
0
votes
0answers
13 views

Hyperlink to a cell in a Google Spreadsheet

I want a user to be able to click a link that sends them to the specific cell in a specific spreadsheet. The spreadsheet must be in edit mode aswell.
0
votes
2answers
16 views

Google Sheets advanced lookup + sum

I'am try to get the total results of multiple sheets where i have in collum A a dropdown list with value 1 to 3 for example. in total i have 12 sheets (once for each month) and i want of all the ...
0
votes
0answers
9 views

Spreadsheet not sorting properlty

When I attempt to Sort a spreadsheet by the information in a column I am greeted with the result: "This operation is not permitted because it would alter the structure of this table. More ...
3
votes
1answer
32 views

Google spreadsheet: calculate start and end rownumber of first non-empty range

I have a column B which may have a value 1 or is empty. I want to calculate 2 fields: startFirstRange: the start rownumber of the first non-empty range, or -1 if not exist. endFirstRange: the end ...
1
vote
1answer
41 views

Google spreadsheet: check if cell itself is empty

Is it possible to have a formula somehow on cell A1 that checks as a conditional if A1 is empty? Obviously, the default way I'm getting a circular dependency error. However, I figure some formula ...
2
votes
1answer
40 views

Google spreadsheet: execute function only once to prevent circular dependency

I'm trying to do something like this: a1 = if(a1 = "", b1 + 1, ) ... aN = if(aN = "", b1 + 1, ) b1 = max(a1,1) The idea is that column A has unique ID's, and b1 keeps the highest generated id ...
-1
votes
0answers
9 views

Major FIREFOX Redirect Error Problem (Error Code 11156) [migrated]

I would appreciate some help please. When I try to log into some website, im being redirected to the members only page (e.g. dashboard, main account, etc) but instead of successfully landing there as ...
0
votes
0answers
19 views

Google sheets equivalent of self-referencing in Excel

This is, in my opinion, one big advantage Excel has over Google sheets is the self-referencing formulas. What I mean by that is: say in cell A1, I'm changing the value in there a,b,c. And according ...
0
votes
1answer
16 views

Search for multiple variables in multiples rows at the same time in Google Sheets/Drive

I'm using Google Sheets (only option, sorry, know this should be a database). Sheet 1: List of 200 employee names, and up to 10 location codes they are frequently assigned to. Sheet 2: List of 50 ...
3
votes
1answer
41 views

How do I convert a cell to RFC 3986 standard (URLEncoded) in Google Spreadsheets?

Is it possible to convert text from a certain cell to RFC 3986 (http://url-encoder.org/) in a Google Spreadsheet? Lets say C4 has the value John Doe, then D4 must be John%20Doe.
2
votes
1answer
79 views

Element-wise operations on cell ranges in Google Spreadsheet

In Google Spreadsheets, what does one obtain if operations are performed on a range of cells? Have a look at the following example: Intuitively, I would expect the result to be 14. This is the way ...
1
vote
2answers
38 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
76 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
25 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
20 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
47 views

Google Sheets Suffix To Column Cells [closed]

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
37 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
26 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
43 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
25 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
25 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
29 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
15 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
20 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
56 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
42 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
25 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
33 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
15 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
67 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
88 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
50 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
39 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
51 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
53 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, ...