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

learn more… | top users | synonyms (2)

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
19 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
20 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
36 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
31 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
16 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
33 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
119 views

Query(ImportRange) throws “Formula Parse Error”

I'm working with Google docs and I've run into a wall with a query(importrange) function. =Query((IMPORTRANGE("URL","Sheet Name!A2:P1000"),"select ...
1
vote
1answer
51 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
63 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
67 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
145 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
28 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
36 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
61 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
42 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
48 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
53 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
62 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
30 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
45 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
867 views

Google Docs Chrome extension - what does it do / why does it exist?

In some Chrome instances, several extensions (apps?) called Google Docs, Google Sheets, etc. are installed (I've seen various subsets of these across different Chrome instances). What are these for / ...
1
vote
1answer
42 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
57 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
73 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
112 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
38 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
134 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
402 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
43 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
57 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
377 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
48 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
152 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
2answers
497 views

Formatting Duration Values in Google Spreadsheet

I have a Google Spreadsheet. In that spreadsheet, I have the following: Date | Start | End | Duration -------------+-------------+-------------+------------------------- ...
1
vote
1answer
82 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
88 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
526 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
66 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
56 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
101 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
343 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
561 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
329 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
359 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
110 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 ...
1
vote
2answers
37 views

Adding a title to cell number 0?

It is good convention to name your columns, to put some kind of description in cell 1. However, this really bugs me, because then all my values are indexed starting at 2, so I think I'm dealing with ...
1
vote
1answer
1k views

How do I eliminate extra line breaks in a Google Spreadsheet cell?

I have a spreadsheet in which some cells have multiple carriage returns. I would like to eliminate all the extras, so that 4 carriage returns shrinks down to just one. This question is similar to How ...
1
vote
2answers
155 views

How can I select a sheet with a variable-supplied suffix in a script?

Here is the code I'm attempting: var day=new Date().getDay()-1; var month=new Date().getMonth(); var sheet=ss.getSheetByName((/^.\s/+month+'/'+day)); I'm new to regex, but essentially I'm writing a ...