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.
8,722
questions
0
votes
0
answers
4
views
Google app script not doing everything I want
The script i wrote is below. However it is putting the data on sheet tracking and moving down to row 21 instead of 2 and then keeps overwriting 21 instead of moving to 3 then 4. Can someone help me ...
0
votes
0
answers
3
views
How to Parse each cells of column full of JSON?
I have a dataset where 2 out of 14 columns are in Json format.
I know how to parse a JSON cell (one by one) with ImportJson(endpoint, path) but i would like to iterate for each row of these 2 columns
0
votes
0
answers
6
views
When using a formula to set a cell to the value of another cell, how can I keep notes that are on the original cell?
The Question:
I hope the title above makes sense. Basically, I have a formula to generate random items from a table filled with all the possible items of interest. I go through each cell in the table, ...
1
vote
0
answers
22
views
Create a dynamic chart in Google Sheets [closed]
I have a table of two columns named 'Days' (in column A) and 'Calories' (in column B) that shows how many calories I eat in that Days.
Days
Calories
1
355
2
709
3
347
4
578
5
254
..
..
30
793
...
- 111
2
votes
5
answers
331
views
How to get Sheets to recognize dates that are entered without any separators?
I want to enter dates in yyyymmdd format, but have Google Sheets format the values as yyyy-mm-dd.
I don't want to enter any dashes (or slashes, or spaces) because it slows down my data entry, but ...
- 129
-1
votes
1
answer
19
views
Highlight cells between two numbers using conditional formatting
How can I apply conditional formatting in Google Sheets that will highlight cells with values between 50 and 100? The built-in options aren't quite cutting it.
Is there a custom formula that I can use?...
Darrell Campbell
-1
votes
1
answer
31
views
I need to equalize a text phrase with a numeric value
So what I need is to make a neat-looking salary-counting spreadsheet, which doesn't show the numeric value in its cells but instead text values. To be specific, my case is I'm a teacher and I have ...
1
vote
1
answer
31
views
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col1
I have a Google Sheet, that I am using as a tracker for a game. This was not written by me, just passed down, and I am having a hard time trying to decipher the issue here. As per the title of the ...
- 13
0
votes
1
answer
28
views
How do i utilize IF functions (or others) in order to create a simple username/password in google sheets?
On cell A3, there is a username input (i.e max or dave), on cell B3, there is a pin input (i.e 1234 if its max, or 8640 if its dave), how would you go about creating a formula in order to satisfy ...
0
votes
0
answers
53
views
Why am I being blocked from using the Google Analytics Add-on In Google Sheets?
I'm trying to add the Google Analytics Add-on from within a Google Sheet. However, I'm being blocked. Here are the steps I'm taking:
Click "Extensions",
Hover over "Add-Ons",
...
liv.marx
2
votes
1
answer
21
views
Is it possible to have a pivot table automatically incorporate newly added 'Filter by' values or show the current values?
I've made a test sheet here (https://docs.google.com/spreadsheets/d/17l49Rn3ZbNx-upzBtmRtsJBHQiCKXuYPs8SMTyrcw9k/edit#gid=0)
You'll notice that the pivot table IS correctly pulling in ALL the data in ...
- 123
1
vote
1
answer
21
views
Script Failing to Finish - Protected Sheet
I've been using a timed trigger with this script to add new columns daily to a school-wide tracking sheet. Recently, I've been getting notifications that the script is failing on only one of the ...
1
vote
1
answer
10
views
Google sheets slider for a multi-valued cell
I have cells in Google sheets that have multiple values, such as:
Continent
Law
North America
Sarbanes Oxley, Freedom of Information Act
Europe
Digital Markets Act, Digital Services Act
By default,...
- 11
0
votes
2
answers
29
views
Google Sheets QUERY to return if contains multiple selection and all other values
I have a data set similar to the image in cells A5:D31. I am trying to write a QUERY function that will return the records that match the "filters" in range A2:D3.
In this example, the query ...
- 19
0
votes
1
answer
29
views
How to make arrayformula automatically applied to newly inserted rows?
The following arrayformula needs to be applied to the entire column B, so that it populates Twitter account IDs in the cells of Column B according to hyperlinks in Column A. It firstly checks whether ...
- 113
0
votes
2
answers
29
views
Identify first appearance of data in an unsorted list in Google Sheets
I have a funky lookup situation, consider this example data
Kris | Apr-2022 | ? |
Kris | May-2023 | ? |
Kris | Jun-2023 | ? |
Pat | May-2023 | ? |
Pat | Jun-2022 | ? ...
- 123
1
vote
1
answer
17
views
Trigger script to share calender with new user whenever form updates sheet with a new email address
I have a Google form that allows people to submit their name and email address in order to sign up for a private shared calendar.
The form populates a Google sheet.
I want to automatically add new ...
- 13
-1
votes
1
answer
61
views
Choose an employee and show the days where he has a specific shift code , along with the employees with the same shift
For convenience,
Let's assume we have a shift schedule of a week.
Header has the days from Monday to Sunday, and the 1st column contains the names of the employees.
Names
Monday
Tuesday
Wednesday
...
- 21
-1
votes
2
answers
56
views
Formula that calculates the difference between numbers
I'm trying to create a formula that would be able to calculate the difference between the numbers and writes it exactly as shown in this image: https://i.postimg.cc/W4cZDgD2/image.png
Full Example: ...
- 1
2
votes
1
answer
37
views
How to do autoresizecolumns() in a Google Sheets file when data imported from another Google Sheets file is changed
I'm new to Google Sheets and apps scripts. This is my first ever question. I apologize in advance if I don't get it quite right.
Google Sheets file Staff Roster sheet Roster contains names, contact ...
- 125
1
vote
0
answers
20
views
Insert a new row in Sheet 2 for each cell in a range in Sheet 1 that is not empty [closed]
Hi I'm new to the Google Sheets App Script and not sure where to start. I'm trying to write a Script that will insert a new row in Sheet 2 for each cell in a range in Sheet 1 that is not empty.
For ...
- 11
1
vote
1
answer
18
views
Adding a total in a cell when a checkbox is ticked in an adjacent cell
On my budget spreadsheet, in the 'amount' column is the different direct debits going out each month, then I tick the checkbox in the 'gone out?' column when it goes from my account. I'm wanting to ...
- 11
0
votes
1
answer
13
views
How search for multiple values in string and return a value depending on the match?
This is my sample data
A
...
1
This doesn't match
...
2
This one has Foo
...
3
In this, Foo exists too
...
4
Here we have Bar this time
...
I have in a different sheet the values to search
A
1
...
- 101
0
votes
2
answers
67
views
Two custom fnctions triggered by OnEdit both returning errors
I have two custom functions bound to a Google spreadsheet with an onEdit() simple trigger that are returning errors.
The first function sortStatus() sorts the columns in range A2:R by I2:I (col 9) ...
- 1
0
votes
2
answers
47
views
Extraction social hashtags with diacritics from a cell?
I´m with a similar problem, like this post: How can social hashtags be extracted from a cell?
Basicly it asks what to do to extract hashtags from a cell in google sheets. Some good answers, like this ...
1
vote
2
answers
33
views
Count the rows in one range based on both a key and the existence of a value in another range
This is likely a duplicate, but it's difficult to search for or to recognize when I have found it.
In Google Sheets, I want a formula for E1:E4 (pink) that will give me the count of rows in A1:B5 (...
- 152
0
votes
1
answer
19
views
How do I use REGEXTRACT to return grouped numbers from a string where the number of groups varies?
In Google Sheets, I'm trying to return numbers from within a text string.
The numbers are values of the property named either item_id or item_ids.
I've tried the REGEXTRACT function but I'm struggling....
0
votes
1
answer
28
views
SUM cost if Product ID is unique and matches multiple criteria
I have data in a sheet similar to the image. There are rows with duplicate Product ID information and I want to calculate the total cost of a unique Product that matches multiple criteria.
In this ...
- 19
1
vote
1
answer
8
views
im trying to get a group of cells and there values repeat along a row. i have values entered from b5:bl5 and i need it to just keep repeating
Google sheets- entered values in B5:BL5 and i need it to repeat across row 5 all the way to og5. I need it to do it automatically. I have tried a scrip that doesn't work cause i don't know what I'm ...
- 11
1
vote
1
answer
30
views
Subtracting lunch break from hours worked
I'm trying to calculate the total hours worked, not including lunch breaks. I'm using standard numbers for hours worked such as 8, 10, 12 etc. However, it needs to reflect a 30 minute break for any ...
- 13
0
votes
2
answers
25
views
Google Sheet: Extract name from emails using a list of names to compare
My list of subscribers, the majority of them have not enter a name but in their emails 1 from 10 has name in it.
For example [email protected] "Rose" is the deducted name.
So I will like to ...
user1968104
0
votes
2
answers
31
views
COUNTIFS not working :(
Anyone able to tell me why this generates 0 and not 5 please?
My COUNTIF works fine below, but selecting multiple criteria doesn't seem to...
Probably some simple syntax error..?
0
votes
2
answers
22
views
Google Sheets - COUNTIFS with some blank values and multiple criteria
This seems like it should be easy but I don't know what I am missing.
Example Data:
Names Numbers
Dave 12.00
John 14.30
Tom 11.40
John
John 21.11
John 23.00
What I am after: ...
- 1
0
votes
1
answer
27
views
How Can I Force INDIRECT to Update When I Add a New Sheet?
I am using INDIRECT to reference sheets that I will create in the future (Week 1, Week 2, Week 3...) I use IFERROR to hide the errors in the cells for future weeks where the sheet does not exist yet. ...
- 144
0
votes
0
answers
11
views
Bio assignment graph
I'm working on an assignment for a bio class that I'm taking, and this assignment requires me to create a graph. I have the graph and the information entered, but have no idea how to manipulate the ...
- 1
2
votes
1
answer
38
views
chart select data: there are more items than can fit in the menu
I have time series data with at least 250 data points. I graph the data with a Sheets Line Chart. I'd like to add some data points with labels to the graph via the Add Select Data Point option.
...
- 21
0
votes
1
answer
21
views
Excluding specific range in Google Sheets
I have a range formula on a Google Sheet that pulls the next future date out from a column containing nothing but dates (column C), starting from the 4th row:
=MIN(FILTER(ARRAYFORMULA(C4:C),C4:C>...
-1
votes
1
answer
28
views
All Time Rolling Average based on string order with multiple conditions in the same column - Google Sheets
I'm trying to calculate the average $ amounts per category (X & Y) and their sub-categories (A,B,C,D,E) on a rolling basis based on past data that's on a string format. There are 2 types of ...
- 9
2
votes
1
answer
167
views
How to force uniqueness of cell in Google Spreadsheet with non-Latin text in cell?
I have Kannada text in my column and it's not working, but it worked fine with Latin text before. Kannada text is from a region near India. Here is my spreadsheet, there are too many rows to include ...
- 257
0
votes
1
answer
40
views
Rolling Average based on string order with multiple conditions in the same column in Google Sheets
I'm trying to calculate the average $ amounts per category (X & Y) and their sub-categories (A,B,C,D,E) on a rolling basis based on past data that's on a string format. There are 2 types of ...
- 9
0
votes
1
answer
31
views
Does Google Sheets have a function to insert X number of Y characters within a cell? [duplicate]
I currently have a spreadsheet which requires a variable number of line breaks within a cell based on input data from another cell.
Long story short, I currently am using a brute force method to ...
- 3
0
votes
1
answer
21
views
Stuck at understanding how duplicate prevention formula works in Google Sheets
Many blog posts and educational sites tell us to use =countif($A$2:A2,A2)=1 in the selected range.
However, they don't explain it. And for me it doesn't work and since I can't understand it I can't ...
- 1,889
0
votes
1
answer
16
views
Automatic way to select a range for a formula based on the date
I am creating a calculator where the user describes a list of tasks in Activities!B2:B, and in C2:C they rate 0-5. To separate each day, I've used a date for each task in A2:A (stylistically separated ...
- 15
1
vote
0
answers
23
views
ArrayFormula Trouble pulling data for just a year or just last 15 days No understanding how to do [closed]
Google sheet to see my problem
https://docs.google.com/spreadsheets/d/1e3sjPNDJ3crJVQIn9L-lKllXP0wvc9NC22cnjxCveEs/edit?usp=sharing
See separate tab (help)
See all Yellow Boxes........they have the ...
1
vote
1
answer
61
views
How to combine ARRAYFORMULA + QUERY?
"Please advise on how to correctly input the formulas to get the desired result as shown in the picture.
Task:
https://docs.google.com/spreadsheets/d/14tpPliM_3b7RIikSimvdzLjkFq3CexPKbtOEuD4aIKU/...
0
votes
1
answer
26
views
Column Identifiers as Described in Query Language Reference (Version 0.7) not working
In the Query Language Reference document found at https://developers.google.com/chart/interactive/docs/querylanguage#Table_used_in_Examples it indicates the following as properties of data tables.
&...
0
votes
1
answer
21
views
Using created filter to filter specific value base on other cells value
I can't seem to figure this out. I am wonder if it is possible to create a filter for a column, and then filter that column based on another cells value. So I have created a filter for A column which ...
- 101
1
vote
1
answer
42
views
Format a numbered list in google docs into a table template?
I have a list with a url, an emoji (of a set of 3), and a name in a numbered list.
Taking a list like this:
https://news.ycombinator.com/item?id=38122061 🥾 D. onohoe
https://news.ycombinator.com/...
- 11
0
votes
1
answer
34
views
Timestamp on change of another range
I have a Planning sheet that I use to follow budgets that are reported every week.
I'd like to have K7:18 stamped with a date when there is a change in the corresponding cell of I7:I18 range.
I cannot ...
0
votes
1
answer
43
views
How to find largest group of consecutive numbers
This is a complex ask, so I apologize if it's unclear and will be happy to clarify. I have a series of numbers (dice rolls, in this case), and I need a formula to do the following:
Find groups of ...
- 3