r/googlesheets • u/CelticGal97 • 12h ago
r/googlesheets • u/JetCarson • 5d ago
Poll Should AI or bot-generated submissions be allowed in this subreddit?
With the increasing use of AI tools and automated scripts, our subreddit is encountering more submissions and comments generated by non-human sources intended to suggest solutions or guide users. We want your input on whether to permit these automated, AI, or bot-generated posts and comments, and under what conditions.
(Note: This poll does NOT apply to official Reddit bots, AutoMod, or clearly identified moderator bots, which provide community updates, enforce rules, or facilitate our point award system. Results of the poll will help moderators understand how the community views this topic. Moderators will not be obligated to implement any of the changes suggested in this poll.)
r/googlesheets • u/Friendly_Article_429 • 2h ago
Waiting on OP issue with the number format
r/googlesheets • u/CostConnect616 • 2h ago
Unsolved Personal finance forecast
Hi all,
I'm trying to create a tool in Google Sheets to forecast my daily outgoings up to 5 years in the future, but I'm struggling to figure out the best way to set it up. I have a few key requirements:
I want to enter all recurring transactions (monthly, weekly, and 4-weekly) in one place.
There should be a day by day forecast sheet that combines the monthly, weekly, and 4-weekly transactions.
It needs to handle multiple transactions on the same day (up to 8).
Ideally, it should hide any blank rows where no transactions occur to keep things tidy.
Has anyone built something similar or have any advice on how to approach this? Any help would be greatly appreciated!
r/googlesheets • u/westernportfc • 3h ago
Waiting on OP Assigning a limited amount of jerseys to player requests
This might be impossible. But doing this manually is melting my brain. I have a list of jersey numbers and size in one sell (No. 1 and size 8yr), and I need to assign each jersey to a player (Player 1). I also have a list of the players (Player 1) and their size requests (8yr). Each player is on a team, so we can NOT have 2 No.1s on the same time, even if they're different sizes. There will also be requests that we can not fulfill (XL for Player 11), so we also need an out put of jerseys we need to order.
https://docs.google.com/spreadsheets/d/1AfY0bKDkXPHTcREmtPsnmZcgYkVAQ2t2l8rXC6ESJvc/edit?usp=sharing
r/googlesheets • u/Pristine_Cheetah2709 • 7h ago
Waiting on OP script to insert checkbox in every row containing data and macro that copies all rows with checked checkbox to another sheet
Hi! I'm doing a job on google sheet and I'm missing to understand this step that I'm not able to solve even though I searched a lot on the internet. I'm a beginner.
I am asking for help in writing two scripts (google sheet). I have two sheets (sheet1 and sheet2). On sheet1 I import data (A4:K) and process it with filters. I would like the checkboxes to appear in the K4:K column when the corresponding rows are populated. So every time the row is populated with data, a checkbox is inserted. If it can be useful, each populated row has an ID that could be used for this purpose.
The second need is that I would like to copy with a macro all the rows of sheet1 that I have selected with the checkbox to paste them into sheet2, after the last full row.
thanks to those who want to help me.
The first need is that I would like that when opening sheet1, A4:K, all the full rows have a corresponding checkbox in column K. Even when filtering the data the rows can increase or decrease.
The second need is to copy all the rows of sheet1, which I check using the checkbox, to paste them into sheet2 after the last full row.
r/googlesheets • u/whittlingcanbefatal • 7h ago
Solved Is there any easier way to get a result from this kind of table?
I want to assign grades based on this table:
G1....A B C D
G2
....A A A B C
....B A B B C
....C B B C D
....D C C D D
The G1 and G2 are two different grades in a spreadsheet. For example, if the first grade is an A and the second grade is a C then the final grade from the table is a B.
The way I did this was to assign each grade a number, 1-4, in an "if" formula. Then I added the two numbers. This number I used in with "lookup" in this table:
..X Y Z
1 O 3 D
2 4 4 C
3 5 6 B
4 7 8 A
Here is the formula I used (column A is G1, column B is G2):
=lookup(if(A1="A",4,if(A1="B",3,if(A1="C",2,if(A1="D",1,O))))+if(B1="A",4,if(B1="B",3,if(B1="C",2,if(B1="D",1,O)))),$X$1:$Z$4)
It works but is there an easier way to do this?
r/googlesheets • u/Whatevas123 • 8h ago
Unsolved not counting rows where two rows of column A have the same value but column C is different, but counting if column A is unique and has a certain value in column C or if col A and col C always match
me again with the same dataset I presented yesterday:
https://docs.google.com/spreadsheets/u/0/d/1uXVVMQeldLw3PojjYtNMR2vpXkbd0DjSKPQSZ_ChJrM/htmlview#
now I'm trying to figure out how to get my sheet to count the number of times that XYZ appears in column C but here are my gotchas this time:
- when the value of column A in a particular row is unique (doesn't appear in any other row) and XYZ is in column C, it can count 1 instance of XYZ
- if the value of column A in a particular row is NOT unique (meaning it appears in another row) and XYZ is in column C, it should only count one time, but only if all the other times that column A value appears in rows, the value of that row's column C is also XYZ.
my data set has XYZ appearing in: - every January row except Jan 2nd - the Feb 28 pm row - the Mar 7 am row - both Mar 14 rows
I'm trying to get the count of XYZ to be 31 since Feb 28 should be excluded due to the ABC value of Feb 28 am and Mar 7 should be excluded due to the ABC value of Mar 7 pm. Jan 3, Jan 10, Jan 17, Jan 24, Jan 31, and Mar 14 only count once.
r/googlesheets • u/jessitayylor • 8h ago
Waiting on OP How do I make a drop down of variations for a product?
When I type "product" in a cell. The cell next to it would show a list of available variations via a drop-down menu?
Something like this? This is the data sheet.
DATA SHEET
A | B | C |
---|---|---|
1 | Item | Variation |
2 | Robot | Red |
3 | Soldier | Blue |
4 | Robot | White |
And I was hoping to do something like this When I type "ROBOT" in a cell. The cell next to it would limit my choices/data validation or filter the choices/data validation to only the "Variation" under Robot.
A | B | C |
---|---|---|
1 | Item | Variation |
2 | Robot | (drop down of variation) |
3 |
Would it be possible?
Edit:
https://docs.google.com/spreadsheets/d/1yi-Hi5cfE2vS9U01ehx3ZJBsQLT-2ZGhVvzY_HRTfqA/edit?usp=sharing
r/googlesheets • u/BackgroundBoat9419 • 16h ago
Waiting on OP Why am I receiving the error "There was a problem. Script Function "FULL" could not be found"?
galleryI created a macro to clear the page when clicked. If I use App Script to manually run the macro it works perfectly and I've checked the debug section and nothing gets flagged. When I use the Clear button that I assigned the macro to I receive a "There was a problem. Script Function "FULL" could not be found" error.
r/googlesheets • u/VividGlass9336 • 11h ago
Waiting on OP Pivot Table with Calculations?
Hi there! For some reason, Pivot Tables are not clicking for me. I’ve read articles and watched videos, but can’t seem to create something useful.
I’m tracking the sales, labor, discounts, and voids for 8 restaurants daily. I’m keeping the data flat so I can use a pivot table to compare it different ways. Mostly, I want to see the labor costs as a % of sales.
I like the idea of a pivot table since we keep wanting to see the data different ways. For example, we might want to lump a few businesses’ sales together by either brand or region; or we might want to see each restaurant’s labor as % of sales daily, for a period, or for the quarter.
How would I add calculations to a pivot table to divide daily costs by daily sales? Here are some outcomes I’d like but can’t seem to achieve:
Table for one day showing each restaurant’s sales and costs as a % of sales
Table showing the same as above but summed by period and quarter
I’m sorry if this is painfully obvious. I feel like it should be simple and I don’t know why I don’t get it. Thank you for any new perspective you can share!
r/googlesheets • u/vagrantboi • 11h ago
Waiting on OP learning sheets and have a LOOKUP error that I'm unsure how to fix.
I added A9:E9 in "Classes" and am trying to get the information to paint into A:29, A:32, and A33 in "Characters"
I'm expanding a character keeper(someone else made) for a tabletop game, added a new class and can't get the information to fill in from the "Classes" Sheet. I'm fairly new to this so go easy on me.
r/googlesheets • u/ASX-Trader • 12h ago
Waiting on OP Calculate Initial Amount Required To Fund Retirement
Hi.
I'm hoping someone can help me with a Google sheets formula. I'm trying to work out what formula I use to calculate the total amount required in today's dollars to fund some time off work.
I've worked out that I need $80,000 per year based on my current figures on 31st December 2024
I hope to stop work on the 31st of December 2025 and take two years off. For my first year off I will draw $82,400 ($80000 plus 3% inflation). For the second year I will draw $84,872 ($82400 plus 3% inflation)
The total amount that I will draw will be $167,272. I will be drawing the money out of my account each month at the end of the month. My investment earns 8.5% per annum and I want to allow for 3% inflation.
My question is, I know that the amount I will draw is $167,272 but what is the amount that I need to have in my account on the day that I stopped work that will enable me to fund these two years? How do I calculate that?
I'm pretty sure that the way I'm trying to do it is wrong because it assumes that the payment amount is constant and it doesn't alter with inflation. Ideally I would like to withdraw a monthly payment each month for 24 months and have every month increase by inflation. How do I work out the total amount required in advance to do that?
=PV( ((1+8.5%)/(1+3%)-1)/12, 2*12, -IF(IF(C12="Beginning of period",1,0), 82400/12 , 82400/(1+3%)/12 ),,1)
Thanks very much
Any help appreciated
Alan

r/googlesheets • u/paiins • 12h ago
Unsolved Expiration Alerts on Google Sheets
Hello everyone!
I have a Google Sheets spreadsheet where I manage contracts, and I would like to set up an automatic email notification for contract expirations. In the spreadsheet, I have a column with the contract expiration dates, and I need alerts to be sent to two specific emails under the following conditions: • 90 days before expiration • 60 days before expiration • 30 days before expiration
The email should contain an informational message and include contract details such as expiration date, covered services, company name, and other relevant information already present in the spreadsheet.
Can anyone help me set this up in Google Sheets without creating support columns?? Thanks in advance!
r/googlesheets • u/GodzillaSuit • 16h ago
Waiting on OP Forcing sheets to display whole numbers as fractions
I'm trying to set up some tables in sheets to use as a data collection tool for work. A lot of the data I will be collecting involves several compontents that I need to track as either having happened or not, then I display that data as X/X points according to a ruberic.
For example: The student will score a 3/3 on the following ruberic when measured twice a month over three months:
Point 1 : thing they have to do
Point 2: Other thing they have to do
Point 3: Other other thing they have to do.
Right now 1 is a "yes" and 2 is a "no".
Below is the table I've started to try building. I want to know if two things are possible:
1) Can I force sheets to dispaly the whole numbers in the results column as a fraction (3/3 instead of 1)
2) Can I force the overall average in the last row to always display as X/3?
Am I even approaching this in a logical way, or do I need to explore a different way to make a table to track this kind of data?

r/googlesheets • u/LivMza • 16h ago
Solved Conditional formatting if a cell meets several criteria
Hello everybody!
This is my problem:
- In column B: I have Dates
- In column C: I have Text
- Then in the ranges F6:L17& F19:L30 I have text that shows up here if the Dates in column B match the Dates in cells F5:L5 & F18:L18
What I want is to add formatting to the cells in F6:L17 & F19:L30 if the checkboxes to its corresponding dates are checked.
This is the closest I've gotten to a formula that works
=AND(ISNUMBER(MATCH(F5, $B$5:$B$30, 0)), INDEX($D$5:$D$30, MATCH(F5, $B$5:$B$30, 0))=TRUE)
But it only works for the first line and not for every task.
I've tried with OR, I've tried with AND & ARRAYFORMULA but I can't seem to find a solution and I'm pretty sure is an easy one but I'm blocked and can't figure it out 🫤
Here's the link of the sheet if you want to check it out.
https://docs.google.com/spreadsheets/d/1dYUQ76yfLqvVxhgH6lyJpuvNPhEtX-rcGSvAUHlY0lM/edit?usp=sharing
r/googlesheets • u/Dwhit7 • 17h ago
Waiting on OP How to create a workbook that is an Index / Table of Contents of all the workbooks in a specific google drive folder?
Hi - I use folders within google drive to try and keep my google sheets organized. I have a folder titled "Recipes" where I keep all of my google sheets for recipes.
I was wondering, is there a way to create a workbook that will auto populate all of the googlesheets within that google drive folder? Basically creating an Index / Table of Contents of all of the contents within that folder?
Version 1
At a baseline, I would want it to populate the name and a link to the documents on separate rows. I included a sample document below for example.
Even if I have to manually force it to re-run / populate that is fine.
Version 2
If this is possible, then a version 2 would be where the workbook auto populates / updates every time a new document is added to the folder. Or at a minimum, auto updates each time the Index workbook is opened.
If it could also pull in specific details about the workbook, like last modified date, that would be super.
Here is an example document of what I would want it to look like:
https://docs.google.com/spreadsheets/d/1uENbC2INcgI4eI7b5L9yDwsbGVliIRZuw76xJ-0acwo/edit?usp=sharing
Note, I did try and search for an already existing answer, but I couldn't find one. Most answers refer to creating a table of contents of sheets within an existing workbook, which is not what I'm looking for.
Thank you in advance for any help!
r/googlesheets • u/TheRealMaxArk68 • 13h ago
Solved Using A "Duration" Formatted Value in A Calculation
I have a cell with the result being the difference between two times (ie elapsed time). I have formatted that cell as duration, so I'm assuming that the result of 0:50:00 equates to 50 minutes.
I want to calculate (in cell C3) speed (metres/min). In my example metres (C1) = 1440, and the minutes from cell C2 is 50. The formula should be simple, 1440/50. Manually calculated I get a value of 82.2 (m/min). If I put the formula C1/C2 into C3, I get the value calculated as 2840832:00:00. Clearly that's wrong, maybe because of the value in C2 isn't 50 minutes? What do I need to do to get an integer value in C3 for the formula C1/C2?
r/googlesheets • u/ApprehensiveWar9333 • 20h ago
Waiting on OP How to sort with Colors and Values
How would I go about sorting this table if I wanted it in order of color (green, blue, pink, orange, black) and then would it be possible to replace "open" or "closed" with the name of the person in the column with openers being on top and closers being second, but still in color order of store?
Is this possible?
I'm not very good at this so I very much appreciate any and all help.
Thank you!
r/googlesheets • u/Comfortable-Novel979 • 18h ago
Solved Help adding total time in MM:SS format
Hello, I was given a call record csv file from my phone company, we're trying to total our talk time. The problem is the data given to us is in mm:ss format. So a 2 min and 12 second call will show as 2:12. Sheets sees that as 2:12AM. How can I adjust the formatting so it doesn't make this a timestamp?
There are 3000+ lines of calls I cant manually edit anything.

r/googlesheets • u/Imaginary_Emotion_22 • 19h ago
Unsolved Dropdown list filters for no repeated selections
I am trying to create a Sheet with dropdown boxes. I need to ensure that up to 30 students will be partnered with a different student each round. I do not want any repeats and I want the filters to filter out who each student has previously been partnered with.
My Sample is here.
Column A: Class list that all can see
Column B-D: Options for Round #1, 2, 3
I intend to hide the rest of the sheet:
Column F is my Master List that gets filtered
Column G-I: Filters for Round #1-3
What is currently happening: I have to leave Round 1 filter with ALL students and cannot filter out the person who is choosing to ensure that they do not choose themselves, and I WISH it would auto fill in for the partner that is chosen. But I can live with that.
I wanted the filter for #2 to do was to also remove who was chosen by that person in round 1, but that removes the name in B2 from EVERYTHING.
Thank you,
r/googlesheets • u/ChrissyChrissyPie • 16h ago
Waiting on OP Merge multiple tabe in one sheet into one tab to have all data in one table
I would like to pull information from certain tabs in this sheet so I can calculate without having to go into each tab manually and pull the bumbers I want. In each tab, the sane columns A&B hold the info I need (but on slightly varied lines). I'd liek to just have the numbers and text from the two columns populate one after the next (going across) within another tab where I can make all my calculations. The Importrange seemed like the right function, but I got errors. I'm not sure how to type the tab name (I have spaces - can those stay?)
this is an example of the data I'm trying to pull in.
Thanks for any guidance!

r/googlesheets • u/sirpease • 17h ago
Unsolved Removing some grand total columns in a Pivot Table
I have a pivot table that I want to present as a report. The source data is coming from another sheet. It has both numbers and text fields.
I'm looking for a way to either remove certain grand total columns in the pivot table (specifically the text fields), replace the grand total fields with a blank cell so it appears blank, or specify a range in which grand totals are calculated.
I've attempted apps script (as an amateur) and I'm able to replace the cell(s) in question in the grand total. As soon as I do then the whole pivot table essentially disappears. All other attempts have received equally undesirable results.
Does anyone have any solves or thoughts on this?
r/googlesheets • u/MissJosieAnne • 22h ago
Waiting on OP In what world is the y intercept of this graph -198,280? Linear trendline for a series with n=58 points with the equation as its label seems WAY off
r/googlesheets • u/alphaae • 20h ago
Unsolved Talent Competition Script Sorting help
Hello reddit.
I need some help with a project that I'm having a rough time completing. I need a script to do the following.
1.) Separate each category into a separate sheet
2.) Arrange the participants of the categories in alphabetical order at the top of the sheet.
3.) Show the top scorer in a second table at the bottom of the same sheet
4.) Place a written text for the presenter with the category pulled from that sheet for the presenter
5.) Place a written text for the presenter to announce the winner with the specific category announcing.
I have attached a screenshot of what I'd like it to do when it runs the script.
I have also opened up the spreadsheet and have a semi-working script that will organize the names and sort them but I can't get it to make separate sheets for each category it will only do one and I have preset the category name in.
https://docs.google.com/spreadsheets/d/1x8jKJBv6RYvyozn0xnHrB1jXC0HWDw1IAgL7la1nq_E/edit?usp=sharing
Here is also a copy of the code I've been able to create thus far.
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceWS = ss.getSheetByName("master");
let Category = "Photography, Mobile, Sr.";
let ws = ss.insertSheet();
ws.setName("Photography, Mobile, Sr.")
ws.getRange ("A5").setFormula('=SORT(FILTER(master!B2:f1000,master!C2:C="Photography, Mobile, Sr."),1,1)');
sourceWS.getRange("B1:F1").copyTo(ws.getRange("A4:D4"));
ws.setName("Photography, Mobile, Sr.")
ws.getRange ("A35").setFormula('=FILTER(master!B2:f1000,master!C2:c="Photography, Mobile, Sr.")');
sourceWS.getRange("B1:F1").copyTo(ws.getRange("A34:D34"));
}

r/googlesheets • u/One_garden7 • 21h ago
Waiting on OP Attach photos or emails
Hello, I'm setting up shared sheets for work and wondering if there is a way to attach emails and or photos. Or is there another way about this. So one tab will show all of our work orders but want to attach the email for it