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

learn more… | top users | synonyms (2)

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

What's the biggest csv file you can import into a Google Docs Spreadsheet?

I'm trying to upload/import a 7mb csv file to Google Docs without much luck; What's the largest file size that you can import?
2
votes
1answer
16 views

Why is a blank string greater than 0 in Google Spreadsheets?

Why does ="" > 0 Evaluate to TRUE? And how can I input a value that would evaluate to FALSE that is not a number?
2
votes
2answers
35 views

Organize employee time log into a table with a row for each employee

I want to get all the Timestamp of an employee (EmpID) from one sheet to another using Google apps script or otherwise. Explanation In Google Sheets, Sheet1 has the following items in 3 columns | ...
2
votes
2answers
47 views

How can I see Google spreadsheets in Myanmar font?

I want to know whether it is possible to see Google spreadsheet in Myanmar font. Because in my Ubuntu operating system I am not able to see the spreadsheet text in Myanmar font which show only boxes. ...
2
votes
2answers
75 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
91 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
195 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
81 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
87 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
223 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
154 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
1answer
562 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
183 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 = (Session.getEffectiveUser()....
2
votes
2answers
31 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
133 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
2answers
363 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
706 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 | | 01....
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 -------------+-------------+-------------+------------------------- 11/...
2
votes
1answer
625 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 =split(url,"/...
2
votes
3answers
150 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
88 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
188 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
6k 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
418 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
4k 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" ) - SUM(FILTER(...
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
795 views

Combining Stuff in Google Spreadsheet

I want to accomplish the following: string = TEST; cell reference = B2, value = ING; RESULT = TESTING Which of the following options are the best to use: CONCAT: =CONCAT("TEST",B2) & ...
2
votes
1answer
940 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
6k 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
910 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
7k 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
13 views

Export images from formulas in Google Sheets

I have a Google Sheet that has a column generating images (barcodes in this case) based off of data from other cells via the =image(url) function. I thought this would be a great way to quickly create ...
2
votes
1answer
28 views

How to use Google Spreadsheets to chart completed tasks using time series graph?

I have the following columns in Google spreadsheet Date and Completed Tasks Sample data includes May 17, 2016 3 May 18, 2016 10 May 19, 2016 9 May 20, 2016 8 May 21, 2016 9 I ...
2
votes
1answer
35 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
28 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
22 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
41 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
47 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 04/03/...
2
votes
1answer
29 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
40 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
25 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
191 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
32 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
2answers
80 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 ...