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

learn more… | top users | synonyms (2)

2
votes
1answer
54 views

How can specific columns of a Google spreadsheet be exported as CSV?

I have a Google spreadsheet with 10 columns of data. I would like to export columns A, C, E, F as csv. I do not need to export the entire spreadsheet. Can this be done? I do not mind using Google ...
2
votes
1answer
112 views

Is there a way to dynamically populate a dropdown depending on the value of other cells?

Is there a way to populate a dropdown list differently depending on the values of another cell? For example, the dropdown in A2 contains Movies and Sports. I want the dropdown in A3 to have Horror and ...
2
votes
1answer
25 views

Automatically populate a table with time values, based on the type of event

I am hoping to find a way using the conditional formatting to find an easier way to have a populated registration time (F), event start time (G) and event end time (H) for each event type as shown in ...
2
votes
1answer
57 views

Can Google Spreadsheets conditionally format based on a previous cells content?

Is it possible to conditionally format the background colour of a cell, based on the value in the column preceding it? For example, in the following table (first row/column are titles): | A | B | ...
2
votes
1answer
605 views

Simplifying Google Form Checkbox Responses into Itemized List

This is more of a Google Sheets question than a Forms question. I want to receive form responses from checkbox questions (which unfortunately cannot be separated into separate rows) and flip the data ...
2
votes
1answer
468 views

Google Sheets: Asterisk character (no wildcard) on conditional formatting

I have a sheet with following values on column A: [ A ] [1] Dead line [2] 15 days remaining*** [3] Dead [4] 131 days remaining* [5] 80 days remaining** I would like to use ...
2
votes
1answer
43 views

Division in Google Spreadsheets

How do I accomplish the following: I have a cell C5 with various numerical values. If C5 is less than 30 return 0 in C6 If C5 is greater than 30 return 1hr in C6 if C5 is greater than 60 return 2hr ...
2
votes
1answer
210 views

ImportXML with XPath does not return an entry of a table

Using Google Sheets with IMPORTXML to scrape a piece of information from a website via XPath. Here's the site (click here). The specific piece of information I'm trying to extract is the Price/Sales ...
2
votes
1answer
35 views

“Clean” join() without excess delimiters?

I'd like a function in Google Sheets to concatenate elements with a chosen delimiter like join(), but without excess delimiters at beginning, end, and between null elements. I accomplished this with ...
2
votes
1answer
61 views

Formula to show text based on numbers

I am looking for a short way to type this: =if(a1=1,"sometext",if(a1=2,"othertext",if(a1=3,"thirdtext"))) In JavaScript you would do something similar like this: switch(a1) case 1: "sometext" ...
2
votes
1answer
126 views

How do I use a negative DURATION formatted cell from Google Spreadsheets in Google Scripts?

I'm using Google Spreadsheets and I have a sheet in the following format: Start | End | Partial result | Expected | Total 08:00 | 12:00 | 04:00 | 05:00 | -01:00 14:00 | 18:00 | 04:...
2
votes
1answer
242 views

Fixing permission problems in cross-spreadsheet import when using Apps Script

I created a function in the Apps Script associated with a spreadsheet: function importNotes() { s = SpreadsheetApp.openById('spreadsheet-key').getRange('F2').getNotes(); return s; } ...
2
votes
1answer
329 views

How to post responses from Google forms to a specific sheet in a Google spreadsheet

I have a form, a Google form, and I'd like to post user's responses to a specific sheet in the Google spreadsheet. Is this possible?
2
votes
2answers
177 views

Grab text of first row of column with last data value

Essentially, I want the values in column D to be the value of the header row of the column with the last data value. For example, if H27 is the last cell with data in row 27, I want D27 to be the ...
2
votes
1answer
290 views

Highlight all cells in a range that contain a formula in Google Sheets

I am looking to apply conditional formatting to all the cells in my sheet and highlight cells that contain a formula. I know that I can format cells based on a custom formula, and I know that I can ...
2
votes
1answer
57 views

Highlight cells, which content can not be parsed as number

How can I highlight cells whose content is not parsable as number? thanks!
2
votes
2answers
1k views

How can I trace precedents in Google Sheet

In Excel we can trace precedents and dependents. Can some one help me with something similar for Google Sheets? I have got the code for dependents from http://webapps.stackexchange.com/a/50149/88163. ...
2
votes
1answer
5k views

I need to hide/show a group of columns in Google Sheets in a simple way

I need to hide and show in a easy way a group of columns in Google Sheets. In Excel I used a Macro with a button, but in Sheets I cannot put a button. How can I add buttons with custom functions in ...
2
votes
1answer
513 views

Conditional Filters Formula in Google Sheets

So I have a cell C4 in which I have an item list for a data validation. Below, I want to create a filter that will take all the entries in another sheet named All and compare that to the C4 cell. So ...
2
votes
1answer
48 views

Formula which will grab specific cells and concatenate them together but only if they are not blank

So I have this formula currently, but it breaks when nothing is in any of the cells. I'm expecting this of course, as I haven't told it to do anything when ISBLANK is TRUE. So my question is, am I ...
2
votes
2answers
107 views

Turn a table into a list of row/column/value tuples

I want to turn a table like this | | apple | banana | chia | |----------+-------+--------+------| | antelope | 1 | 4 | 2 | | bear | 6 | 3 | 9 | | cheetah | 8 ...
2
votes
1answer
173 views

Can't filter a column to see all cells containing `@` in Google Spreadsheets

I'm trying to filter a column to show cells that contain @ (as in an email address) but when I put @ in the filter box it comes up with a blank. Any ideas? An example spreadsheet here. Here is ...
2
votes
1answer
178 views

Formula in Google Sheets to lookup contents of another cell

I'm trying to create a formula that brings back a certain cell depending on what another cell has in it. But the formula I'm trying isn't working. It's working for other things (what I thought was the ...
2
votes
2answers
743 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"?
2
votes
1answer
1k views

How to get historical currency data in Google spreadsheets?

I use this formula to get the currency: =GoogleFinance("CURRENCY:EURUSD", "price", DATE(2006,10,2)) But when I enter a date before 2006-10-2 I get an error. Is there any way to get older data ...
2
votes
1answer
91 views

google cell format: only 3 digits

Is there any way to do this: I have cell and most of the numbers have 2 significant digits like 3.45. But once in a while I have a number like 143 big number, the rest are under 10. Currently for 143 ...
2
votes
1answer
328 views

Posting more than 2000 lines from IFTTT to a Google Spreadsheet

I have an IFTTT trigger set up whose action is to add a row to a Google Drive spreadsheet. Once the spreadsheet reaches 2000 rows, IFTTT creates a new document with the title: Document Title (1) ...
2
votes
1answer
467 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
695 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
3k 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
395 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 # column,...
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
105 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
646 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
199 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
622 views

Google Spreadsheet SUMIF Using Two Criteria

I've read through the forums but found nothing that solves my problem. My apologies if the answer is here somewhere and I've missed it. I have a Google spreadsheet with multiple pages, each tracking ...
2
votes
2answers
264 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. Instead,...
2
votes
1answer
7k views

Get cell row and column index in Google Spreadsheet

I have spreadsheet with two sheets inside. The name of the first sheet is "fr" and the name of the second sheet "cql". Now the first sheet contains data as the picture given below. And the second ...
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
739 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: =query(ImportRange("...
2
votes
1answer
350 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
729 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
15k 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, ..., ...