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

learn more… | top users | synonyms (2)

3
votes
1answer
1k views

Duplicating a set of columns with various widths within the same spreadsheet

I'm trying to duplicate a set of 5 columns multiple times on the same sheet. For example, column A is wide, columns B & E are medium width and Columns C & D are narrow. I want to duplicate ...
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
683 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
886 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
241 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
9 views

Getting a sum of cells (bottom to top) in Google Sheets until a specific value is matched

I'd like to sum up values of specific cells in a column (In this case column A) from bottom to top, in order to get the latest added values, until they match a specific value (if greater than zero) ...
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
2answers
48 views

How can I sort in Google Sheets by cells containing section (or version) numbers?

I'm indexing a document with sections and sub-sections. I want to have one column representing the section-number and be able to sort by this column. However when the sub-section gets higher than 10, ...
3
votes
1answer
146 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: http://www.jacorre.com/tutorial/remove-duplicate-rows-google-...
3
votes
0answers
145 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
73 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
63 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
428 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
1answer
58 views

Filter rows by a set of numbers from another row

I have row of six digit numbers where I want to filter rows containing exact numbers set. For example: orderid 154934 154935 154936 154937 154938 154939 154940 I want to filter 154935,154937 these ...
3
votes
0answers
61 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
184 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
109 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
117 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
287 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
97 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
102 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
481 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
10k 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
20 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
87 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
81 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 rows)...
2
votes
3answers
614 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
6k 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
9k 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
1answer
47 views

How to find URL of the current sheet

In Google Sheets, how do I create a hyperlink to this sheet? I have a sheet which I'm about to download as PDF for archive. It would be useful to include a link in the sheet, pointing to itself, so ...
2
votes
2answers
580 views

How to update Google “My Maps” when Spreadsheet data changes?

In the process of building a Google My Maps map, I found that it's quite handy to import spreadsheets from your Google Drive to use it source for your map layers. As using spreadsheets is pretty much ...
2
votes
2answers
166 views

How can I embed a chart from a Google Spreadsheet in a Google Doc?

I would like to embed the chart from a Google Docs spreadsheet into a Google Docs Document, so changes to the original will be reflected in the embedded version. I found a way to embed a static image ...
2
votes
3answers
252 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
358 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
3k 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
52 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
1k 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
151 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
973 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 name,...
2
votes
1answer
2k views

How do I prevent that references in my formula change when I copy the formula to a new cell?

I'm trying to use submitted Google Form data in one spreadsheet to locate and retrieve data from another spreadsheet (tab in same document). The Google Spreadsheet file is here. The =QUERY statement ...