Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. Don't use it for the Google Drive Excel files editor. Please consider adding a demo spreadsheet to your question, this will greatly increase the chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
0
votes
3answers
205 views

Google Sheets COUNTIFS Range of cells as criteria possible?

In the Google Sheets Formula =Countifs, is it possible to have a range as a criterion, for example: =COUNTIFS(ExampleSheet!A1:A10, "Some criteria", ExampleSheet!B1:B10, List_of_Names!C1:C) I couldn'...
0
votes
0answers
5 views

is the string 'TRUE' can cause a problem in a query?

I am getting information from a external workbook whit the following query. =QUERY(IMPORTRANGE("1eOxqZX9078WcGlR-vxVs4njjHrM9U7cH0TDxGTp60Tw","core!A:E"),"Select Col1, Col2 where Col5 ='TRUE'",0) I ...
0
votes
1answer
15 views

Conditional formatting skipping colors after the same large value

Looks like the conditional formatting skipping large formula because of the same large values. Help me please how can I get the order what is on the picture in the column "What I would like".
0
votes
0answers
12 views

Visualize Data that updates dynamically

TLDR: How can I create a bar chart that updates the x-axis daily? The end goal is to have a chart that shows a side by side comparison of goal completions performance for each source of traffic. Here ...
0
votes
1answer
8 views

Conditional formatting for passed dates

I need the formula to highlight a cell if the date in that cell has passed. Cell U3 had 6/29/19 today is 7/1/19 - I want to have the cell turn red after 6/29 to alert me to follow up.
0
votes
1answer
17 views

How to set a cell is a formula equal to another cell?

I have a sheet of formulas, and I want to duplicate it several times for different data set. If I use ='Sheet1'!A1 in A1 of Sheet2, it will copy the value but the formula. Is there a way to copy ...
0
votes
0answers
6 views

Remove Count column by repeating rows in Google Sheets

I have a worksheet, 'wi', in a Google Sheet such that: Count | Entry | Description -------|-------|----------------- 2 | Foo | yada yada 3 | Bar | blah blah blah On ...
0
votes
1answer
120 views

ImportRange not pulling in Dates from first sheet in correct format

I'm trying to pull in a date cell from one sheet to another with an importrange function: =iferror(query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UClhpev01z-...
2
votes
1answer
603 views

Combining data from two columns to one (in separate rows - not comma separated) and removing blank lines

I have a Google Sheet which receives information from a google form. I would like to collate some of this data in a separate sheet (within the same document). I would like the new sheet to combine ...
0
votes
2answers
29 views

View only few non-continuous columns

=FILTER(Matrice_data;Matrice_nom=G2) Pretty straight forward. I type a name in G2 and I get all rows with this name. My problem is this one: the "matrice_data" contains many columns, like 30. I want ...
3
votes
2answers
4k views

Summing top three values in a column from a Google sheet

I have a Google Sheet with a variable number of data rows. I would like to sum the three highest values from a certain column, but they won't necessarily be in the top three cells. An empty cell ...
4
votes
1answer
2k views

Display the contents of a named range elsewhere in the sheet

I have a sheet like so: A B 1 W X 2 Y Z I have the range A1:A2 named as "Column1". I want to display that range elsewhere. Now if I wanted to display it as a row, I know =TRANSPOSE(Column1) works. ...
0
votes
1answer
11 views

Consolidate columns from a dynamic range into a single column in Google Sheets

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case I want non-unique cells. Also, each cell in the range is ...
9
votes
3answers
12k views

Can you do a UNION SELECT in a Google Sheet?

I've got a Google Sheet linked to a form which collects replies from three different people on the same subject using the same form and editing three times. This creates a sheet which contains three ...
0
votes
0answers
6 views

Mark all line if checkbox is true

I will give an example: I have checkboxes in B3:G3. This is a checklist, I want to change the background color to green for A3:H3 if B3:G3 boxes are checked. Can someone please tell me how to do ...
0
votes
0answers
8 views

Showing top 5 most frequent values and counts of each from a data range

I have a google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending ...
0
votes
1answer
17 views

Create case sensitive vlookup with array based search key

I have the following case sensitive vlookup formula however search_key must be a single value here, and I need to search an array of keys. =ArrayFormula(INDEX(return_array, MATCH (TRUE, EXACT(...
2
votes
2answers
14 views

Multiple IF-statements in a single cell - checking long list of results

I have a Google Sheets where I enter all the results of my beer pong matches with friends. I'd like to see my amount of wins/losses with certain friends. This can be done with =IF(AND(B2="Won",E2="...
0
votes
1answer
382 views

Google sheet - get crypto currency price from Cryptobridge Exchange API

How can I retrieve only the price from this API in a Google spreadsheet? This is the API ticker - https://api.crypto-bridge.org/api/v1/ticker For example, if I use this function =INDEX(IMPORTDATA(...
0
votes
1answer
12 views

How to filter after parsing csv values contained in Google Sheets cells

I would like to be able to match all cells in column A (titles) that have one of the comma separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
0
votes
1answer
935 views

Auto insert Formula on the next empty row upon form submit

I have a spreadsheet that contains 3 columns with leave request data coming from a google form. [From Date] [To Date] [Name of the Requestor] 5/16/2018 5/18/2018 John 5/18/2018 5/20/...
1
vote
2answers
772 views

Google sheets create a list of unique values that don't exist in another column

I want to check create a list in C of all the unique values in A that don't exist in B. I'm using unique to get the unique values in A, but can't work out how to remove the values that exist in B. I'...
0
votes
1answer
16 views

Copy data validation “list of items” from one sheet to another?

On one sheet I have a column that uses "list of items" data validation which allows for a drop-down menu to extend and a multiple of options to be selected. Is there a way to automatically populate ...
10
votes
2answers
2k views

How to do a simple set operation in Google docs spreadsheet?

Suppose I have some names in cells B1:B3, and a full list of all the names in A1:A4, is it possible for me to generate a list of names in A1:A4 that don't appear in B1:B3? E.g. Cells A1:A4 have "A", "...
0
votes
0answers
4 views

Google Sheet Pivot “% of Parent Row Total”

Can Google Sheets pivot table calculate "% of Parent Row Total"?
0
votes
1answer
9 views

What do <> (angle brackets) mean in Google Sheet formulas?

What does <> mean in a spreadsheet formula, such as: =A1<>B1
0
votes
2answers
7 views

How do I sort data by size and not in ABC order

I am new to Google Sheets and have only used Excel a little, so Google Sheets is a bit different. I want to sort our band uniforms in size order from smallest to largest but Google Sheets only sorts ...
-1
votes
1answer
1k views
0
votes
0answers
8 views

Disable printing / downloading spreadsheet for editors - Google Drive Sheets? [duplicate]

I know it is possible to disable printing and downloading a Google Drive Spreadsheet for viewers, But is it possible to disable the same for editors other than the owner ?
1
vote
0answers
6 views

Create a Google Sheets graph with multiple lines in the same chart

I have data that looks like this: Name X Y Bob 0 15 Bob 3 20 Bill 0 24 Bill 5 30 And so on. Basically I want to have a line for Bob, a line for Bill, etc. With X as the X ...
1
vote
1answer
13 views

Allow others to make google spreadsheet copies that retain the view/edit permissions of its parent spreadsheet

I have a google spreadsheet with the permission "anyone with the link can view" so that I'm the only one able to edit but people can look at it freely. I have also been giving people the https://docs....
0
votes
1answer
15 views

Query with Date Selector. Both must be inputted or no information is available?

Currently using: =IFERROR({QUERY(PRI!A4:T,D1,1);QUERY(NON!A5:T,D1,0)},"Nada") This is used to grab information from two spreadsheets with the same number of columns, with PRI grabbing data with ...
0
votes
1answer
32 views

Cut and paste data from a row of cells from one tab to another tab in Google Sheets and repeat with new data

The below code works for Q3 but not for Q5. I'm relatively new to sheets and writing code and I'm sure that you can't have two functions with the same name but am unsure how to resolve so that I can ...
0
votes
1answer
26 views

Suming values in a column if value in cell next to it matches another cell

I'm trying to create a sheet to maintain a small inventory of servers. I created a segment where each type of component will be listed at the top. Below it is a series of copy-pasted chunks which ...
2
votes
1answer
701 views

Catch New Rows in Existing Filter Views

I have a series of filter views that filter my Google sheet based on the values in column B. I have 200 rows in my sheet, so the range for each filter view is A1:C200. Often, new rows get added to ...
0
votes
1answer
42 views

How to reference a dynamic range in Google Sheets Script?

I have a sheet where the filter originally started on row 2, so my range was A2:J. As I've added and deleted rows/columns, my range has changed and it's still changing as I edit the sheet. Is there ...
1
vote
2answers
2k views

Highlight second highest number in Google Sheets row

I am attempting to use conditional formatting to highlight the second highest number in a row in Google Spreadsheets. I've set it to "format cells if custom formula is", applied to range "CO27:DB27" ...
0
votes
1answer
15 views

How can I set this up to run two onedit functions in the same sheet?

all If you put the two together they don't work What is the right code to work together Note that the first to arrange automatic The second is to set the date if you put only one works, but if you ...
2
votes
1answer
9 views

How can I quickly change the color of text in a cell in Google Sheets?

How can I quickly, with either a single or double click, assign a color to the text in a cell in Google Sheets? In order to quickly determine status of certain pieces of data in a spreadsheet I ...
0
votes
1answer
806 views

Pasting timestamp for edits by multiple users in Google Sheets

I got a good Google Sheet script for pasting a time stamp at the designated column of the same row on editing any cells of another particular column. Here it is On Edit of column 11: timestamp is to ...
0
votes
1answer
337 views

Arrayformula and Sum queries. Possible alternatives

I am using a sheet to list and summarise teachers absences and substitutions. Here's the worksheet Basically in the sheet "Substitutions" all absences and substituions are stored record by record: ...
0
votes
2answers
528 views

The script does not have permission to get the active user's identity

Anyone know why this caused The script does not have permission to get the active user's identity.? I don't have any functions that asks for the user identity. So I'm a little confused. function ...
1
vote
1answer
20 views

AND / OR Conditional Formatting for Google Sheets with multiple criteria

I am trying to write a custom formula to conditional format column E by referring to the different tenure level targets in column D to indicate if they are hitting their targets base off of their ...
-1
votes
0answers
27 views

IF and IFS not working properly

I am trying to locate data present in different sheets. I have tried using IF and IFS. Below are the formulas I have used. For IF, I get NOT IN USE even when I know there should be one of the sheets ...
2
votes
1answer
296 views

Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet with a keyword instead of exact match

I would like to use the importrange formula to import data from "spreadsheetA" that contains "keyword" in columnD. I can get importrange to work but I cannot figure out how to filter it so it will ...
0
votes
0answers
11 views

Trying to use Google Sheets importHTML() to import a table. But no content appears

I'm attempting to load the following website into Google Sheets: https://www.lasvegasadvisor.com/vegas-lines/?sport=ncaafb&period=0 Can anyone help provide a formula that would work in google ...
-1
votes
3answers
256 views

How to run multiple OnEdit scripts in the same sheet?

I am using an auto-timestamp script in Google Sheets. Where when you input something in a particular cell, the script will automatically add a timestamp in a particular cell. But the problem is that I ...
-1
votes
1answer
64 views

Arrayformula for running MAX of separate column

I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
0
votes
1answer
40 views

How to list total files for every subfolder in Google Drive?

On google drive I have a directory with over 1000 subfolders. I want to list all subfolders with the total amount of files for each subfolder. This is the script I got so far but it doesn't work cause ...
0
votes
1answer
106 views

Fetch Random String From Array For Each New Form Entry

!Sheet1 is populated with entries coming from a Google form !Sheet2 has an array of 12 employee names in array A2:A13 How can I automatically populate the cell on the right column of every new line ...