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

learn more… | top users | synonyms (2)

3
votes
1answer
240 views

Google Spreadsheets Calculation/Reinput Automation?

I have a little basketball stats sheet, and a cell that has a total of a specific player's statistic they have accumulated throughout the season. But there is no way I know of to update this cell ...
3
votes
1answer
157 views

Published Google Docs spreadsheet: one data set with 3 sheets each sorted differently

Is it possible on a published Google Docs spreadsheet to have 3 individual sheets, each sorted differently from the common data, and only update the common data?
3
votes
1answer
1k views

Is there a way to insert a bookmark into a google Spreadsheet?

I know you can do this with a Google Doc, but how about a google Spreadsheet? I'd like to have a bookmark and then link into that cell of the spreadsheet from an HTML document.
3
votes
1answer
513 views

Qualtrics + Google Spreadsheet integration

I'm looking for a way to use a Qualtrics survey as a data source for a Google spreadsheet. My client wants a google spreadsheet that automatically updates based on the latest survey results. I've ...
3
votes
2answers
3k views

Parsing timestamp in Google spreadsheet with script

I have a spreadsheet with a form that timestamps every time the form is submitted. I'm writing a script to change the background color of the timestamped cell if it is more than 7 minutes after a ...
3
votes
1answer
2k views

Having units in Google Spreadsheets?

I want to be able to input plain numbers like 200, or 3000 and have the cell display 200g, 3kg, etc... But still be able to add the values in the cells. So if I add the two above I'd get 3.2kg on the ...
3
votes
1answer
2k views

Can I auto archive my Google Spreadsheet?

We are using Google Spreadsheets for call logs, which is shared with more than 20 users and we all add entries simultaneously. I would like to know if there is any feature to automatically archive ...
3
votes
1answer
642 views

Google spreadsheets: Charts for black and white printing?

I have a couple of column charts in google spreadsheets that have several columns of data. I would like to print them out in just black and white, since they are part of a larger report that doesn't ...
3
votes
1answer
845 views

How to extract info from the subject line and add to Google Spreadsheet?

I'd like to be able to extract tasks from the subject line of an email in Gmail (using some sort of custom syntax) and then insert the task as a row in a shared Google Spreadsheet. Currently the ...
3
votes
1answer
240 views

What would I use to write a formula only once and have it work on multiple sheets? Google Docs

I want to write a formula once in one sheet and have others call that formula and do calculation in their own sheet. I want to do this because calculations need to be done in 100 other sheets within ...
3
votes
0answers
22 views

Alternate colored lines in Google Sheets that respect filter views [duplicate]

Basically I want the zebra stripes effect for readability but all the methods I've found so far don't work when using filter views. Does anyone know a way or is this not possible? Update: Using a ...
3
votes
1answer
76 views

Problem: Script does not trigger on FormSubmit: Removing Duplicates in Google Sheet of Form Responses based on a column

I am trying to remove older duplicate form responses based on a column using the following code. The credit for the code goes to: ...
3
votes
0answers
42 views

Google Spreadsheets CSV Encoding not UTF-8?

I was assuming that Google spreadsheet exports UTF-8 encoded CSVs. However, this does not seem to be the case — my german Umlauts, etc. (äüöß, etc.) are not recognized, i.e. when opening the file in ...
3
votes
0answers
75 views

IFTTT to Google Drive (Sheets) - How to write on the same row?

I'm looking for a way to get a recipe from IFTTT to Google drive (sheets) in the same row. The recipe writes in the first 3 columns, but if there is anything already in the same row it skips that row ...
3
votes
2answers
63 views

Coloring cells according to the value to which its content is closest

I am trying to make a guide for my dragons on a game I play but I'm not quite sure how to code what I am trying to do. Link ex. if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its row ...
3
votes
1answer
34 views

Highlight/reject cells that contain any of the values from another sheet

I have a sheet/tab that contains nothing but a single column where each row is a word, let's call it wordlist. In another sheet/tab, I would like to make it so that for one of the columns, if the ...
3
votes
1answer
115 views

Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet

I want to bring in data from a separate sheet and filter it. I can get IMPORTRANGE to work like this: =IMPORTRANGE("URL","A:J) But that doesn't filter. I can only get FILTER to work in the same ...
3
votes
1answer
274 views

The default number of rows for “Add more rows” at the bottom of Google Sheets

At the bottom of Google Sheet web application, there is an 'Add' button and '1000' is the default number of rows to be added. Can I change this default number?
3
votes
0answers
59 views

Create button “Add” to add a new row in sheets [duplicate]

I'd like to know how to add a new row copying all formulas from previous row .. What I'm looking for is as follows SR. NO ITEM CODE DESCRIPTION UNIT COST 1 5463345 ...
3
votes
1answer
138 views

How to count rows in Google Sheets that do not contain a specific text

I need a formula that will tell me how many projects are going on at any given time in my Google spreadsheet. My table header looks like this: Project | Jan 1-5 | Jan 6-10 | Jan 11-15... And each ...
3
votes
1answer
83 views

Capture Created Date from form that allows for editing

I've looked through the previous posts and can only find information where it looks like the responder assumes that the form is completed in one go, so the TimeStamp column is the obvious answer to ...
3
votes
2answers
112 views

Google Spreadsheets: Identifying and Working with Any Cells that Match

I am trying to find a function, or a series of functions, that allow me to (1) Identify if any cell in column A matches any other cell in column A, (2) add numbers in Column B that occupy the same ...
3
votes
2answers
272 views

Make dynamic array blank based on adjacent values in Google Spreadsheets

I'm attempting to indicate certain months that fall between the end of one period and the beginning of another. I have the months of the year in column C: values 1 through 12. It's a running snapshot ...
3
votes
0answers
96 views

Color entire row based on the status in Column L [duplicate]

How can I make my spreadsheet look like in the picture below? Starting row 5, I want to color the entire row based on the status in Column L.
3
votes
2answers
1k views

Google Spreadsheet: Finding the SUM of an array where the size of the array is dependent on a cell's contents

Here is a simplified example of what I want: sheet 1 sheet 2 The yellow highlighted areas are areas important to the formula. Essentially Column C finds the row on Sheet 2 with the same Item as ...
3
votes
2answers
100 views

Referencing a Range Name from a different spreadsheet [duplicate]

I would like to create a spreadsheet that contains name, phone number, address and email address of many individuals. I will define the data as a range named "Index". Then I would like to create a ...
3
votes
1answer
3k views

Can we do a link between two Google Docs spreadsheets?

I want to make a link between two spreadsheets, like if I click on a code in a cell, it opens the spreadsheet which the code refers/links to.
3
votes
1answer
422 views

How can I create a Google Spreadsheet whose name and cells auto-complete with cells completed on another sheet?

I'm using Google Spreadsheets to manage projects, and I'm not familiar with scripts or macros (though I'd really like to learn how to use it, so if you have any good tutorials or tips, let me know). ...
3
votes
4answers
12k views

How to collate data in Google Spreadsheets?

I have two sheets with data in single Google Spreadsheets document: SheetA: Date Name CounterA 22.02.2011 Beta 101 22.02.2011 Alpha 2 21.02.2011 Alpha 1 SheetB: Date Name ...
2
votes
5answers
9k views

Conditional formatting color gradient

A few friends and myself have created a Google Spreadsheet where we are keeping track of virtual item sales. One column has costs, one column has income one has profit, and a fourth has a running ...
2
votes
2answers
2k views

How to display google's spreadsheet inside a post in my blog?

I have a public Google spreadsheet in Google Drive and want to display it within my blog post (people can directly see it, not only a link to the sheet). How can I do that? Please help! [Edit] I'm ...
2
votes
2answers
19 views

Set a formula for all cells in a column

I would like to use a same formula for cells in a column. Instead of writing it for all cells, is there a way to set it in a cell and tell to apply this formula to all cells in a column (or any set of ...
2
votes
1answer
59 views

Insert current date to cell based on content in other cell

Background: I am trying to create a sheet which tracks my costs on daily basis. Instead of entering the current date everyday, I want the sheet to store the current day based on the content I enter ...
2
votes
1answer
65 views

Have formula copied to entire column in Google Sheets

I'm trying to make a spreadsheet that displays the average of a row, i.e. =average(c3:al3) and the function is in column AN3. I want to copy that function down a row (but need to do this for 560 ...
2
votes
3answers
442 views

How to calculate difference in hours between two time fields which runs from one day to the next

I've found a formula that will calculate how many hours between 2 time perfectly well if they are in the same day, but as soon as the times go from one night to the next morning I end up with a minus ...
2
votes
3answers
1k views

Extract and sum numbers from a cell containing text that contains numbers

Suppose cell A1 contains a string that includes substrings that are either integers or decimals, the decimals being of a form that would match the regex \d+\.\d+ . For example, the contents of cell A1 ...
2
votes
2answers
3k views

How do I write or read Google-Sheets “comments” on mobile devices?

In a Google-Sheet called Field Schedules for Google web-apps at http://drive.google.com you can right click on a cell to open a context menu with the option to "Insert comment." Let's say for example ...
2
votes
3answers
3k views

how do I get named ranges in Google Spreadsheets to be more reliable/current?

I'd like to pull values from a named range into a calculation. I have this function: function getNamedRange(n){ SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n); } Seems pretty simple. I ...
2
votes
2answers
5k views

Can I sum based on a specific cell text value?

I have a spreadsheet for tracking sales leads. Each row is a lead. One of the columns is called "Status" and the options under this column are limited to a list of 4 options, i.e. Prospect, Proposal, ...
2
votes
1answer
6k views

Embed a Google Spreadsheet in a forum post

I want to embed a Google Docs spreadsheet in a forum post so that it is easily viewed by people. The spreadsheet should be read-only in the embedded status. Any way to do it?
2
votes
1answer
8k views

Take SUM of colored cells in a column in Google Spreadsheets?

I have a column of numbers, where I've changed the background color of some of the cells to blue. All of the other cells in the column have the regular white background. Is it possible to create a ...
2
votes
3answers
109 views

Does Google Sheets have sheet-level named ranges?

Microsoft Excel allows sheet-level named ranges. See here for an explanation. Google Sheets does have "named ranges" functionality, but I have only figured out how to use it on a workbook-level ...
2
votes
2answers
239 views

Return multiple rows with same value in first column

I'm working on a document that I would like to have a sheet that searches another sheet for a specific value that I type in (a name) and return content from all of the rows that have that value in ...
2
votes
1answer
2k views

How to write this Google Spreadsheet percentage calculation function?

I track my diet (macros) in Google Spreadsheets. For 1 day my protein is 114.63 carbs 194.75 and fat 69.15, the 70.54 is sugar which I'm not sure I should include. The total of all 3 is 378.53 ...
2
votes
2answers
50 views

Prevent “invoked too many times per second” error in Google Apps script

I need a number to be converted to a name. In B1:B1000 I have numbers. In C1:C1000 I has function name. On writing C30 got an error: invoked too many times per second ... how do I do function on range ...
2
votes
1answer
2k views

Calculate compound interest rate in Google Spreadsheet

Assume the PV=100, FV=150, yearly IR=X, number of period = 60 (5 years of interest and compounding is monthly basis) Therefore: FV = PV * (1 + X / 12)^60 How can I calculate X easily? ...
2
votes
2answers
2k views

How to stop Now() function from auto-updating?

I'm using Google Spreadsheet's now() function as a shortcut to insert the current timestamp now. However, the value in the cell changes whenever the spreadsheet is modified in any way, even when the ...
2
votes
1answer
907 views

Conditional Cumulative Sum using Arrayformula

I saw a nice solution for the cumulative sum problem here: Cumulative Sum without Script My question is how do you make this conditional? I mean cumulatively sum only numbers that belong to a certain ...
2
votes
3answers
129 views

How do I cancel editing a cell, in Google Spreadsheets?

I sometimes double-click on a cell to see the formula or whatever, or I just accidentally start typing in the cell. How do I cancel the edits? Esc and Ctrl+Z don't work, so I typically hit Enter and ...
2
votes
2answers
888 views

Google Spreadsheet, put data in a row based on whether a cell has an X in it

I have a list of tutors in a Google Spreadsheet: Sheet 1 Name | Address | Phone | Profile | Subject1 | Subject2 | Subject3 | ... | SubjectN I would like to populate additional sheets with the ...