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

learn more… | top users | synonyms (2)

1
vote
2answers
38 views

How can I highlight duplicate cells?

I'd like a formula that highlights all duplicate cells, even if they are in different columns. In my example spreadsheet A1 is equal to D15, the formula should highlight both, and so on with all ...
1
vote
1answer
25 views

COUNT value in column A + filter column B & C + > & < value column D (date)

I need to calculate how many rows in a range(>= <= of VALUE in column D( date value where every row has own date): VALUE in column A(if duplicate count as 1), same VALUE in column B, same VALUE ...
3
votes
1answer
38 views

Count Occurence of a Regex Search Term

Using CTRL+F indicates the number of occurrences of a search term. However, when using regex (via Find and Replace) there's no indication of count. Does anyone know how to find the number of ...
2
votes
1answer
65 views

Running Total In Google Sheets with Array

QUESTION: How do I create a running balance that has the following criteria: It uses an array formula the rows don't need to be sorted by date, but running total only returns balance of: Rows ...
2
votes
2answers
36 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 ...
1
vote
2answers
45 views

Google sheets: How do I do a group count on 2 fields, to summarise colour and size choices

I have a spreadsheet where each row is a persons choice of colour and size of a T-Shirt. It looks something like this: ┌─────────┬────────┐ │ Large │ Red │ │ Small │ Blue │ │ Medium │ Red ...
4
votes
1answer
39 views

How do get data from a previous row when a different column matches?

I'm using Google sheets and trying to find a prior service date for each location. I'd like to have a result look like the below. I already have data filled in for column A and B. What formula ...
2
votes
1answer
22 views

How to get sums by group, and output only those, where sum is not equal to 0?

In Google Sheets, I have the following data: | A | B 1 | foo | 20 2 | bar | 50 3 | baz | -40 5 | bar | -50 4 | foo | 100 6 | baz | 60 Currenly, I'm using this query ...
2
votes
1answer
64 views

Automatically copy and paste cells that have just been updated to a list from forms

I have a spreadsheet that collects information from two different forms. Each form feeds a different sheet in the spreadsheet. There are 3 other sheets that the info from either of the first two forms ...
2
votes
0answers
23 views

Import site URLs to Google Sheet (and add new row each time)

I am trying to make a list of site pages that will automatically update in Google Sheets, while keeping the full history rather than overwriting older rows with new ones. I see two possible ways of ...
2
votes
1answer
19 views

How to concatenate 1 in front of many cells at once on a google sheets doc?

I am trying to concatenate many cells of a google sheets with "1 " in front of every cell. There are 360 cells so changing each individually would be a difficult task. Any help would be greatly ...
2
votes
2answers
37 views

How to word count multiple rows in Google Sheets?

I have a spreadsheet similar to: A B C 1 "text a1" "text b1" "text c1" 2 "text a2" "text b2" "text c2" 3 "text a3" "text b3" "text c3" 4 "text a4" ...
3
votes
1answer
83 views

Add a script trigger to Google Sheet that will work in Android mobile app

I have a Google sheet that includes a script that should be run when clicking a button. The recommended way to do this (as far as I can tell) is to insert an image and then attach a script to the ...
2
votes
1answer
52 views

Creating duplicate linked Google spreadsheets that share copy and formatting

I'm looking to create a content calendar on one Google Drive and then have that exact content calendar (copy, formatting, and any new rows that get added) shared as a tab in a spreadsheet on another ...
2
votes
1answer
55 views

Formula to use in Google sheets to count all cells containing specific texts

Here is a link to a sample spreadsheet: https://docs.google.com/spreadsheets/d/1LcfqPxsnQGEVZKd2imM1WPYRvgbZBiEQ18mHVd3znjE/edit?usp=sharing What I want to do is count all unique values in column C ...
3
votes
1answer
31 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
31 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
25 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
40 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 ...
8
votes
1answer
104 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
19 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
42 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
14 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
26 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
47 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
27 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
33 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
82 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
27 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
217 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
124 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
34 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
29 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
31 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 ...
10
votes
3answers
154 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 ...
2
votes
1answer
17 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
40 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
86 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
57 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
28 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
131 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
41 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 ...