Questions tagged [google-sheets]
For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.
1,822
questions with no upvoted or accepted answers
1
vote
1
answer
257
views
function no longer working on mobile
I have a function that uses the onEdit trigger. It was working great both on PC and mobile yesterday, but today it no longer works on mobile but still functions on my PC. Not sure what is going on.
...
- 11
1
vote
1
answer
123
views
AppendRow Column Offset
I wrote a Google Sheets Apps Script to function as a data entry form for my team to use. The script works great, but I'm looking for a way to improve it. I'd like to be able to skip the first column ...
- 13
1
vote
1
answer
157
views
How do you work with the Color Class array?
In Google Sheets using Apps Script, I want to read in the font color in many cells, change some values (change colors) in the array, and write them back to the cells.
I have previously used ...
- 11
1
vote
1
answer
142
views
Split Text to columns in Google Sheets with multiple full words as delimiters - "to" or "for"
I need to split a text when the delimiter is "on" or "to" or even "for", removing the delimiter from the results:
Ex:
Creating more value for business =
Creating more ...
- 11
1
vote
1
answer
30
views
VLOOKUP of Text is not working
Here Is how my google spreadsheet looks like
I'm trying to find the the Artifact ID (Column K) using the artifact name that is in G
So on A2 I'm trying to use this formula
=VLOOKUP(G2;$K$2:$L$39;1;...
1
vote
1
answer
207
views
How do I get Google Sheets to choose the winner of a sports game, based on each team's score?
I am doing a report on whether Expected Goals (xG) is an accurate predictor of the winner of a soccer game. I have every game from the 2021-2022 English Premier League Season organized into columns. I ...
Carter Sasser
1
vote
2
answers
371
views
Why Are Checkboxes Breaking My FILTER?
I am filtering data from another sheet. The filtered range is chosen based on a dropdown. The filtered data is mostly true/false values. I want to display these data points as checkboxes. When I ...
- 144
1
vote
1
answer
81
views
Custom conditional in Google Sheets: How to integrate in Apps Script?
My goal is to have Google Sheets detect when a cell's contents has a strikethrough added, and to change the font color of that cell in response. I learned that this is not possible without an Apps ...
1
vote
1
answer
90
views
Convert 5min interval data into hourly data
I am looking to convert a year's worth of 5-minute energy data into hourly data.
The data is set up with each day taking up a row and each 5-minute interval taking up a column. Consequently, my data ...
- 19
1
vote
1
answer
18
views
Import values from others spreadsheets to a single one
I'm having some trouble to import data values from many spreadsheets to a single one. I need to import all the values from all columns "A" of each spreadsheets to the column "A" of ...
1
vote
1
answer
1k
views
Showing rounded integer values in a Pie Chart in Google Sheets without decimals (Pie Chart - Slicing Label does not work)
The proposed solution for the general question has been already asked here: How to change the values of a pie chart to absolute values instead of percentages in Google Sheets
But it doesn't work for ...
- 361
1
vote
1
answer
134
views
Google Sheets Query and Aggregate Date Scattered Across Multiple Columns
I am working on this Google Sheet: https://docs.google.com/spreadsheets/d/1Ai8P8T8GmLMzTxgcxQFhHTqbUK6hG4TrJBtvP9PWC9M/edit#gid=0
I have the following formula in G1 (red shading):
=QUERY(A:E,"...
- 11
1
vote
1
answer
45
views
How do I create a graph that shows progression over time?
Firstly, to help explain what I'm looking for. We're a charity delivering services each week to children and young people. At the end of our service, a volunteer will fill out a report form on each ...
1
vote
1
answer
50
views
How to filter data from various sheets based on dropdown selection
I am setting up a sheet to track renewable services for my company. There will be various tabs, for example, Domains, Hosting, Google Workspace and Phone Numbers.
I would like to be able to filter all ...
- 11
1
vote
0
answers
142
views
How to have multiple filters work at the same time in one formula?
I'm trying to make a filtered list. The information in the list depends on if multiple values are check or not.
In image one you have the ranks which I want to have one filter. Then the training type ...
- 11
1
vote
1
answer
290
views
How to create Google sheets range off of 2 separate columns
I need to create a range off of two separate columns within a sheet.
Let's say column A is my index, while column D is third party index.
When using VLOOKUP i can lookup my index to find 3rd party ...
- 11
1
vote
1
answer
58
views
Why do conditional formatting rules based on date affect the wrong cells?
Here is a Google Sheet for our church rota - publicly viewable: https://docs.google.com/spreadsheets/d/1KODl9N_I3VeO0CKBrU_75GoQ5dBvUuSzx0FinNKf9zs/edit?usp=sharing
I have conditional formatting rules ...
- 528
1
vote
1
answer
86
views
Google Sheets - Index Match multiple criteria and dates in headers
I am trying an index formula so that I can retrieve the value of dataset based on the item and the date.
Data source formatted as below, with price valid until
Start
01/Sept/2022
01/Oct /2022.
29/Oct ...
- 11
1
vote
1
answer
171
views
Dynamic & extendible category names
I'm having trouble on something with Google Sheets:
I need category names to be dynamic & extendible between 15-200 values, so it's not quite worth an Apps Script, would be super cool to put the ...
1
vote
1
answer
290
views
How to "layer" image in google sheets using checkboxes
I have a table of checkboxes (rows 1-3 and columns a-d) and I'm trying to make a layered image from each column - meaning that if for example, I check A1, B2, C3, and D1, four images will appear on ...
- 13
1
vote
1
answer
104
views
Using Google Sheets to bulk collect the number of Google Search results count for given keywords
I need to bulk check the number of Google Search results count for given keywords by using Google Sheets or Microsoft excel
For example,
ocean is About 2,960,000,000 results
1
vote
1
answer
80
views
RC notation in IMPORTRANGE
Is there a way to use RC notation in the IMPORTRANGE function? What I need to do is to import a certain column, which I'm finding using MATCH in the first row (headers)
=importrange("linktosheet&...
- 11
1
vote
1
answer
93
views
Applying conditional formatting formula to 1000 rows without changing row number
This might sound strange but I am trying to apply the custom formula in the conditional formatting rule to 1000 rows:
=AND($A12="P0", (DATEDIF($D12, TODAY(), "d") >= $B$4))
...
- 11
1
vote
2
answers
86
views
Google Sheets: return multiple values of substring search
Lets say I had a sheet like this
COLA
COLB
COLC
ROW1
blah
blahbingo
blah
ROW2
blahmagic
blah
blah
ROW3
bingoblah
blah
magicblah
And then I had a sheet like:
R
C
Substrings
magic
bingo
What ...
user1754150
1
vote
0
answers
118
views
How can I force Google mail to open .xlsx file as a Google sheet?
I have a spreadsheet sent from Gmail, in the past, the spreadsheet would redirect to Google sheet in a new tab.
However this morning, the spreadsheet isn't being redirected to the next tab, instead, ...
zettomon
1
vote
1
answer
89
views
Google Sheets: Search for matching words in a list based on the last word in a cell
UPDATE
Now it only need to scan the whole row instead of only the cell next to it.
I am quite new to google sheets and I want to achieve the following:
Lets say we have list 1:
Red apple
yellow ...
user305241
1
vote
2
answers
15
views
Looking to join 3 cells together but only if the 3 cells contain a value
I am looking to TEXTJOIN 3 cells together but only if all three cells being joined have a value in them, the goal would be to just have values show up in column AL but I don't want a value that just ...
- 31
1
vote
1
answer
33
views
How to use ARRAYFORMULA to remove values that contain brackets?
I am trying to write a formula that pulls all data from column A that doesn't contain brackets.
Source Data
Desired Result
(no values containing brackets)
- 31
1
vote
1
answer
341
views
Google Sheets Select Row that contains text and transpose it in a single step
I'm trying to select a row of data from my table based on text in columns A and B and then transpose it into a column. In my example, I would like to just have Minnie Mouse's information in a single ...
- 11
1
vote
1
answer
4k
views
Split / subdivide Google Sheets cell into multiple columns and / or rows
In Google Docs, you can insert a table, right-click a cell, select "Split cell", specify into how many columns (and / or rows) you want to subdivide the cell, and click "Split" to ...
- 11
1
vote
1
answer
23
views
In google sheets is it possible create/embed a dropdown fillable table?
What I am trying to do is have a cell be the sum of a set of values that are from a table, and for the values of this table to be easily accessible/changeable from the near the cell. The issue is that ...
- 11
1
vote
1
answer
17
views
I need a formula to search a term in a specific row and add up data in column below said row
I am trying to create a daily sales report for my business. I can download all the data in CSV and copy it into sheets. I wish to search for a term in a particular row e.g "Gloves" and then ...
1
vote
3
answers
44
views
How do I inhibit conversion of `(3)` to `-3` within a Google Sheets formula?
=index(split("A (2)"," "),2) renders as -2 even when cell number format is set to "Plain text". It also breaks further functions like REGEXREPLACE that expects text, not ...
- 627
1
vote
1
answer
121
views
Google Sheets - Button link to script with a range input
Is there a way to write a script, assign it to a button, and press the button to execute the script AFTER selection a cell range that serves as an input parameter to the script in question?
- 11
1
vote
1
answer
683
views
Stack two sets of data into one in google sheets - different columns
I am looking for a solution that helps me stack two sets of data (from diff. columns, different order) into one data sets
EXAMPLE:
columns names + order tables1:
first name, last name, city, age, ...
- 11
1
vote
1
answer
168
views
Creating a rolling date formula to automate spreadsheet
I'm not very experienced with google sheets formulas, and I'm attempting to create a rolling year formula for a scholarship spreadsheet. All I really need it to do (if it's even possible) is to ...
- 11
1
vote
2
answers
209
views
Query formula to add a column in Month/Year format from existing date values
I have a data set where 1 of the columns contains dates in "YYYY-MM-DD" format. I'd like to use a single query formula to select some columns from that data set, including the date column. ...
- 77
1
vote
1
answer
91
views
How to auto-update cell border color based on how long ago the cell was last updated in Google Sheets?
It doesn't seem that conditional formatting is able to achieve what I'm trying to do. I'm looking for a way to make a range of cells to each auto-update its own border color based on the duration of ...
- 11
1
vote
2
answers
100
views
Alternate colors with masked rows
In Google Sheets, it seems that masking an odd number of rows in a range somehow defeats the alternate color formatting (adjacent rows will occur with identical colors). Is there a workaround using, e....
- 41
1
vote
2
answers
1k
views
Google sheets chart to compare two values every year
I'm trying to build a chart within google sheets to compare my portfolio contributions vs yearly allowed maximum limits. My data looks like the below. I'm not sure which chart to make use of to ...
- 111
1
vote
1
answer
285
views
google sheets query function where A matches string in a cell
I am trying to use a cell value to impact a query. The issue seems to be that the cell value is a string which I am unable to use as a dynamic object in the query.
I have based my findings and ...
1
vote
0
answers
70
views
Importrange access permission in Google Sheets
I'm trying to use IMPORTRANGE() but I run into trouble with permissions even though
I'm the owner of the data source
I'm editor of the data destination
I get the "You don't have permission to ...
- 145
1
vote
1
answer
46
views
Is there a formula to count occurrences of a string in another sheet when the string needs to be "looked up" in a different sheet? (Quiz example)
Sorry for poor title wording.
I am keeping track of quiz questions in a Google Spreadsheet. In order to generate question statistics I also keep track of some metadata about each theme in a different ...
- 111
1
vote
1
answer
212
views
countuniqueifs one condition and not-another condition
In my sheet, Column G will either indicate "LIKE Submission" or "Incident Referral." Columns D-E-F list different names (one per row). I want to tally the number of unique names ...
- 113
1
vote
0
answers
40
views
How to more effectively find duplicates in Google Spreadsheet?
I have this Data Validation to find duplicates in my Google spreadsheet column:
=COUNTIF($D$2:$D,"="&D1) < 2
It shows duplicates like this, which are very hard to find when you have ...
- 257
1
vote
1
answer
28
views
Google Sheets IF THEN question
I have two columns of data, one column will have either "cc" or "cash" in each cell, the second column will have $amounts. I am trying to have it automatically know whether each ...
- 11
1
vote
1
answer
34
views
Communicating across different Sheet files
Excuse my poor terminology, I'm a casual Sheets user.
I have a business with 5 different, somewhat complicated revenue streams. So I made a budget sheet for each of them. I end up with a GROSS PROFIT ...
- 11
1
vote
1
answer
21
views
How can I use the filter option to only view names of agents that are in particular groups?
On the top rows are some groups that each agent is assigned. What I want is to use some kind of tool or formula that can display columns of those agents that have the desired groups. For example, if I ...
- 11
1
vote
1
answer
655
views
Google Sheets, group by unique values in Column A, then show all values in Column B that are in the group
I have rows of raw data, and I would like the group by column A, and then for each group, list out the values of column B that are in that group. Duplicates in column B (Likes to go to) are fine, and ...
- 111
1
vote
0
answers
157
views
How to combine multiple sheets and sort the results in Google Sheets?
I have a "main" sheet like this (which is single words, not compound words):
And I have a compound word sheet like this:
Notice the slightly different columns. How do I combine both lists, ...
- 257