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

learn more… | top users | synonyms (2)

3
votes
1answer
70 views

Repeating functions inside “if”

I was creating a spreadsheet the other day and I stumbled across the following "problem". I wanted to test the result of a function against a value and if that was true, display the result of the ...
3
votes
1answer
3k views

Google Spreadsheets Data Validation - Unique Values

I have a google sheets document which contains two sets of data in separate worksheets: Public IP addresses Private IP addresses In my private IPs worksheet I want to create a dropdown which ...
3
votes
1answer
626 views

Google Spreadsheet protected cells

I have created my spreadsheet with protected cells scattered throughout the document in google docs. Now I want to duplicate the spreadsheet and have the protected cells in the new worksheet keep the ...
3
votes
2answers
345 views

Google spreadsheet: Cannot select pre-selected date in Date picker

In a Google spreadsheet, when having a cell formatted as date, or with a data validation set on date, a date picker appears when double clicking this cell. If the cell is blank, the picker's date is ...
3
votes
1answer
5k views

Panel with Vertical Scroll Bar that allows navigating to different worksheets

I am attempting to build a Script that would open a window with each worksheet I have in my spreadsheet, allowing me to click on the worksheet name to navigate to that sheet. I found the following ...
3
votes
1answer
180 views

Data Validation Criteria

I am currently trying to change cell validations cell range in Google Spreadsheet base upon a cell's input. For instance, if A1 = 123 then have B1 (which is the data verification cell) pull from ...
3
votes
2answers
2k views

How to add minutes and seconds only, in a Google Spreadsheet?

How can I add minutes and seconds only in a Google Spreadsheet? Example: I want to add 1201:01 to 390:35.
3
votes
1answer
181 views

Compute average over month when measurement is irregular

I want to measure my power consumption. So once in a month I note my meter reading. So I got the following data: 2012-12-01| 893.8| | |31 2013-01-05| 977.2|34|2.45|31 ...
3
votes
1answer
483 views

How to report which checkboxes were left unchecked on a Google Forms submission?

I have a Google form that stores its responses in a Google spreadsheet. It's set up so that whenever someone submits the form, I receive an email report of the responses. There are many places in the ...
3
votes
2answers
3k views

Conditional formatting based on cell values in same column

In Google Spreadsheet, how do I change a cell's background color to red if the date in the cell matches the date in another cell in the same column? Column E in my spreadsheet is a list of due dates ...
3
votes
1answer
1k views

Delay in updating referenced cells when using importrange in Google Sheets

I'm using the importrange() function in Google Sheets to reference data from 'spreadsheet1' and display in it 'spreadsheet2'. This works fine, but if I edit 'spreadsheet1' and then go to ...
3
votes
1answer
2k views

How do I Sum Thoughout Different Sheets

How do I create a reference to a defined interval in several sheets? Something like this: =SUM(Sheet1:Sheet2!A1)
3
votes
1answer
258 views

Statically positioned chart (ignore scroll)

I have a Google Spreadsheet with 4 columns of data. I have a chart on the right side. When you scroll down to see all the data, the chart scrolls up off the screen. Is there a way to make it static ...
3
votes
2answers
3k views

Google spreadsheet: named range built upon formula

I have a list of items (cells A1 to A7) that are counted in cell B2 (whose value is currently 7), B2's formula is something like =COUNTA(A1:A10000). That way if I add some value in cell A8, the count ...
3
votes
1answer
977 views

Single Response Spreadsheet (with multiple sheets) from multiple forms

I have four surveys (google forms) from which I want responses to go to a single response spreadsheet (each on a different sheet). It appears in the form view that I can select to have the response ...
3
votes
1answer
1k views

Google Apps Spreadheet SUMPRODUCT issue

I have a table with the headings Status | Currency | Total In the rows I have a variation of statuses and currencies. Using Google Spreadsheet I added this: ...
3
votes
1answer
1k views

Google Spreadsheets: add unique QR code field to a spreadsheet based on a form in google docs using Script Trigger

At the end of each row filled in, using a custom Google Form, I'm adding three more columns: Date of creation Unique ID QR code (it is a list of computers) Unique ID and QR code are unique. QR ...
3
votes
1answer
327 views

In Google Spreadsheets how do I put in a formula in after a transpose?

In my spreadsheet the transpose formula fills in a deliberately variable number of column cells, how do I fill in a cell after a transpose formula? I've created a youtube video to demonstrate (click ...
3
votes
3answers
2k views

How to apply a function to all the rows preceeding the current one in an arrayformula?

So I have the following situation: In the first column I have certain values, in the second column, given a certain row, I want it to contain the result of a function applied to all the preceeding ...
3
votes
1answer
529 views

Import CSV, show formatted hyperlinks, =Hyperlink(..,..)

Based on a csv import, I would like to show formatted hyperlinks. There is a formula =HYPERLINK( "www.google.com" ; "Search Google" ) that does this, but is there any way to include this in a csv ...
3
votes
1answer
301 views

Google Spreadsheet text cell formatting question

I'm trying to create some sort of automated formatting for certain columns (except headers of course.) to take a string of numbers (and previous formatting; which should be stripped.) and format it as ...
3
votes
1answer
246 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
540 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
662 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
859 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
9 views

Populate column with editable range based on drop-down

So, here's my issue. I've got three sheets. One sheet has all of the classes for Mondays and Wednesdays, one sheet has all the classes for Tuesdays and Thursdays. The third sheet has a drop-down menu ...
3
votes
0answers
24 views

How can we retain authorship when we copy/paste a comment?

We have comments in a Google Spreadsheet that were not originally written by us. The cells the comments are written in already have "Conditional format rules" applied to them (e.g., cell H2). We ...
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
38 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
101 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
57 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
98 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
67 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
42 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
255 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
319 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
55 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
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
154 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
92 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
276 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 ...