A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

2
votes
1answer
428 views

Multiply duration (in hours) by decimal value

I have the following data in a Google Spreadsheet: +----------+------+--------+ | Duration | Rate | Value | +----------+------+--------+ | 16:15:00 | 55 | 893.75 | +----------+------+--------+ I ...
2
votes
2answers
47 views

Suming functions by relations between sheets (Google Sheets)

Table for counting items: ID COUNT 1 10 2 0 3 12 4 1 5 5 6 10 7 9 8 9 9 11 10 0 Table-pedia with info about items: ID NAME FEAT1 FEAT2 1 Nanan R 20 2 Meowth S ...
2
votes
2answers
577 views

How do I refresh script results when a cell color is changed?

I have a Google Spreadsheet that contains a script to count a number of cells by color. The script works perfectly but seems like when ever I change the color of the cell, the count is not updated. ...
2
votes
2answers
2k views

Is there any way to use a formula in a Google Sheets filter view?

I have a sheet with a column of numbers. I'd like to create a filter view that only shows rows with a value greater than 500, but I can't find any way to do that. There are lots of different numbers, ...
2
votes
1answer
359 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 # ...
2
votes
3answers
3k 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 ...
2
votes
1answer
96 views

Appending text to each column in a Google Drive Spreadsheet

I have a Google Drive spreadsheet that has about 200 columns. Each one is a name, and to each one I want to append -banner.jpg. Is there a way to batch process this? For example: abcde fghij ...
2
votes
2answers
545 views

Preventing concurrent editing of a cell in Google Spreadsheet?

We often have the case of two people overwriting each other's changes by writing on the same cell at the same time. Is there a way to make it so that cells can't be written by more than one person at ...
2
votes
3answers
190 views

Counting number of cells in a range that contain certain text characters

My question simply: Is it possible to generate the data in A3:A5 using a formula? A B C D E F 1 PASSED/TOTAL | EXAMS ATTEMPTED 2 ...
2
votes
2answers
256 views

Incrementing value read from spreadsheet exploding. 1 + 1 = 200 000 000 000

I read a value (in this case 7) from a Google Spreadsheet with a script, increase that value using source++ and write the increment back to the spreadsheet. I expect a series of 7, 8, 9, 10. ...
2
votes
1answer
4k views

Date and time stamp automatically added in column [duplicate]

We have a barcode scanner that we use to track student assignments. I am trying to create a Google Spreadsheet that tracks the assignments for each subject. I have been using the following script: ...
2
votes
1answer
2k views

CONCATENATE a string with an ArrayFormula

I have the following string in column H and I. H2="http://" I2="aaa" H3="http://" I3="bbb" I want to have a auto-generated J column, which results like: J2="http://aaa" J3="http://bbb" So I put ...
2
votes
1answer
707 views

Arrayformula with dates before and after

Here is what my spreadsheet looks like I enter recurring weekly payments details in G,H,I,J & K (Name,Amount,Day of week, Start Date, End Date respectively). This arrayformula: ...
2
votes
2answers
3k views

Import Google Spreadsheet range if a column contains a particular value

I want to import a range of data from one Google Spreadsheet (1) into another (2), but only when Column J in spreadsheet 1 contains a 'yes'. I'm using the following formula: ...
2
votes
1answer
333 views

IF statement with ranges

How do I make it so that if any number in the range C6:D11 on PAGE is less than zero, output "Some words" and if not, output "Some other words"? I tried using =IF(PAGE!C6:D11 < 0, "Some words", ...
2
votes
2answers
1k views

How to automatically add spaces in between each character in a text string?

Teachers I work with enter 4 scores for a student’s assessment in a single cell, like this: 2113, with no breaks or spaces. How can I separate those characters so as to be able to create a chart of ...
2
votes
2answers
713 views

Allow trailing whitespaces in Google Spreadsheets

I was working on a Google Spreadsheet and I found that it does not allow more than one white space anywhere. Is there any option to stop auto trimming on text? I searched it but didn’t found any ...
2
votes
1answer
14k views

Using INDIRECT() in sheet reference range

I have a separate sheet with some data where a user's input will reference the row number of a sheet. I'm trying to reference that sheet by inserting the column letter and taking a number input from a ...
2
votes
1answer
4k views

Google Spreadsheet: Adding formulas to Google Forms responses rows

I have a sheet with form response data in several columns, say A:G. Some of the data are numerical and I'd like to have some calculations done on them, e.g. in columns H through J. I have already ...
2
votes
1answer
2k views

How do I disable Google Spreadsheet comment notifications?

I've got a shared document with a co-worker and they're adding comments. The system keeps sending me emails with every comment she adds and it's rather tiresome. Google Support says there should be a ...
2
votes
1answer
1k views

Date format in spreadsheet (google doc)

I want to create spreadsheets to record the money I spend each year. Each file is named as Cost-2012, Cost-2013,...etc. Inside each file, there are in fact 12 sheets, which are named as 01, 02, ..., ...
2
votes
2answers
1k views

Is there a filter delimiter in Google Spreadsheets?

I'm trying to build a list of servers in a Google Spreadsheet. Each server has multiple services, and I would like to be able to select a single service and see all the servers providing that service. ...
2
votes
1answer
2k views

Convert a date in Google Spreadsheet?

I have a Google Spreadsheet with a date row in UTC time, such as 12/6/2012 11:14:32 I want GMT+2 local time, so how can I convert the date in Google Drive? And, if not possible, is there a way to do ...
2
votes
1answer
5k views

JOIN formula in Google Spreadsheets

I want to use the JOIN formula in Google Spreadsheets to transform all values in one column into a list, with , around each value. Has anyone used this formula before? The only information I can ...
2
votes
2answers
1k views

How to write a logical function that checks several columns and rows

I have three columns in my Google Spreadsheet: C (Dialog) - D (Declined) - E(Answered) All the columns are dates. Example: C D E 14/02/2012 28/02/2012 ...
2
votes
1answer
953 views

How to put the name of a cell into another cell in Google Spreadsheet

In Google Docs Spreadsheet, is there a simpler way to put the name of a cell reference into another cell than this? =concatenate(char(code("A")+COLUMN(D3)-1),row(D3))
2
votes
2answers
1k views

How to count cells that are below a certain date and check if other cells in the current row are empty?

I need to count how many cells in a specific column are below a certain date. Also, at the same time, the counter should only be increased, if some other cells next to each cell being counted are ...
2
votes
2answers
4k views

Hyperlinks in Google spreadsheet docs

In Google spreadsheets, it is possible to add a hyperlink to a cell as: =hyperlink("www.google.com";"search the web") This will show the text search the web which will hyperlink to Google. ...
2
votes
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 ...
2
votes
1answer
315 views

Getting a new “publish to web” URL for a published Google doc

When I publish a Google spreadsheet to the web, it generates a URL that I can share with others. I know I can stop publishing. When I re-publish, the URL is the same. I no longer want ALL viewers who ...
2
votes
1answer
134 views

Can I add a search box that will open a new page on some 3rd party site to Google Docs?

I'm trying to make a document/spreadsheet containing a few resources I regularly use and perform searches in. Can I create something I can perform searches directly from a document that will open up ...
2
votes
2answers
4k views

Is there a way to search and replace a regular expression in Google Docs spreadsheets?

The problem is that I entered a lot of formulas like =round(x+y) into a Google Docs spreadsheet. Now I need them exported to MS Excel and the latter only accepts =round(x+y,0) as a valid format. ...
2
votes
1answer
75 views

Using Google Spreasheet's IMPORTXML Function to Download Selected Multiple Columns

I use Excel's XML mapping to download RSS feeds and select elements to display in column. I'm currently using Google Spreadsheet's IMPORTXML functions and trying to recreate my below Excel results. ...
2
votes
1answer
35 views

Import only those cells from a spreedsheet that have certain conditional formatting

I have created a Google form which links to a spreadsheet. There is an option which asks for the person's name. For the answers in the sheet, I've created a conditional formatting which highlights the ...
2
votes
1answer
24 views

Reverse lookup from Google spreadsheet key

I can't figure out where the data in an IMPORTRANGE function is coming from. How can I use the spreadsheet key to find the the original spreadsheet IMPORTRANGE is pulling from?
2
votes
1answer
37 views

Detect whether a cell has strikethrough format

Is there a function that checks if the strikethrough format has been applied to the cell contents? I would like use a formula similar to (=IF(HAS_STRIKETHROUGH(C5), 1,0)
2
votes
2answers
33 views

Conditional Average with a function

I'm trying to average a range but only if one condition is true. A B C 1: A 1 2 2: B 3 4 3: C 5 6 4: D 7 8 5: A 1 6 6: E 8 9 7: E 5 8 What I want is if the char I'm looking for is "A" then I ...
2
votes
1answer
33 views

Split one column into two equal halves

GOAL: Split one column of items into two half-sized columns of items. So if there are originally 1 column by n rows I want two columns by (n ÷ 2) rows with exactly the same items as the 1 x n column. ...
2
votes
1answer
38 views

Auto create sheets within a spreadsheet with formatted columns and cells

What I would like to do is be able to run the script and have it create 4 new sheets (within the current sheet I'm in) with the following formatting in each: 1st row frozen with column headings on ...
2
votes
1answer
53 views

Google drive (sheets) shared file - move ownership - different domains

I'm helping my daughter with her project and started an example of how to create a table using google sheets, which I shared with her. She continued on with it but now I want to remove it from my ...
2
votes
1answer
40 views

How to auto format a phone number with an associated hyperlink in Google Sheets

I don't know if this is possible, as its a bit of an edge case, but it would be amazing if this can be accomplished. I have a spreadsheet of contact information, with fax numbers. At work, we send ...
2
votes
1answer
36 views

How to update values from another cell only once, not with subsequent edits?

I have a cell A1, whose values I change periodically. I have a cell F7:F, I add values to this column from time to time. I have another cell K7:K whose values depend on what's in A1 and what's in ...
2
votes
1answer
21 views

Find slope/intercept of last 30 values entered?

I have a column A7:A that has numbers from 1+; I have another column B7:B, that has dates from Oct 31st, 2015 +; I have a column F7:F that has weight values; I have a goal weight of 108.36297798; ...
2
votes
1answer
33 views

How to add bulleted list in Google Sheets

I am unable to add bullet in front of any sentence. See below example, I need circle bullets in front of every sentence written in one cell. First Sentence Second Sentence Third Sentence How can ...
2
votes
1answer
36 views

Conditional Formatting Based on Date Formula

I'm trying to highlight the contents of a Date cell if the following conditions are met: Value of another cell = "No"; and Value of target cell + 7 days > Today The formula that I have right now ...
2
votes
2answers
46 views

Join several columns with a single formula returning an array

This is the opposite of my question Split a column of strings with a single formula returning an array. I have several columns, say, A-K, which I want to join into one comma-and-space separated column ...
2
votes
2answers
28 views

How to recombine text into one line in Google Sheets

=index(ImportXml("https://www.google.com/finance?q=ibm", "//div[@class = 'companySummary']" ),0,1) Put that anywhere in a Google sheet and it will give you a cell with a block of text. This text is ...
2
votes
1answer
115 views

Getting COUNTIFS to work in complicated Google Sheets formula

I'm having trouble with COUNTIFS on Google Sheets. I've been able to use SUMIFS and now I need to use COUNTIFS. I tried to do a Find and Replace, changing SUMIFS to COUNTIFS, on what I thought were ...
2
votes
1answer
102 views

Is there a way to have a Slack overlay in Google Sheets?

Google Sheets lets you chat with other people actively using the sheet, but it has no history and overall feels like the simplest thing that could work. Slack, by contrast, seems like everything you ...
2
votes
1answer
20 views

Time query in Google Spreadsheets

I've started using Google Spreadsheets for our logbook and it's been a great help. I have data with a time component in it (start and end time) and I'd like to query or filter the data where the ...