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

learn more… | top users | synonyms (2)

2
votes
1answer
913 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
889 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 ...
2
votes
1answer
1k 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 ...
2
votes
3answers
876 views

How to count the occurrences of 'STRING' in a row, but only if that row begins with 'IFTEXT'

I have a sheet with employee time logs, as follows: Name Day1 Day2 Subtotal of 'OFF' days this period Jess PRESENT OFF 1 Bob PRESENT PRESENT 0 Name Day3 ...
2
votes
4answers
5k views

Google SpreadSheet conditional formatting based on number of characters

In Google Spreadsheet, is there a way to conditionally format a cell if it has more than 30 characters?
2
votes
1answer
3k views

Get rid of row in result of Google Spreadsheets query

I use the following formula in my Google Spreadsheet: QUERY(Inkomster!A2:G;"SELECT SUM(C) WHERE G="&B3&" AND F='"&A3&"'") This returns the following (On two different rows that ...
2
votes
1answer
3k views

Is there a Google Docs (or -spreadsheets) API to scroll the view?

If I want to scroll to a particular paragraph in Google Docs, or a particular cell in Google spreadsheets, is there a way to do that via Google Apps script?
2
votes
2answers
55 views

Filtering by case-sensitive string equality

I discovered (when answering this question) that string comparison in filter is case insensitive: the formulas =filter(A:A, B:B = "Yes") and =filter(A:A, B:B = "YES") have the same output. ...
2
votes
2answers
72 views

Google Sheets: how to resolve emails to user names

We have a Google Apps account for our organization. When you create a Google Form using your Apps account, there is a helpful option to have the form capture the user's email address when the form is ...
2
votes
1answer
194 views

In Google Spreadsheets, how do I make a cell display a value randomly depending on a percentage in another cell

I want to make a cell display the word, 'YES' and 'NO' where the chance of the cell landing on'YES' would depend on the % in another cell. (E.g. 100% would mean there is a 100% chance on the cell ...
2
votes
2answers
102 views

Concatenate several columns into one in Google Sheets

I want to concatenate/combine columns A, B and C into column D as displayed. The number of filled entries in A, B and C will vary over time, so absolute cell addresses cannot be used. What formula ...
2
votes
2answers
56 views

Truncating the Title in the HYPERLINK function in Google Spreadsheet

I have a number of URLs that I first convert from text to the HYPERLINK function and then want to edit the title-section of this function, so that only the domain is visible in the cells. I attached ...
2
votes
2answers
71 views

Lock the formula of a cell, even if the cells it references change

I have this, in D1: =sum(D3:D) I want D1 to always be the sum of the rest of column D (D2 is a header) I sometimes move a row, from eg 8 to 3, and that causes D1 to change to sum(d4:d). I want ...
2
votes
1answer
88 views

Remove header and blank row from the results of a SUM, GROUP BY query

I have a two column table where one column is some sort of key where duplicates are allowed and the other column includes amounts. I then have the following formula somewhere else on the sheet: ...
2
votes
1answer
640 views

Conditional format rule “Text Contains” with a “+” misinterpreted as a formula

I'm trying to conditioan format a bunch of cells that are strings by having cells with a - red and cells with a + green. However when just putting a + o - in the conditional formatting rules it says ...
2
votes
1answer
388 views

Multiple auto-populated select boxes Google Sheets Script UI

I found an example of how to auto-populate a drop-down select box from a Google Sheets range, for a Google Apps Script UI that enters new rows in the spreadsheet. I have so far been unable to figure ...
2
votes
2answers
119 views

onOpen doesn't execute

I've got a google spreadsheet I created yesterday and used Tools -> Script Editor to create a function in a single file called Code.gs function onOpen(e) { var email = ...
2
votes
2answers
30 views

How to output data from Column A and Column B to Column C automatically in Google Sheet

When I insert data in column A and Column B, It should be automatically merged and added in column C. A | B | C =================== ABC CDE ABCCDE
2
votes
1answer
117 views

Import key statistics without a header row

I'm interested in pulling a specific # from Yahoo Finance. I'm looking at American Airlines AAL I have found the table: =importhtml("https://finance.yahoo.com/q/ks?s=aal&ql=1","table",11) ...
2
votes
1answer
2k views

Get unique values from range of cells

Let's say I have a column of values (e.g., first names). There may be some duplicate data (some first names are very common). How can I get the unique values? In SQL I would do SELECT DISTINCT ...
2
votes
2answers
276 views

How to sort cells with text and numbers correctly

This is my example list: example file As you can see I am sorting all unique elements in row B. But the "Sprint 9" gets sorted as the highest value, not the lowest. I really have no clue how I can ...
2
votes
1answer
605 views

How can I COUNT rows with ARRAYFORMULA?

I have a sheet that lists incidents in an Incidents sheet: | Date | Name | Incident | |------------|-------|---------------------| | 01.01.2015 | Vidar | Car broke down | | ...
2
votes
2answers
2k views

Formatting Duration Values in Google Spreadsheet

I have a Google Spreadsheet. In that spreadsheet, I have the following: Date | Start | End | Duration -------------+-------------+-------------+------------------------- ...
2
votes
1answer
573 views

How do you extract a number from a URL as raw text?

So I have a list of Google+ profile links in a Google Sheet https://plus.google.com/112337650380234682548/ How can I extract the number on the end in a seperate column? I've tried ...
2
votes
2answers
588 views

How to get a Google spreadsheet cell to display a number as 4 digits, even if it's less than that

This is a formatting issue. Basically, I would like to get all the numbers I enter into a column become 4 digits in the other column ("output column"), with 0's in front as applicable. E.g. when I ...
2
votes
3answers
144 views

Code invoked too many times

My goal is pretty simple: take the value in column U and check if it's within particular ranges. If it's 1–100, put a 1 in column A. If it's 101–200, put a 2 in column A. The problem is, I have almost ...
2
votes
1answer
87 views

Google Docs sheets: How to refer values in other column by key? (like SQL join)

I have a sheet/document with item number and it's price. In an invoice, I have: | Number | Qty | Price | ... | Total price | 21 | 3 | ??? | ... | = C2 * B2 I'd like the Price column to be ...
2
votes
1answer
166 views

How to make my Google Spreadsheet automatically calculate “days since”

I have a spreadsheet with several columns and one of them is for date (column D). How do I make the next column (E) automatically display the days passed from date in D until now on open? I have ...
2
votes
1answer
5k views

Does IMPORTDATA() refresh when an Apps Script runs even if the sheet isn't open?

We have a Google Spreadsheet that pulls data from an external source via an IMPORTDATA() function and then a Google Apps Script that is on a timed trigger that performs other things with the imported ...
2
votes
1answer
376 views

Updating logging sheet from another sheet doesn't work after inserting code offset

I am trying to log the data, formulas and times of cell edits from one sheet into a logging sheet. The problem I am having is that it only seems to record changes made in the logging sheet. This has ...
2
votes
2answers
3k views

How can I use text from a cell as part of a formula?

I do not want to concatenate a string or show the value like "Some Text " & C12. I need to use the text in a cell as part of the formula in another cell. For Example: I have a cell containing a ...
2
votes
2answers
5k views

Does text overflow a Google Spreadsheet cell if the cell next to it is empty?

In Microsoft Office Excel a long text string is readable over multiple columns whenever the next (right) column is empty. Is that also possible within Google Spreadsheet?
2
votes
3answers
2k views

IF statement in Google spreadsheet

I am trying to sum up the total of all values in a coloumn that are POSITIVE. Also, I have some subtotals in the coloumn that should be left out from this sum. =SUMIF(E6:E65 ; ">0" ) - ...
2
votes
1answer
6k views

Google Docs Spreadsheet - Parse Error

Not sure why this formula does not work. I just want the value to be zero if 60-D15 is negative =MAX(60-D15,0) also tried =MAX(D15,0) =MAXA(D15,0) Any clues?
2
votes
1answer
893 views

Sorting Unique rows by a related Sumif column in Google Spreadsheet

I am putting together a worksheet to keep track of expenses. I currently have the following source table called "Expenses": A B C D 1 Date Description ...
2
votes
3answers
5k views

How to have auto updated current time cell in GoogleDocs / Excel

I am trying to create a spreadsheet that will allow me to track my clients' timezones. I am using the useless NOW() function to calc current time, and then factor in the time zone value (+/- a #) as ...
2
votes
2answers
868 views

Pre-fill a cell with today's date

I would like a certain cell of mine to display today's date when I starts a new row. Is it possible?
2
votes
1answer
6k views

Put Google search inside Google Docs spreadsheet cell

Is there a way to put a Google search widget inside a cell of a Google Docs spreadsheet? I want to avoid my users to hit Ctrl + F every time they search. Would be very cool a Google Docs App that ...
2
votes
1answer
31 views

Validation to check a list of values from found on a different sheet

I have two sheets in my workbook named 'Tasks' and 'Personnel'. The 'Personel' sheet has three columns that contains a list of Person's ID, Name, and Contact Number. While the 'Task' sheet contains ...
2
votes
2answers
25 views

Formula to use to add up all rows that returned a value of 1 for several cells that meet a specific crtieria

This is the spreadsheet I need help with: spreadsheet I need cell B21 in the 'Reports' sheet to add up all rows that meet this criteria: return a value of 1 if columns M, O, T, U, W, X, Z, AA, AB, AC ...
2
votes
1answer
18 views

Combine rows to a column in Google Spreadsheets

I have a question about combining rows to a column in Google Spreadsheets. There are a lot of threads already about this topic, but I couldn't find one about this particular problem. This is the data ...
2
votes
2answers
34 views

Automatically create an array of unique names and their number of wins

I'm trying to make a sheet that I can use to track which heroes I need to practice in a game... Want to manually input the following things: Map name (A2:A), Hero name (B2:B), Result (C2:C) I ...
2
votes
2answers
44 views

How to remove duplicate rows based on one column, retaining the first row for each

I want remove all row with equal date 02/03/2016 1 11 24 7 8 3 22 02/03/2016 1 11 23 7 8 3 22 03/03/2016 1 11 25 7 8 3 22 03/03/2016 1 11 25 8 7 3 22 ...
2
votes
1answer
21 views

How to block automatic row and column insertion in Google Sheets?

While playing around with array formulas, "sometimes" when the result has more rows and columns than the previously available in the sheet, Google Sheets automatically adds the row needed. I said ...
2
votes
1answer
74 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
1answer
18 views

A spreadsheet formula to assign letter grades based on numeric ranges

I'm trying to figure out how to write an IF statement that will be 'true' if a value is in-between two values. Example: If value is between 60 and 69.9 return "D" If value is between 70 and 79.9 ...
2
votes
1answer
24 views

Parse out column with commas in it into separate rows

I have data in the following 2 column format in my google spreadsheet: 3M 701 A.W. Sperry DSA-2002 Accusplit 725MX, 705X, Magnum XL I would like to be able to parse out the 2nd ...
2
votes
1answer
120 views

Forms Data Manipulation In Google Sheets

I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in ...
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 ...