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

learn more… | top users | synonyms (2)

3
votes
1answer
54 views

How do I create a Google Form where the user can upload a CSV?

I'd like to create a Google Form where the user can upload a CSV and each line in the CSV populates a spreadsheet. Is there any way to do this in Google Sheets?
3
votes
1answer
117 views

Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet

I want to bring in data from a separate sheet and filter it. I can get IMPORTRANGE to work like this: =IMPORTRANGE("URL","A:J) But that doesn't filter. I can only get FILTER to work in the same ...
2
votes
1answer
32 views

Google Spreadsheet Inconsistent Datetime Format

When we push date and time values to a google spreadsheet in the format d/m/Y H:i a (eg. 25/12/2015 1:43 pm) the date and time is displayed inconsistently, see below: Any idea why the format is not ...
3
votes
1answer
123 views

Allow data to be overwritten from importrange

We have a specific set of data that is being imported from another spreadsheet with the importrange function =IMPORTRANGE("spreadsheetkey"; "sheetname!A8:F133"). Now we want to change that data on ...
9
votes
1answer
354 views

Google Sheets - Script to change individual text color within a cell

I have a Google spreadsheet with hundreds of employee names for a schedule. Each cell has multiple names within that cell that look like this: First Last First Last First Last First Last First Last ...
2
votes
1answer
25 views

Why does Google Spreadsheet use days as unit of time when entering a number?

When using Google Spreadsheet today, I noticed that when entering a date as a simple number sequence (e.g., 151127), and choosing date as format, it ended up being a completely different date than the ...
2
votes
1answer
94 views

Conditionally substitute cell contents with Unicode characters

Is it possible to conditionally substitute a cells contents, with a Unicode character i.e. rather than colouring all '1' cells green, swap the '1' for another character instead? Specifically, I want ...
3
votes
1answer
17 views

The function isblank returns True even though the range contains nonempty cells

I have a number of log sheets then a single sheet used to pool data from each individual log. Example Log Sheet: Data Sheet Example: The data sheet counts the number of times the correct color ...
2
votes
1answer
37 views

Cannot plot null values in Google Forms

I have a Google Form with questions that I collected the responses from. Now I am trying to create the charts. I click on Responses -> View responses, then I click on Explore to see the chart. The ...
4
votes
1answer
65 views

Countifs returns incorrect result when applied to the output of Query

In my spreadsheet, the chart for Negative Feedback weekly pulls data from the Query sheet, but logic doesn't work which separates negative feedback from positive feedback. The logic works fine on the ...
4
votes
1answer
40 views

Automatically create hyperlinks with parameters, based on data in another column

(This question was posted here by Tasos, but then deleted). I quote a part of it: At the moment, I have a cell which used to create a hyperlink on the next column. This is what I have done at the ...
2
votes
1answer
44 views

How can I round a Google Form input to nearest .5?

I'm trying to write a script that will calculate based on user input from a Google Form and then send a customized email based on the input provided. I've written an apps script bound to the ...
6
votes
1answer
124 views

How to refer to 'this' cell in a conditional formatting formula?

For a field I have conditional formatting with custom formula: =today()>C8+14 and =today()>C8+30 with different styling, basically I want to have a visual styling to highlight older and oldest ...
3
votes
1answer
43 views

EOMONTH-Function returns always “Parse Error”

Following Google Docs "Editors Help" I just tried out the function EOMONTH. Unfortunately, every example I found there will result in an "Parse Error". Example: =EOMONTH(DATE(1969, 7, 20), 1) The ...
3
votes
2answers
268 views

Ways to learn google apps script language? [closed]

I've been looking for a good way to automate my google docs and sheets on drive, buy haven't found specific answers or even general ones I can modify to work for my specific requirements. I'm doing ...
5
votes
1answer
288 views

In Google Sheets how do I duplicate a sheet along with its permission

In a Google Spreadsheet called Attendance there is a sheet called Template. The user duplicates this sheet, renames the sheet with current date and uses this sheet to mark attendance for students. The ...
3
votes
1answer
61 views

Can't find script insert option in Google Sheets

I want to add a script to Google Sheets. I watched a tutorial where to add a script one has to follow the path Insert>Script. But I found that there is no script option there. How can I get the ...
4
votes
1answer
39 views

FILTER function in non-continuous sections

I have seen similar questions asked but have not found a good working solution. I have a spreadsheet which has two sections which need to filtered into a separate tab. The first column is names, all ...
4
votes
1answer
37 views

Can I query a range of cells that match the criteria of another range of cells in Google Spreadsheets?

I want to query a range of cells, using another range as the criterion. Is this possible? Something like this: QUERY('Current Schedules'!A5:F154, "Select F where A = '"&A13:A15&"' ") Where ...
11
votes
3answers
268 views

Average of last 7 non-empty non-blank cells in Google Sheets

I have numbers in range D7:D and there are times when some cells are empty. I want the average of the last 7 numbers but to skip blanks. So if in the last 7 there's only 3 that are full, I want it to ...
4
votes
1answer
30 views

How do I modify the range of an existing Google Sheets chart?

I have inserted a chart into a google sheet, and need to modify the range of data it examines. I can manually define the range when I insert the chart using the chart editor as per the image below, ...
4
votes
2answers
47 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
51 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
414 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
28 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
150 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
45 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
30 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
65 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
566 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
93 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
43 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
26 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
40 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
9 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
63 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
61 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
30 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
38 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
74 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
41 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
47 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
133 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
23 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
343 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
90 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
279 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?