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

learn more… | top users | synonyms (2)

4
votes
1answer
250 views

How to nest ImportRange into the query function in the new Google Sheets

We have applicants who come in and fill out a form via Google Forms. I have fields that auto-populate from the Response tab into a separate tab that is organized to fit our trackers. I am looking for ...
2
votes
1answer
178 views

onEdit only updates top row after pasting (editing) multiple rows

I'm using a Google Spreadsheet and have a script that updates the "last email" column with a date stamp when an employee enters an email address into the prior column. The problem occurs when my ...
3
votes
3answers
13k views

How to automatically insert a new row and retain functions/formulas from last row?

I have a table with cells that have functions/formulas, like this one: I need a script that creates a new row, copying with it the functions/formulas of the last used row. I find this script which ...
1
vote
1answer
271 views

Google spreadsheet multiple auto-sorting

In a Google Spreadsheet I have different group of cells to auto-sort ascending i.e. table range a3:c16 columntosortby = 2 table range d18:f31 columntosortby = 5 table range g33:i46 columntosortby = ...
2
votes
1answer
26 views

Importing columns across two Google Sheets in same spreadsheet

I have one spreadsheet which houses the database of my members. In the same spreadsheet, I have another sheet where I will track attendance for the events for each of these members. First issue: ...
1
vote
2answers
383 views

Dynamic generation of cell list / array from range value in Google Spreadsheets

I want to create an array/ a list of cells (i don't know the exactly terminology), in rows or columns form, covering the range between 2 numbers Fox example, I have 2 cells, A1 = 3; B1 = 10 I want ...
0
votes
1answer
50 views

How can I collect the sum of different columns while filtering?

I am creating a travel budget spreadsheet and need to be able to sum different filters (By Month, By Salesperson, By State, etc)...is there a way for me to display the sum of a column based on what is ...
3
votes
1answer
13 views

Multi-column Conditional Formatting and Row Export in Google Sheets

I need to (a) indicate and (b) export several columns of data into a separate sheet, but only the rows that satisfy a certain condition: three columns (which are not exported) must be after today's ...
3
votes
2answers
86 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 ...
2
votes
0answers
28 views

Adding columns automatically to Google Sheets

I am using Zaption and Socrative as assessment tools while teaching. After I download the CSV files, I import them to Google Sheets. I figured out a way of having the results sent to a PDF after ...
0
votes
1answer
96 views

Trying to query one sheet to inform another

Someone at the google+ apps script forum suggested I ask you guys about this project I'm working on. See the whole (pretty short) thread here: ...
1
vote
1answer
49 views

Associate cells to imported ones with importrange in Google Sheets

I have the following issue: I have table A with several columns and I use the following function to import some of data to table B: ...
1
vote
1answer
13 views

Trigger Script in Spreadsheet in Similar Fashion to a Link

I currently have a button on my spreadsheet (a text box image that triggers the script). Unfortunately this button doesn't work when using a tablet. Is there a way of triggering a script to run via a ...
1
vote
1answer
12 views

Method to filter based on dropdown list of months

I'm trying to convert an Excel spreadsheet into Google Sheets. I have the entire year on a sheet and would like to filter the list based on a selected month from a dropdown. For example, selecting ...
4
votes
0answers
59 views

Embedding a Google Visualization Chart inside a Google Spreadsheet

Unfortunately, the embedded charts in Google Spreadsheets do not have the customization I want. I would like to use a Google Visualization Chart instead. The documentation discusses how to use a ...
2
votes
1answer
15 views

How to split then combine data to rows

I have a Google Spreadsheet that receives data from a Quickbooks invoice entry. The data from item description has the quantity of each item separated by commas, then the items which are also ...
0
votes
1answer
16 views

Multiply cell value depending on keyword

I would like to apply a transformation to a table which basically looks like this: If there is a certain keyword in column A, then the number in column B should be multiplied by 2. Is that possible ...
0
votes
2answers
127 views

Count word occurrences in survey feedback

I have a suggestions column in a survey feedback, that's being pushed from the survey provider to a Google Spreadsheet. I'm trying to do a bit of wording analysis on it. For example, I'm trying to ...
2
votes
1answer
227 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 ...
0
votes
3answers
144 views

Can Google Forms & Sheets serve as a kind of DB?

Pardon the naive way I am expressing this question. I'm a teacher trying to create a database of grades. I'm trying to figure out if Google Sheets and Google Forms is sufficient for my task or if I ...
0
votes
1answer
17 views

Script to Create Copy (Backup) of entire spreadsheet onOpen

Is it possible to write a script or is there another method of backing up a spreadsheet when opened? The reason is: I have other users opening my spreadsheets and if they completely stuff everything ...
7
votes
4answers
6k views

How can I get Google Sheets to auto-update a reference to another sheet?

I am using an external application to enter data into a Google Spreadsheet. This workbook is then referenced by a separate workbook (with the IMPORTRANGE(spreadsheet_key, range_string) function) that ...
0
votes
1answer
736 views

Query multiple Google spreadsheets

I have the following sheets: ListA ListB ListC Note: at each sheet I have a column which contains YES or NO. How do I query all my lists (spreadsheets) where myBooleanColumn equals to YES/NO?
2
votes
1answer
13 views

Creating a copy of a sheet by copying values only?

I use the below script to basically duplicate an existing worksheet. The worksheet I am copying contains the ImportRange. I don't want the copy spreadsheet to have the formula just the value from the ...
2
votes
1answer
845 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 ...
1
vote
1answer
13 views

Using IF Formula to get data from another Cell

I am just trying to use the IF formula: COLUMN A COLUMN B COLUMN C Cats NULL Dogs Monkeys Kittens NULL ...
2
votes
1answer
116 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 ...
0
votes
2answers
84 views

Google Spreadsheet criteria if cell above is blank

I am trying to get a sum of column values in a Google Spreadsheet only if there is no value in the column above. Consider the following example: A | B | C --------------------------------- ...
4
votes
0answers
43 views

Retrieve the distance between two cities

Credit: Google Spreadsheets function to find distance between cities function getDirection(city1, city2, unit) { var directions = Maps.newDirectionFinder() ...
2
votes
3answers
375 views

How can I filter a full table based on only unique values in a single column?

How can I deduplicate a table (all rows and columns) based on the values in column A? Example: What I want after deduplicating: Basically, I only want unique values in column A, but I want all ...
2
votes
1answer
75 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. ...
4
votes
2answers
663 views

Partial sum of columns based on row identifier

I have a spreadsheet with list of persons ("players"), and for a each person I have following values: ID number (unique) Name (just text) Score from round 1 Score from round 2 ... Score from round ...
2
votes
1answer
353 views

Date Formatting for Deadlines

I have set up a Google spreadsheet for managing projects and due dates, and I need to apply the following formatting to a column of dates, but I'm not sure how: Dates more than 7 days from today: ...
-1
votes
1answer
12 views

How do I block rows/columns/cells from certain users on the shared Google Spreadsheet?

I have a spreadsheet with sensitive data. How might I block a certain column from others users viewing it?
1
vote
1answer
19 views

How to change the size of a published Google Spreadsheet chart that has its own sheet?

Google Spreadsheet offers the option to put a chart on its own sheet (instead of inserting it in the current sheet). This looks like a good idea for large charts. However, I ran into an issue when ...
8
votes
2answers
5k views

How to reverse the order of selected rows?

Is there an easy way to reverse the order of selected rows? (Or how about all rows, if that's easier.) Specifically, in this case, there is no column that could be used for sorting to get them in ...
0
votes
1answer
94 views

Find the nth occurrence of variable, look at adjacent cell and organise in an array

I have a Google spreadsheet with two arrays: The first is a list of ~100 names (in a single column) with the adjacent column being the variable (A,B,C,D,E,F,G,H,I or J). The purpose is to group people ...
3
votes
3answers
2k views

Google Spreadsheets date and time format

I have lots of dates in the first column in the format August 23, 2010 at 11:01PM I was wondering if anyone knows a way to get them formatted in a way that I can then use for date and time ...
4
votes
2answers
755 views

Is it possible to select a whole column for chart data in Google Spreadsheets?

I've got a Google Spreadsheet that's written to by a sensor system approximately every thirty seconds. Each insertion adds a row to the bottom of the spreadsheet. I've got a graph of this data. ...
1
vote
1answer
1k views

Script error trying to populate form drop down menu from spreadsheet

We are trying to link a list on a Google Spreadsheet to a drop down menu on a Google Form. The twist is that this Google Spreadsheet list will be continuously updated and needs to automatically ...
1
vote
1answer
14 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? Technically I can ...
2
votes
1answer
30 views

Print text associated with a value

I have a Google Sheet that collects data and analyses any trends. What I'm trying to do is figure out how to output the text value associated with the numerical value. Example: Apples 7 ...
0
votes
1answer
208 views

Time Triggered Script to Change Cell Contents

What I want to do is for example: A time will be manually entered into a cell say D3, when it is 20 minutes before this I want cell D1 to change to "20 MIN", WHEN IT IS 5 minutes before I want to ...
1
vote
1answer
1k views

Multiple views of master spreadsheet that allow editing of master data

I have a monster spreadsheet with lots of contacts in it. Each contact is a row, with different attributes as columns (first_name, last_name, number, etc.). There are a hundreds columns—i.e., too many ...
2
votes
1answer
96 views

How to get more than 1 question to populate the same field/column in the Spreadsheet?

I am looking for some way to get the submitted answers to 2 or more questions to populate in the same column in the response spreadsheet, but on the next row. e.g. When gathering data from ...
3
votes
1answer
87 views

Google Sheets auto-format the highest value in a row of cells

I have a set of data like so: A|B|C|D 1 9|3|5|4 2 7|6|6|6 3 3|6|2|7 4 6|9|4|7 And I would like to auto-format the highest value in each row. So, for instance in row 1, A1 would get the ...
60
votes
3answers
111k views

How can I create a fixed column header in Google Spreadsheet like the first sheet?

How can I create a fixed column header in Google Spreadsheet like the first sheet?
2
votes
1answer
46 views

Highlight cells, which content can not be parsed as number

How can I highlight cells whose content is not parsable as number? thanks!
1
vote
1answer
42 views

Query(ImportRange) throws “Formula Parse Error”

I'm working with Google docs and I've run into a wall with a query(importrange) function. =Query((IMPORTRANGE("URL","Sheet Name!A2:P1000"),"select ...
3
votes
1answer
28 views

Is it possible to embed a Google sheet's in a non google-site website?

I'm creating an internal site for my users and I don't want to be constricted by the limitations of hosting a site through Google sites. But I also want to be able to embed Google documents and ...