One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (1)

4
votes
1answer
3k views

Remove empty rows in Google Spreadsheets

Is there a way to in Google Spreadsheets to remove / delete empty rows? Ideally I'd like to be able to select the columns to check.. ie. Select columns D, E and remove all the empty rows from them or ...
4
votes
1answer
1k views

Normalizing and grouping dates in Google Spreadsheet

I have a spreadsheet pulling together various readings taken of hours heater has run, electricity units use for that period, and outside temperature. For the last 3 months, the electricity usage and ...
3
votes
1answer
150 views

Can a Google Spreadsheet be recalculated if it's not manually opened?

We have some Google Spreadsheets that use the ImportHtml() to pull in some real messy tables. Then on on an alternate page we access the above data and pull it into a nice cleanly formatted table via ...
3
votes
1answer
390 views

Upgrade existing Google spreadsheet to New Google Sheet

I keep getting the message on my Google spreadsheet that I'm using the old spreadsheet. There is an option to create the new Google sheet, but no option to upgrade the existing. Please check this ...
3
votes
1answer
151 views

Is there a way I can set only a certain sheet visible in Google Spreadsheets?

So lets say I have a Spreadsheet and I press the share button to Public (no edit except me). I have 2 sheets but only want other users to see 1 sheet. Without having to create another document is ...
3
votes
1answer
226 views

Monitor webpage changes using Google Spreadsheets

I want to monitor a web page. The best solution I found was Google Spreadsheets (apart from feed43 which has limitations). I'm using the importHTML function in Google Spreadsheet which will update ...
3
votes
1answer
400 views

Looping through the range of cells to test the dates

I am trying to create a function to check the dates in 'Dt' sheet and return Match or Not Match. It works fine with when I display my answer in MsgBox, but when I try to do it in one of the ...
2
votes
1answer
32 views

Consolidate List of Similar Data into Single Row in Google Spreadsheets

I have a Google Spreadsheet that lists donations given by constituents with a unique account number. Several constituents have made more than one donation in a given period of time listed separately. ...
2
votes
1answer
36 views

How to automatically refresh date in Google spreadsheet published to the web?

I am using Google Spreadsheets to maintain a register of all students in my school and track their monthly payments. Each student has a separate Google spreadsheet maintained and in the spreadsheet I ...
2
votes
1answer
51 views

Is there a way to neatly format length in Google Spreadsheets across multiple orders of magnitude?

I have a spreadsheet with a column for length with values ranging from millions of kilometers to fractions of micrometers. Viewing it all in meters as unformatted numbers directly is awkward since you ...
2
votes
1answer
55 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 ...
2
votes
1answer
51 views

Return name of range as string in Google Spreadsheets

I have a range that is named apple in Google Spreadsheets. I thought a method like getRangeName("apple") would exist. I want to get the names of many ranges and set them as the header for the columns ...
2
votes
1answer
36 views

Moving down the fixed page numbers in a Google spreadsheet

Can you move the page numbers on the left side of the screen down? I have a list that I need numbered, but the very top of the list (meaning the first column) has the categories for said list. So that ...
2
votes
1answer
48 views

Display Columns For this weeks Data

I'm trying to create a to do list schedule like spreadsheet that would show only the data presented for the current week. I've been having some difficulty figuring out how to display the data of ...
2
votes
1answer
74 views

Altering the Sort Filter in Google Spreadsheets

I live in Australia and when I'm putting in dates, I place the date, followed by month then the year. However when I am filtering/sorting my spreadsheet it's coming up incorrectly. How do I edit ...
2
votes
1answer
103 views

MATCH Function does not work

MATCH function does not appear to work at all. Given the following data: $7,109.19 | $867.19 | -$5,374.81 | -$11,616.81 =MATCH(MAX(FILTER(B122:U122, B122:U122 < 0)),B122:U122) returns the error ...
2
votes
1answer
105 views

Applying IF Statement to entire column

I currently have an IF statement that checks a cell in column B if it is blank. If it is blank it returns No in the corresponding A cell. If it does have a string, it returns Yes. I currently have ...
2
votes
1answer
76 views

Get data from specific date in Google Spreadsheets

I’m trying to collect data from specific dates in a Google spreadsheet. For example, I want to get the data from January only. Is this possible?
2
votes
1answer
43 views

Creating a Chart with independently indexed series

I have a data series that looks like this: 0 317.59 1.4 317.59 1.5 408.77 2.15 408.77 2.25 440.46 2.98 440.46 3.08 471.37 3.12 471.37 3.17 ...
2
votes
1answer
2k views

How can I use ARRAYFORMULA and SUM together to get a column with the sum of the proceeding rows

In google docs, I have the following data | A | B | C | ... | N | TOTALS | 1 | 123| 425| 324| ... | 234| =SUM(A1:N1) | 2 | 123| 425| 324| ... | 234| =SUM(A2:N2) | ...
2
votes
1answer
2k views

Import Google Spreadsheet range if a column contains a particular value

I want to import a range of data from one Google Spreadsheet (1) into another (2), but only when Column J in spreadsheet 1 contains a 'yes'. I'm using the following formula: ...
2
votes
1answer
418 views

Automatically create a new row each day, on top of older rows

I am looking for a way to automatically create a new row on top of older rows each day. Inside of this row should be the current date.
2
votes
1answer
107 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 ...
2
votes
1answer
614 views

If cell equals a value then move row to different Google Spreadsheet

I've got a script that sorts a Google Spreadsheet when a value in a column is changed. I'd like to add to the script the following functionality: If the value for column E = "Complete" then the ...
2
votes
1answer
278 views

How to do a date formula in Google Spreadsheets

I have to do a query that increments automatically the date year by year. For example I use this formula: =INDEX(query('Scheda Intervento'!C3:K14;"select sum (K) where (F >= date '2013-01-01' ...
2
votes
1answer
1k 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 ...
2
votes
1answer
655 views

How to use a named column in a calculation based on a cell value in Google Spreadsheet

Playing with named ranges, I discovered it is possible to name a whole column (say "September") and use it in rows: =September*K4 What I'm trying to achieve now is that the value of the column ...
1
vote
1answer
19 views

Google Spreadsheet add rows until marker

I have a spreadsheet of the form ItemA Subitem1 5 Subitem2 4 Subitem3 7 <potential marker> ItemB Subitem 4 I want to sum the values next to Subitem to a total next to ItemA, ItemB, ...
1
vote
1answer
25 views

How can I use a single column as a normalized data point with multiple choices in Google Sheets?

I find I often want to have a column that is meant to be one or more choices from a fixed set. E.g. a Codebase field in a dev roadmap spreadsheet, which might have choices like {DB, API, JS, CSS, ...
1
vote
1answer
28 views

In Google Spreadsheet, how do I make cells take a random string value from a list of strings?

Suppose I have a list of strings {apple, pear, banana, orange}. How can I have each cell in a column of cells have a random value from that list?
1
vote
1answer
33 views

How to parse a date and time in the format 2012-12-12T00:00:00+00:00?

I have a CSV file with dates in the following format: 2012-06-20T00:00:00+00:00 Is it possible to parse a date or date and time in this format? The goal is to turn it into a date and time that ...
1
vote
1answer
39 views

Preventing Paste Overwriting Cell Formatting in Google Sheets

I have a spreadsheet used for booking employees onto training sessions. It's very simple to use; the person booking simply enters their employee number into column A and column B, C & D ...
1
vote
1answer
117 views

How to make a protected range appear normal in Google spreadsheets

I have a project in Google spreadsheets shared with several other users. There are sensitive formulas throughout that I have protected to prevent accidental deletion or overwrite by the other users. ...
0
votes
0answers
30 views

get editor name, time stamp, and date after every editing

I have a spreadsheet, on that every cell i want the name of user, new time and date on which the editing done. the formate will be "date, time stamp, and update by" Kindly suggest
0
votes
0answers
173 views

Filtering data based on dropdowns in Google Forms

Is it possible for me to have a choose from a list question in a Google Form and have two dropdown boxes? Such that, the list items in the second dropdown box will depend on the item chosen in the ...
0
votes
0answers
86 views

Sending emails to specified end users

I'm trying to write a script to send emails from a google spreadsheet. My specifications are: Send email upon form submit Only send to email address listed on form I have written a sendEmail ...
0
votes
0answers
78 views

How to automatically “start a new paragraph”\“full stop” in a cell ?

I have some problem to set the automatic start of a new line in a cell of a Google spreadsheet without press CTRL + every time that I want a new line in a cell. I have try to do as specify in this ...
0
votes
0answers
46 views

Using a dynamic OFFSET & ISBLANK() function

I have a spreadsheet I'm working on that we use to run large corporate events. Each segment length has a length as well as corresponding start time. I want each cell in the time column to look above ...
0
votes
0answers
137 views

Use of importdata with arrayformula

I’m using importdata to retrieve Yahoo stock information, which works fine on indivdual rows, but when combined with arrayformula to fill down the page when additional symbols are added does not ...
0
votes
0answers
50 views

A simple spreadsheet sorting

In a Google Spreadsheet I have different groups of cells in one column to auto-sort. All cells in this column have same pattern of data, all datum have it's own cell and it's needed to be sorted into ...
0
votes
0answers
186 views

How do I reference only the elements of a multi-column range that meet particular conditions?

I have a spreadsheet made up of several individual sheets: one sheet for each category of product and one sheet designed to be a "global view." In each sheet is a 3-column range of variable length ...
0
votes
0answers
427 views

How to use Google Scripts w/ spreadsheet and jotform to auto sort so the newest entry is always listed on the top row?

I'm trying to setup a form and spreadsheet database to track inventory on a daily basis at a remote location. The idea is that the person at the remote location can update our stock via a simple ...
0
votes
0answers
39 views

How to set hot keys to a customized menu item in a Google Spreadsheet?

I am working with Google Spreadsheet APIs, and found that customized menu item is highly useful. I created some but am now wondering whether I can specify a hot key for each?
0
votes
0answers
147 views

Download list of contact information and check if Google Account

I'm looking for a way I could: retrieve a list of all my contacts and their email's and In order to retrieve a list of all of my contacts into a Google spreadsheet, I exported them as a Google CSV ...
0
votes
0answers
727 views

How to make Google Spreadsheets recognize data as numeric

I have the opposite of a fairly standard problem -- how do I force Google Spreadsheet to understand that a value is numeric? It seemed like using Format > Number should do the right thing, but it did ...
0
votes
0answers
269 views

Query google spreadsheet and display single column?

I am working on using Google Spreadsheets as a gradebook. I am attempting to display only a student's grade, not an entire classes. I am integrating this into my wordpress site. The function I want to ...
0
votes
0answers
154 views

Create new document within spreadsheet and link

I would like to create a spreadsheet script that allows me to create a new document every time I click on a new cell or row and then have that document links back to that cell.
0
votes
0answers
329 views

Google spreadsheet revision history — too much detail

For word processing, Google Docs revision history is great. However, for some of my spreadsheets, something isn't working right: Revisions are not grouped together, multiple for each minute. "Show ...
0
votes
0answers
443 views

Google spreadsheets - “=ImportHtml DIV” how to?

I play Runescape and I want to make a tool that people can use to make profit in the game. My problem is that I am not sure exactly how do use a command to pull data text from a div element. The ...
0
votes
0answers
1k views

Change value in one cell through a formula in another cell

I am trying to capture a timestamp for when a spreadsheet is first edited, and ONLY keep that first timestamp. However, NOW() updates each time a spreadsheet is edited. I found a working formula ...