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

learn more… | top users | synonyms (2)

4
votes
2answers
41 views

How to add up the correct answers from the check boxes question?

What syntax do I use when adding up the number of correct answers that have boxes checked? My question is; In this example the correct answers are Cotton and Calico. I award the students a total of ...
1
vote
0answers
17 views

dropdown script https://drive.google.com/open?id=1623xEoNJABNoqBhT4MhynWpiSsVBjuLylt5rPW9af1o [duplicate]

I have a script to populate a dropdown list based on a selection of another cell's dropdown list. E.g., if user does not make a choice from D9 dropdown menu, therefore F9 dropdown is Blank but has ...
1
vote
0answers
31 views

Filtering/consolidating data from multiple areas in Google Sheets

I am collecting data on a Google Form where users select a name then answer specific questions depending on what name they select. This leaves the resulting response spreadsheet with many blank ...
2
votes
1answer
103 views

Google Spreadsheet Scatter Chart with Multiple Data Series

I want to create a scatter chart with Google Spreadsheet which turns out to be very difficult. Based on a similar question here. I came up with this approach: What I want to achieve is sketched ...
2
votes
1answer
24 views

When creating a 'Checkboxes' question in Google Spreadsheets, what formula do I use to add a mark to each box ticket correct to the answer?

I am creating a Google Spreadsheet for the first time and I have suddenly become stuck. When filling out my spreadsheet I do not know what formula to use when adding up the correct answers. I have ...
2
votes
1answer
62 views

Dropdown list based on the result of another cell's dropdown

I want to populate a dropdown list based on a selection of another cell's dropdown list. Here is my spreadsheet. E.g., if user does not make a choice from D20 dropdown menu, therefore F20 dropdown is ...
2
votes
1answer
29 views

Using a script to process form input every time a form is submitted

I'm trying to have it that every time I submit a form, it adds automatically some data. This is the script I have: function onFormSubmit(Test) { var sheet = SpreadsheetApp.getActiveSheet(); ...
4
votes
1answer
22 views

Using query command with dates

I have a column in Sheet1 that contains dates in format DD/MM/YYYY, like this: 12/10/2015 28/09/2015 28/09/2015 28/09/2015 28/09/2015 28/09/2015 28/09/2015 26/10/2015 26/10/2015 26/10/2015 26/10/2015 ...
3
votes
1answer
34 views

Is it possible to have a Data validation with a Custom formula WITH a dropdown list?

I currently have a Data validation of Custom formula on a cell, B8, that looks a little like this: =or(and(B7="Yes",isblank(B8)),and(B7="No",B8>=75,B8<=100))` I'd like to turn the latter ...
6
votes
1answer
155 views

Convert all text to UPPERCASE in a Google Spreadsheet

I have a spreadsheet I'm sharing with others to collect some data. Each person is just (supposed to be) putting a single character in any of a range of cells. How can I convert those cells to convert ...
2
votes
1answer
42 views

Combine two columns into a list of all possible combinations of entries

I have two columns such as: a 1 b 2 c and I need to combine them like: a 1 a 2 b 1 b 2 c 1 c 2 Is it possible with a formula?
3
votes
1answer
36 views

Named Range insert by mouse

In Google Sheets, when I start to enter a formula and want to insert the reference to a cell, I just click on that cell and get the reference by row and column. However, if the cell is the part of ...
2
votes
2answers
19 views

Added rows in Google Sheets missing formula

I have made a simple formula, based on cells in the same row, to indicate to the user that they have entered enough information—and it works as needed. ...
1
vote
1answer
21 views

How to append the value of a cell to the output in another cell?

I'm trying to append the value of a cell into my string output. Example I have a dropdown selection of Apple and Oranges in A1. I want A2 to say You've picked [input value from A1], how would I do ...
2
votes
0answers
34 views

Run two timestamps for time in/out in one Google Sheets [closed]

I have this code: function function1(event) { var timezone = "GMT-8"; var timestamp_format = "hh:mm:ss a"; var updateColName = "Agent"; var timeStampColName = "Start Time"; var sheet = ...
1
vote
0answers
8 views

Error message when using Trace Dependents code

I attempted to use your code to trace dependents in a google sheet found at this link: In Google Spreadsheet, finding what formulas reference a given value When I attempted to run it I received the ...
4
votes
1answer
27 views

Highlighting the maximal value of a column with conditional formatting

I have a simple column of numbers here (A2 to A10) in which I want to highlight the maximum value in that column. Instinctively I went to conditional formatting and learnt that the formula for it was ...
3
votes
1answer
48 views

Google Script Active Cell Validation

A Google Script I am writing that seems to be failing. The first part of this script is to validate that only a specific sheet is being changed. In this case column 3 of my sheet called ...
3
votes
1answer
48 views

Combine two charts in one and auto-update them

I have two tabs Sheet1 and Sheet2 with two columns each: Date and Temperature. I want a single chart of temperatures against date. The problem is that the date columns are not necessarily identical ...
2
votes
1answer
24 views

Make google spreadsheet chart use tab+column name as the series header

I have a chart of two series, Sheet1!Foo and Sheet2!Foo. They are named in the chart the same way - Foo. How can I include the sheet name in the series name?
3
votes
1answer
36 views

Setting values with if and then & numbers across sheets

I want to format a cell in Google Sheets to where if a cell on a different sheet (titled "Additional Options") has a "yes", the cell on the current spreadsheet is 1, but if it says "no", the cell on ...
1
vote
2answers
48 views

Sorting Pre-Sorted Google Form Data by Timestamp

I have Google Form checkbox data that I am separating out into another table so that each checkbox selection generates its own line. I received some help in pulling the data over, and the resulting ...
2
votes
1answer
34 views

How can specific columns of a Google spreadsheet be exported as CSV?

I have a Google spreadsheet with 10 columns of data. I would like to export columns A, C, E, F as csv. I do not need to export the entire spreadsheet. Can this be done? I do not mind using Google ...
1
vote
1answer
38 views

Google Sheet get value from a REST API

I need to include in a cell on a Google sheet the value of the ticker.last attribute in a JSON which is being constantly updated from a REST API located at https://www.mercadobitcoin.net/api/ticker/ ...
3
votes
1answer
61 views

How can I sort a data with named-ranges without breaking references to the ranges?

How do I create a named range which refers to cell content, not cell location? Or, how do I sort but preserve a named range? I have a Google Sheet which has several named cells. When I sort the ...
2
votes
1answer
16 views

Date automation down columns

So I know how to get google spreadsheets to automate dates down a column. Write a date, left click on the crosshair in the bottom right hand corner, drag down and let go. The thing I'm hoping to do ...
1
vote
1answer
185 views

Is there a way to generate edit response url for google form

I have a Google Form With a Google spreadsheet to store responses. In my spread sheet I have 4 column: name, email, revenue, and a fourth column Id which is used to identify the particular recipient. ...
2
votes
1answer
78 views

Is there a way to dynamically populate a dropdown depending on the value of other cells?

Is there a way to populate a dropdown list differently depending on the values of another cell? For example, the dropdown in A2 contains Movies and Sports. I want the dropdown in A3 to have Horror and ...
3
votes
1answer
113 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
60 views

Is there a way to disable a cell from allowing input if another cell has data in it?

I'm basically trying to create a simple form on Google Sheets. I'd like to make it so that if a user inputs X into A2, then A3 will be disabled, otherwise A3 is open for input. Is there a simple way ...
3
votes
1answer
34 views

How to create multiple columns from one single column?

I've got information that I gather from emails, from where I just take the lines, pass them through a filter and drop them into a Google Spreadsheet sheet. It ends up looking like this: Element 1: ...
4
votes
1answer
30 views

Google sheets aggregate strings

Can Google Sheets queries aggregate strings? I want to aggregate all locations for all years, so the following: year location 2013 Sudan 2014 Syria 2012 India 2014 Poland 2014 ...
2
votes
1answer
38 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 ...
2
votes
1answer
34 views

Trying to query a range of names in Google Spreadsheets, query is failing with the space in the names

I'm trying to query a range of names in Google Spreadsheets. When I try and make a query like: =QUERY('QA Import'!A1:H, "select A where G = "&B2&"") where B2 is John Doe I get the error ...
2
votes
1answer
17 views

Google spreadsheet: Show 2 formulas or more in a cell

I just want to know if there's any way to show 2 or more formula values in the same cell. P.E. =ARRAYFORMULA(A1+A2) =ARRAYFORMULA(B1+B2) =ARRAYFORMULA(C1+C2) Reason: I want to make a ...
1
vote
1answer
26 views

Keeping track of prices, recording every change above some threshold

An updated price comes in to a cell each minute "googlefinance()". In a second cell we start with a reference price which is typed in manually. The initial value is equal to the first price that comes ...
2
votes
1answer
34 views

Auto calculate and display timestamp in a specific row and column

I have a spreadsheet with a formula that works to display the timestamp in the same row, but is there a way to set a column at the same time so I don't have multiple time stamps displaying along that ...
1
vote
1answer
30 views

Google Sheets not sum numbers correctly

I have a sheet with many numbers generally in range 1.5-0.5 with lots of decimal places, however google sheets isn't doing sum on them correctly it returns 0. I need all the decimal places so I can't ...
3
votes
1answer
107 views

Send selected cells from a row via email

I am starting with Google Scripts and I was hoping to create a sheet that could email selected cells from a row. What I want to do by selecting Send Email at the end of a row it then selects pre ...
1
vote
1answer
47 views

“sort” function results in all of my data going to the bottom of the sheet

I am trying to use a form to create a list of jobs for our student employees, which exists as an editable Google sheet. I would like to be able to have any staff member add jobs that need to be ...
3
votes
1answer
82 views

Deleting all comments in Google Sheets at the same time

There are over 200 comments in our shared Google Sheet files and we need to clean all the comments faster. Deleting the comments one by one by clicking Resolve is a waste of time. Is there another way ...
1
vote
0answers
16 views

How do I dynamically load all data from an XLS file into Google Sheets?

I have an automated service that places a new, huge (~6MB: ~20,000 rows) .XLS file into my Google Drive every day. I want to write a Google Sheets .gs script that will run once a day, and load all the ...
3
votes
1answer
80 views

Querying data through importHTML function in Google spreadsheet

I am trying to import table data into a Google spreadsheet using the below function: =ImportHTML( "http://eciresults.nic.in/StatewiseU05.htm" , "table" , 9 ) Following is the result I get out of ...
2
votes
0answers
568 views

Cannot retrieve data for some exchanges from Google Finance

Since last weekend, I encounter problems retrieving data from exchanges such as SGX and SHA. =GOOGLEFINANCE(SGX:ES3,changepct) =GOOGLEFINANCE(SHA:000001,changepct) The error message is; When ...
0
votes
1answer
21 views

Conditional formatting rule to apply to across row [duplicate]

I have set a conditional rule so that if a specific cell equals "y" then the cell background is changed to green. Is there a way to apply this rule so that it will change the background colour for ...
2
votes
1answer
27 views

Why can't I refer to another cell with the value comparator field?

This is more a curiosity question than a fix to a problem, because I found a few ways to fix it already. From what I could gather, if I want to apply a conditional formatting based on the value of ...
0
votes
1answer
30 views

How do I modify this Google script to pull the email from column E in the Google sheet?

Instead of assigning one static email to the "var email" below (or for that matter multiple emails separated by commas), my question is how do I enter the column E value from the Google Sheet this ...
3
votes
1answer
45 views

How to get information from Google Sheets to send it via form emailer?

I created a Google Form. There is a field called email. With this I can receive emails from the submitter. I want to send them a confirmation email taking some information from the sheet. How can I do ...
5
votes
2answers
465 views

Insert image from Google Drive into Google Sheets

How do I insert/link to images existing in a folder in Google Drive into a Google Sheets spreadsheet? There is the image() function, I imagine using it something like this: ...
4
votes
0answers
53 views

Running inventory list that references a product key

So I'm trying to create a rudimentary inventory management system in Google Sheets. I want this inventory guide to keep track of my product inventory, as well as my packaging inventory. So if a ...