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

learn more… | top users | synonyms (2)

1
vote
2answers
524 views

display google docs spreadsheet in mobile mode in desktop browser

When viewing a google docs spreadsheet in a mobile browser you have handy filter buttons at the top. How do I display mobile version of the page in a desktop browser? Preferrably Firefox. Edit: I seek ...
1
vote
1answer
2k views

How can I alphabetize a unique result in Google Spreadsheets?

I'm generating a list based on using the UNIQUE function. How can I get the output to be alphabetized automatically?
1
vote
1answer
6k views

Circular dependency detected even while using IFERROR

I have the following function in the cell B2: ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0))) And the following function in cell C2: ARRAYFORMULA(SUM(IFERROR(Purchases!C3:C, 0))) Basically, the ...
1
vote
3answers
1k views

Missing sort in Google Spreadsheets menus

I have a Google spreadsheet that has the A column as an ID field (incrementing numbers 1...1000; no formula, just numbers). That column is frozen as is the first row of headers. I sorted by column B ...
1
vote
1answer
5k views

Put Google search inside Google Docs spreadsheet cell

Is there a way to put a Google search widget inside a cell of a Google Docs spreadsheet? I want to avoid my users to hit Ctrl + F every time they search. Would be very cool a Google Docs App that ...
1
vote
1answer
26 views

Add cells based on the content of another column

I have this sheet and I am trying to add the values of June but the issue that I have is that these values will update often with so I need to find a formula that accounts for that. The other issue is ...
1
vote
1answer
26 views

Conditional Formatting: no color when cell is empty

I am using this custom formula for my sheet: =if(B2<((B$7/4)*$A2), 1, 0). However, this also highlights the rest of the cells that are empty. How do I exclude empty cells from this formula?
1
vote
1answer
28 views

How do I organise my data in Google SpreadSheets

I have separate sheets based on month's of the year. Each time I create a new month I copy the template sheet and fill in the data. Therefore each sheet has the same layout it's only the data that ...
1
vote
2answers
32 views

Google Sheets permissions

I built two separate spreadsheets in Google Drive and spreadsheet "A" has a script that when run, will copy the input data into spreadsheet "B". When I test it, it works perfectly. But I have other ...
1
vote
1answer
52 views

How to create an auto updating Google spreadsheet with information from Steam?

How would I create an automatically updating Google spreadsheet containing information about Recently Played in Steam? Table must look like here: A2 taking information about account name from ...
1
vote
1answer
36 views

How to create an automatically updating Google spreadsheet with information from Steam?

How would I create an automatically updating Google spreadsheet containing information about Recent Activity in Steam? Table must look like here: But the hours column must automatically updating ...
1
vote
1answer
20 views

Using IF Formula to get data from another Cell

I am just trying to use the IF formula: COLUMN A COLUMN B COLUMN C Cats NULL Dogs Monkeys Kittens NULL ...
1
vote
1answer
34 views

Script to Create Copy (Backup) of entire spreadsheet onOpen

Is it possible to write a script or is there another method of backing up a spreadsheet when opened? The reason is: I have other users opening my spreadsheets and if they completely stuff everything ...
1
vote
1answer
177 views

Query(ImportRange) throws “Formula Parse Error”

I'm working with Google Spreadsheets and I've run into a wall with a query(importrange) function. =Query((IMPORTRANGE("URL","Sheet Name!A2:P1000"),"select ...
1
vote
1answer
66 views

How can I get a unique ID when I submitted an answer in Google Form?

I am using Google Form for my customers to submit their opinion. I read this and this, but I have some questions which these two posts haven't mentioned. Is it possible for my customers to know their ...
1
vote
2answers
65 views

How to insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets

How do I insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets? Lets say in row 1 it says "A" and in row 2 it says "B", "C" in row 3, etc. I would like it to say ...
1
vote
2answers
78 views

Google Query For Dates in Last 30 Days

I'm trying to figure out how to do a google query for dates in the last 30 days. I'm using a wordpress plugin that lets you display spreadsheet data and do queries. Here is the current working query ...
1
vote
1answer
231 views

Google Sheets Function to get a shortened URL (from Bit.ly or goo.gl etc.)

I have a Google spreadsheet that builds Google Analytics links. I'd like to shorten those links. I tried the Bit.ly example but even their example does not work. How can I do this?
1
vote
1answer
29 views

Dynamic Values in a formula?

I have a google sheet where I am using this query to pull data from another sheet : =QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where (Col18='Survey Complete' and ...
1
vote
1answer
37 views

How to count specific items in column?

I just started using Google Spreadsheets. I have a question concerning "counting" a specific word/item in cells. Example: Column A 1. HAT 2. HAT 3. SHOE 4. CANE 5. SHOE 6. HAT 7. - 8. - ...
1
vote
1answer
63 views

Delete second row in a cell in Google Spreadsheet

In order to create some list, I've been copy some data from the web. Unfortunately it looks like it has been added second row for each record it need. For example: Each record is in the same ...
1
vote
2answers
44 views

Average two numbers IF the second number exists

I have 2 columns (Google sheets) of numbers similar to: 10.453 12.178 12.654 0 The third column is: if number higher than 0 exists in both columns, display an average of the two else just ...
1
vote
1answer
49 views

Google sheets: conditionally change sign of decimal value in cell for each row

Here's a preview of the spreadsheet I'm editing in Google Drive: The transaction type cell has a value of "debit" if the row is for an expense, and "credit" if it is income. I would like a script ...
1
vote
2answers
80 views

Google Spreadsheet: Count column if criteria is met in the respective column and another column

Is there a way to COUNT the cells in column A if the cell in column A equals "X" and the adjacent cell in column B equals "January"?
1
vote
1answer
65 views

Google Spreadsheet Query for unique and “most recent”

Given a Google spreadsheet like this: I would like to create a query that shows only the most recent row for each unique name, so the results would look like this: What would that query look ...
1
vote
2answers
34 views

Google Sheets rounds numbers where not wanted

This: 3.1415926535 produces: 3.141592654 How can I have it output the whole number as is? I was using this with TO_TEXT(), getting the same result, but I guess the problem lies prior to ...
1
vote
2answers
53 views

Operations on hyperlink labels of cells

I am using hyperlinks on some price values on cell in Google Docs. So my cells look like this: =HYPERLINK("http://www.site.com/item","$25.00") I am trying to perform a SUM operation on a bunch of ...
1
vote
1answer
44 views

Select row in Google Sheets

I have a function in Google Sheets that is giving the following output: a b c 1 2 3 I don't want the a b c I just want the 1 2 3. What function can I wrap around my other function so that it will ...
1
vote
1answer
73 views

Dynamic date range query

I'm trying to create two cells that will update my query: A >= date"A1" and A <= date "B1". My goal is to create a search page for the data, where the end user could insert a date or the =today() ...
1
vote
1answer
83 views

Google Sheets IMPORTRANGE + QUERY won't import IMAGE

I use Sheets in my business to create product catalogs for wholesale prices. I have different sheets for different companies but the data in many of them is the same. I'd like these sheets to sync ...
1
vote
2answers
132 views

Google Spreadsheet criteria if cell above is blank

I am trying to get a sum of column values in a Google Spreadsheet only if there is no value in the column above. Consider the following example: A | B | C --------------------------------- ...
1
vote
2answers
51 views

Google Spreadsheets: Using conditional formatting from date in another cell

I have two columns side by side containing dates. The dates in column A are based on when a project is due. The dates in column B are based on when a project is completed. What I want to do is show ...
1
vote
1answer
157 views

Google spreadsheet - QUERY changes time format

I have this column with amounts of time (format set to number > duration), if I made a query of that column I get it with a different time format and I'm not able to set the proper format back again. ...
1
vote
1answer
475 views

Google sheets VLOOKUP issues not finding a value thats is in its range

Heres a link to a copy of my spreadsheet I am attempting to do the same formula using a VLOOKUP in 4 sheets to do a calculation called LSI. In the first sheet I have the raw data from a form coming ...
1
vote
1answer
46 views

Google Sheets list key with highest value in key value pair

I have a some data in key-value pairs setup like so: A B 1 Key: Value: 2 Cars 5 3 Bikes 4 4 Vans 9 5 Trucks 6 What I can't seem to ...
1
vote
1answer
61 views

How can I add hours in Google Sheets to show days?

I have 2 cells in Google Sheets with values: 22:00:00 22:00:00 and I have a cell which has the function sum to sum these values and it shows the value: 20:00:00 I am guessing it is trying to ...
1
vote
1answer
403 views

How to pass multidimensional array to a custom function in Spreadsheets

I want to pass a multidimensional array to a custom function. I do it like this: =customFunction({{"a1v1"; "a1v2"};{"a2v1"; "a2v2"; "a2v3"};{"a3v1"}}) However I don't get the expected behaviour ...
1
vote
1answer
52 views

Function to retrieve a subset of rows coming from another function in Google Sheets

I have a function in a Google Sheet that returns a handful of columns and two rows. One of those rows contains column headings, which I would like to remove. Is there any way to wrap this function ...
1
vote
1answer
177 views

Get YouTube video's view count in Google Drive/Sheets?

Is there anyway to write up a Spreadsheet in Google Drive such that a cell will have the current (or as close to current as possible) number of views that video has received?
1
vote
1answer
89 views

How can I sort all worksheets in my Google spreadsheet on open?

I have successfully been able to sort the first sheet in my Google Spreadsheet onOpen, but I'd like to sort all four spreadsheets onOpen and I can't make this work. I've tried quite a few variations ...
1
vote
1answer
96 views

Circular Dependency Error, Blank when 0

I have tried both of these in Google Spreadsheets and continue to get circular dependency errors. I am trying to show blank when the result is 0. New values are entered daily and want to have the ...
1
vote
2answers
587 views

Issue with Custom number format in Google Spreadsheet

I have a number cell formatted in this way: #,#" days";. Nevertheless, if I write 1.5the output is 1 days even if the content is still 1.5. Otherwise, if I write 1,5 the output will be as it is ...
1
vote
1answer
68 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?
1
vote
2answers
58 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
102 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: ...
1
vote
3answers
369 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
2answers
640 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 ...
1
vote
3answers
351 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, ...
1
vote
1answer
382 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 ...
1
vote
2answers
116 views

Gmail message details stored to spreadsheet

I would like to know if there is a way that I could have specific messages in my email to go directly in a Google spreadsheet. For example I need all email messages with subject line of "X" be stored ...