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

learn more… | top users | synonyms (2)

2
votes
2answers
30 views

How do I sequence the date in Google sheets without sequencing the year?

I am trying to sequence dates for the beginning of the week. It should read: 21-12-2015 28-12-2015 04-01-2016 and so on. My problem is that when I highlight the first two dates and drag; I get ...
2
votes
1answer
86 views

Access Google Spreadsheet revision history through API

Is there any way I could access Google Spreadsheet revision history through the API? The reason for that is I have a lot of sheets and simply relying on build-in revision history does not suffice to ...
2
votes
2answers
55 views

Can a button be added to Google Drive (Sheets) toolbar?

I use Google Drive Sheets for spreadsheet budget management. I am frequently using the Insert > Row Above/Below menus. I don't find any shortcut for either menu option in the Keyboard Shortcuts ...
2
votes
1answer
42 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
92 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
24 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
52 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
468 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
319 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
42 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
155 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
32 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
60 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
95 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 | ...
2
votes
1answer
168 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
262 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
120 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
196 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
56 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
3k 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
446 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
46 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
96 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
148 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
154 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
527 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
76 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
247 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
412 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
552 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
342 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
93 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
502 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
188 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
255 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
693 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
323 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
711 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
13k 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
3k 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
996 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, ..., ...