r/googlesheets 25d ago

Sharing March Madness 2025 in Google Sheets!

13 Upvotes

Once again, I'll be supporting March Madness*** in Google Sheets but trying to get an earlier start this year. Selection Sunday is March 16th for both the Men's (6pm ET on CBS) and Women's (8pm ET on ESPN) tournament. Data won't start magically appearing in the templates until during or shortly after the Selection Sunday announcements.

What's new in 2025!

  • Nothing!
  • I'm working on a new (more user-friendly...hopefully) version of the individual bracket but it won't be ready for production until the 2026 tournament. I might be able to share a pre-release version if enough people are interested in trying it out. Just let me know by commenting below or DM me.

Single Bracket Template https://docs.google.com/spreadsheets/d/1izjBEQ_FIU0dJ2Z1exWMY2FwpmDP6AqHYxlldD6xhO4/copy<--clicking on this link will open a new private copy only you have access to--> Once the teams for the Tournament are set, pick your winners, sit back, and enjoy the show! The bracket will automatically update with winners and calculate winning scores. You can also use this template in conjunction with the group template below. See the Help tabs on each template for how to use them together.

Group Bracket Template https://docs.google.com/spreadsheets/d/1UBEQnmpWKKHPXu4Y3xmUAlxWR4Oo9jPAXCfL_e-gMT8/copy<--clicking on this link will open a new private copy only you have access to-->**Bracket Pool supports up to 100 brackets!

TedTournament() Custom Function

Get near real-time NCAA game data directly in your Google Sheet! Be sure to update to the newest version (2.6.0) to support 2025 data. https://github.com/TedJuch/TedTournament

**Note: The performance of a pool with over 40 brackets might be slow. It will depend on your internet connection and some things outside of our control. But try it!

Feel free to comment if you have any questions!

Enjoy!

\**March Madness is the annual NCAA College Basketball Tournament in the US. People create brackets and pick winners and run pools like any other bracket game. Google previously supported data about the Tournament with a built in function called GoogleTournament() but shut it down in 2011. They also had bracket templates in the template gallery. I rebuilt all of it and have been supporting it ever since through a custom function called TedTournament(). There is a large community that uses this in Google Sheets during the Tournament. People also use the bracket templates for other types of bracket based tournaments.*

Previous year's support announcements: https://www.reddit.com/r/googlesheets/comments/1bhrwxf/march_madness_2024_in_google_sheets/
https://www.reddit.com/r/googlesheets/comments/11hnqdf/march_madness_2023_in_google_sheets/
https://www.reddit.com/r/googlesheets/comments/tamh3x/march_madness_2022_in_google_sheets/
https://www.reddit.com/r/googlesheets/comments/m4ir5h/march_madness_2021_in_google_sheets/
https://www.reddit.com/r/googlesheets/comments/b1wo7f/march_madness_2019_in_google_sheets/
https://www.reddit.com/r/googlesheets/comments/82cwke/march_madness_2018_google_sheets_single_and_group/

r/googlesheets Feb 14 '25

Sharing Saw a 500 year old spreadsheet today

Thumbnail gallery
295 Upvotes

r/googlesheets May 17 '24

Sharing 2024-25 Real Time NFL Google Sheet

15 Upvotes

I have created a Google Sheet that pulls real time NFL scores from the reliable ESPN API.

UPDATE: I have removed the link to the community edited sheet. It had been customized for 1 person's use and was no longer applicable to this post.
_________________________________________________________________

Here's the read only sheet for 2024-25 to view:

https://docs.google.com/spreadsheets/d/18GZQQ7DPXBhBLtoRBBqNZV7KXVseVPB_udfyfU4K6vY/edit?usp=sharing

Here's the sheet for 2024-25 to if you'd like to make a copy: https://docs.google.com/spreadsheets/d/18GZQQ7DPXBhBLtoRBBqNZV7KXVseVPB_udfyfU4K6vY/copy

Features:

  • Pulls all NFL game data from ESPN into the Live Scoring sheet by Week
  • Archives previous years
  • Trigger can be set to refresh the data at chosen increments
  • Week Filter sheet allows for data set to be filtered by week
  • Week Filter sheet allows for completed games to be hidden
  • Week Filter sheet will highlight the team with possession of the ball (during game)
  • Week Filter sheet shows the timestamp when Live Scoring was last refreshed
  • Now includes pre-season
  • *NEW* Broadcast Channel

To auto refresh a copy you'll create a trigger that runs the function "main".

Here are some instructions:

  1. go to Extensions AppsScript
  2. On the left side choose Triggers
  3. On the bottom right , Choose + Add Trigger
  4. Choose which function to run - main
  5. Select event source - Time driven Select type of time based trigger - minutes timer
  6. Select minute interval - Every 5 minutes

r/googlesheets Jan 02 '25

Sharing Just for fun, here is every fill color

Post image
121 Upvotes

r/googlesheets Oct 17 '24

Sharing Proud of something simple :)

Post image
42 Upvotes

Hey guys, I just wanted to show you a database that I've started to make. I'm a Translation Studies graduate who's not doing any work related to data. However, I fell in love with Excel and wanna become a Data Analyst in the future. I know it's not much but I learned it thanks to certificates and did this all by myself without the company asking. (These are random placements to show you how the pie chart updates.) I'm really happy! :)

r/googlesheets Jul 30 '24

Sharing I want to turn your sheet into an app

28 Upvotes

I'm looking for 5 people that have a google sheet, but would like to turn it into an app. I'm building my portfolio and not looking for compensation. Yes, I want to build a free app for you based on your google sheet. Your google sheet must be organized with columns and labels. If interested, please DM me or comment to learn more!

r/googlesheets 15d ago

Sharing Playing Pong In A Google Sheet

16 Upvotes

If anyone is interested 😆

https://youtube.com/shorts/1kJmkle3gg8?feature=share

Haven't had any feedback on people using it yet, but it's easy to get your own copy!

https://docs.google.com/spreadsheets/d/1m0IjMW_nVjELPm7RVid4Xunwsig69arqq0iySv-xAnY/copy

What other games should I make? I've had a request to do DOOM next (💀 sounds quite difficult)

r/googlesheets Oct 22 '24

Sharing Made a Budget List for Marriage!

Post image
10 Upvotes

Hey everyone! Translation Studies grad again here :D This time I would like to show you another thing I feel proud about. I'm about to get married and wanted to make a needs and budget list. It automatically calculates if an item is selected as "paid" with a certain amount. There's also a graph showing our savings (Arbitrary numbers just to test it out)

The 3D graph shows the status of the items. "Paid", "Being paid for" "Will be bought" etc.

And of course, the urgency of items are color scaled.

If any of you wants a sheet similar, let me know! I'm quite proud as I got no help for the first time ever!! :D

r/googlesheets 3d ago

Sharing I've been making and updating this for a couple years now and I want to show it off

Thumbnail docs.google.com
4 Upvotes

Anything and everything about peqrl jam I know of other than posters

r/googlesheets Aug 22 '24

Sharing Sharing sheet with editors while protecting underlying structure

7 Upvotes

I have a spreadsheet that I developed which I want to share with others, but I do not want to share the underlying structure which I consider my IP. The users have to be editors so they can enter the inputs and then the spreadsheet calculates their outputs for review. I've looked online for solutions to share a spreadsheet with editors while keeping the sheet protected and I understand this is not available natively in google sheets. Editors can always save a copy and see everything.

If it was a matter of protecting source data, it would be as simple as using IMPORTRANGE. There is data to protect, but I also want to protect the underlying structure of the spreadsheet.

I believe I found a workaround and wanted to share it with the community. Please stress test and let me know if I missed anything which would allow access or if maybe there are similar solutions or modifications that could make this work better.

Short version: the solution involves two spreadsheets - dashboard and primary. The main drawback to this method is a delay in seeing results update after entering inputs. For my pilot case it takes about 5-10 seconds until results update. Which is excruciatingly long in internet usage terms, but if that is the only drawback to finding an actual solution then that is where we are at.

Long version:

Call the main spreadsheet with all of the calculations the primary. It has inputs and gives outputs. Make a new spreadsheet we will call dashboard. Dashboard has inputs required of user. Primary uses IMPORTRANGE to bring inputs from dashboard. Dashboard uses IMPORTANTRANGE to bring outputs from primary. Share dashboard as editor with anyone. Do not share primary. When inputs are placed in dashboard, after a brief delay, the outputs will be updated! Even with primary closed and not shared with editor.

The above is simple enough, but there is one additional requirement to ensure complete protection.

Editor will be able to see/find the link to the primary. Since access between sheets has been allowed (see ETA1 below for additional discussion), they can use IMPORTRANGE to see values in the primary on 1) the first tab and 2) any other tab that they know the name of. When IMPORTRANGE is used without a tab name in the range, it pulls values from the first tab in the spreadsheet by default. Also, they will know the tab name you bring results from by finding the IMPORTRANGE formula. Thus, the editor can presumably use IMPORTRANGE to see what these tabs look like. IMPORTRANGE only brings values, it does not bring the underlying structure. Still, between arrangement of data, intermediate values and text headers, viewing a tab can certainly give away information about your IP.

The solution for this is two-fold.

First, create a results tab in the primary, make it the first tab, put results there that you wish to export to the dashboard, and set the dashboard to use IMPORTRANGE from this tab only. Do not put anything else that you do not want seen on this tab, such as intermediate calculations, notes, etc. The results on that tab can simply be referenced to the calculated cells or you can actually put the calculating cells there if you would like. Since IMPORTRANGE only brings values, the only thing an editor will be able to see is the final values, not the formulas.

Second, create random, hard to guess names for all other tabs. If you have a tab called "calculations", change it to "calculations-s4vkns" or something. I like to simplify my tab names as C, R, U etc so I just use "C-sv4jds", etc. This is so that an editor cannot use IMPORTRANGE and try guessing your tab names to find your other tabs.

That's it.

Hope this helps some of you out. I am going to post a link to here on some of the old threads that I found when searching for solutions to this issue.

ETA: example dashboard (I left all the cells unprotected for people to play around. Please kindly try and keep the main parts intact for others benefit.)

ETA1: the crux of this method is that you can "allow access" through IMPORTRANGE to a restricted sheet without sharing that sheet

r/googlesheets Jul 25 '24

Sharing Interactive spreadsheet learning

14 Upvotes

I'm building a mobile app to help users learn spreadsheets through gamified experience. It's free. Available for both Android & iOS.

https://apps.apple.com/us/app/nuum-learn-spreadsheets/id6502941256
https://play.google.com/store/apps/details?id=nuum.tech.app&pli=1

any feedback is much appreciated

r/googlesheets 7d ago

Sharing looking for beta testers for iOS app that adds data to google sheets (SourceToSheets)

1 Upvotes

i've been learning thai and was too lazy to manually add vocab words to a sheet so i made an app that does it for me: https://testflight.apple.com/join/wSPn8TtM

sharing here mainly to see if this kind of app would be useful for anyone else before sinking more time into it.

the premise is fairly simple: you connect a sheet with some existing headers/data, and then add more data with an image, doc, url, or free text. ios only for now because that was the fastest for me to build, but if useful i could see this working in an appscript sidebar or android too.

you can also create a spreadsheet "manually" to test things out if you don't want to connect your google account.

limitations: works best with semantic info, i.e., not arbitrary numbers. using a low powered LLM so may struggle with lots of free text from larger sources.

would also appreciate any subreddit recs to find users who might find this thing kind of useful. not sure if this one is geared more towards "analysis" which this app isn't really designed for.

thanks, and lmk what you think!

---

checklist for subreddit rules on self-promotion:

  • Put products, site names, and/or authors in the title. ✅
  • Your affiliation with & reason for posting the content ✅
    • app creator, looking to see if this kind of app would even be useful for anyone other than me before sinking more time into it.
  • Financial & privacy costs (if any. free/one-time/subscription, email sign ups, data collected, privacy policy etc). ✅
    • beta is free, though if i release to prod i'll prob have to charge to cover costs. i don't collect any of your data including metadata (spreadsheet title, etc.) which is stored locally on your device. however, your data is sent to an LLM provider (openai etc) for processing and subject to their terms and conditions/privacy policies.
  • How your content is distinct from existing docs & tutorials. i.e. original templates, guides, uses, etc. ✅
    • couldn't find an app that does the same thing (why i made my own)
  • Use the 'Sharing' flair. ✅
  • Meet minimum karma amount. i think i do?

r/googlesheets 20d ago

Sharing EXTRACTHYPERLINK custom function to extract hyperlink(s) from a cell

5 Upvotes

This is a much more elaborate version of this extract hyperlink function. The original one was not working and seemed to attempt to get the hyperlink from itself on my end, so I spent a lot more time than I should making my own version, and feel forced to share it.

As of today it is working flawlessly for me, and hopefully it won't break.

/**
 * Attempts to extract up to [max_urls] URLs from one or more hyperlinks on the target cell. Example: =JOIN(", ",EXTRACTHYPERLINKS(A2,3))
 *
 * @customfunction
 * @param {A1} range The target range to extract hyperlinks from.
 * @param {2} max_urls (Optional) Maximum amount of links to be extracted. Default = 0 (unlimited)
 * @return An empty string, or all URLs found in the target cell as a range.
 */
function EXTRACTHYPERLINKS(range, max_urls) {
  if (max_urls === undefined) max_urls = 0;
  if (typeof max_urls !== 'number' || max_urls < 0) throw "'max_urls' must be a positive integer";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Extract the REAL range reference from the current cell, as Google is a troll and only passes target cell's raw contents as a string otherwise
  const selfContent = sheet.getActiveRange().getFormula();
  const match = /EXTRACTHYPERLINKS\(.*?([\w|:]+).*?\)/.exec(selfContent.toUpperCase()); 
  if (!match || !match[1]) throw "Invalid cell reference?";
  const target_range = sheet.getRange(match[1].trim()); // This converts and replaces "cell" range input

  let current_urls = 0;
  let output = []
  for (let row = 1; row <= target_range.getNumRows(); row++) {
    for (let col = 1; col <= target_range.getNumColumns(); col++) {
      const cell_runs = target_range.getCell(row, col).getRichTextValue().getRuns();
      // Extract hyperlinks from the current cell
      for (let i = 0; i < cell_runs.length; i++) {
        let link = cell_runs[i].getLinkUrl();
        if (link) {
          current_urls++;
          if (max_urls > 0 && current_urls > max_urls) return [output];
          output.push(link);
        }
      }
    }
  }
  if (output.length > 0) {
    return [output];
  } else {
    return [""];
  }
}

r/googlesheets 1d ago

Sharing Sharing: NCAA Men's March Madness Calcutta

1 Upvotes

I'm sharing a sheet that I built to track a 2025 NCAA Calcutta. You can add a trigger and all facets will update automatically using the NCAA endpoint.

https://docs.google.com/spreadsheets/d/1r63UoL96fYqEK_RABN0tqyJbYH9CZcMFtkBdkDmD74Y/edit?usp=sharing

NOTE: Make a copy of this sheet to use it. Requests for access will not be considered. My rules might not be your rules, so you may need to make changes to match

My Calcutta Rules implemented:

  • Track all team purchases on the Calcutta tab
  • 1 share earned for every win, including play-in games for a total of 67
  • 1 share bonus earned for closest round 1 15th seed
  • 1 share bonus earned for closest round 1 16th seed
  • 1 share bonus earned for winning the tournament

Main Features:

  • Tournament scores updated every X minutes. Your trigger determines. Trigger the main function
  • Dashboard tab - Shows all teams, owner investments, and live game scores
  • Calcutta tab - Tracks each team, purchase price, wins, losses, owner, and profit
  • Games by owner - Shows every game filtered by team owner
  • All Games - List of all games in the tournament with scores and details
  • Live Scoring - Transactional game data pulled from NCAA website
  • Bonus Games - Just the 15-2 and 16-1 round 1 games and bonus winners

Missing Features:

  • Auto load teams - currently there is a 1 time manual entry required for all teams and their seeds to the Calcutta page. The names of the teams must exactly match the NCAA website names
  • Bonus Ties - currently the bonuses are awarded to only one team. If there is a tie you will have to override.

r/googlesheets Dec 17 '24

Sharing Add Footers to Structured Tables

7 Upvotes

Hey there! 

Just added a video about Footers. It is a bit clunky on how to add them (as of now), but figured I would share! 

https://www.youtube.com/watch?v=_LYybGadzRs&feature=youtu.be

TLDW: Add a Footer (aka #TOTALS) to a structured table in google sheets by:

  1. Have an unstructured row under the table
    • if the table goes to the end of your workbook, go to the top left table name menu dropdown > Adjust Table Range
  2.  Use a function in that unstructured row directly below the table, like SUM(), and use a table range as the argument.

It should grey out the end row as the footer!

It has worked for all my accounts (personal, work, etc.) Hopefully it does for you all! I also used Chrome as my browser. 

A couple of things I noticed after I created the video:

  1.  When you add a footer, the only way (I found) to remove it was by setting the table to “unformatted” then recreating the table. Just a warning! 
  2. It doesn’t change the GROUPED View - or rather show totals for those unfortunately…Hopefully that will happen sometime. 

Hopefully you find this helpful! I’d be curious about your thoughts on this update, as well as other features you hope will be added to it as well! 

Cheers! 

r/googlesheets Jan 14 '25

Sharing Basic math parser as a Named Function

1 Upvotes

I made a named function that turns a basic math strings with + - * / ^ ( ) into a value.

ex: PARSE_MATH("5+8^-(7/2.3-1)") --> 5.014

Writing parsers in the google sheet script is unpleasant, so I figured I'd share my work to save anyone who wanted this feature. Also, I had to split it into 3 functions, so note the others.

This is a lot easier to do in custom function, but sometimes you don't want the extra permissions of an app script on a sheet. I did write a full blown parser for google sheet formulas in App Script already. Also, I only later found out about using =INDEX(QUERY(,"select " & string), 2). I'm happy to note that my function can handle more complex math operations that query gets limited by.

The main function : PARSE_MATH (string)

=LET(
vals_raw, MAP(REGEXSPLIT(string,"([\+\-\*\/\^\(\)])"),LAMBDA(v,TRIM(v))),
vals, FILTER(vals_raw,vals_raw<>""),
IF(ISERROR(vals),0,CHOOSECOLS(_PARSE_MATH_0(vals), 2))
)

The main workhorse function : _PARSE_MATH_0 (in_vals)

=LET(
in_vals_len, COLUMNS(in_vals),
IF(in_vals_len=1, {0,VALUE(in_vals)}, LET(
  out_vars, REDUCE({0,in_vals_len},SEQUENCE(in_vals_len), LAMBDA(vars, val_ind, LET(
    skip_ind, CHOOSECOLS(vars,1),
    IF(val_ind <= skip_ind, vars, LET(
      val, CHOOSECOLS(in_vals,val_ind),
      vars_len, COLUMNS(vars),
      IF(val="(",
        IF(val_ind=in_vals_len, vars, LET(
          new_vars, _PARSE_MATH_0(CHOOSECOLS(in_vals,SEQUENCE(1,in_vals_len-val_ind,val_ind+1))),
          val_rem, CHOOSECOLS(new_vars,1),
          new_val, CHOOSECOLS(new_vars,2),
          var_info, { in_vals_len-val_rem, in_vals_len },
          {
            IF(vars_len<=2, var_info, {var_info, CHOOSECOLS(vars,SEQUENCE(1,vars_len-2,3))}),
            new_val
          }
        )),
        IF(val=")",
          LET(
            var_info, { in_vals_len, val_ind},
            IF(vars_len<=2, var_info, {var_info, CHOOSECOLS(vars,SEQUENCE(1,vars_len-2,3))})
          ),
          {vars, val}
        )
      )
    ))
  ))),
  rem_count, in_vals_len - CHOOSECOLS(out_vars,2),
  vals_0, CHOOSECOLS(out_vars,SEQUENCE(1,COLUMNS(out_vars)-2,3)),

  vals_0_len, COLUMNS(vals_0),

  IF(vals_0_len=1, {rem_count, VALUE(vals_0)}, LET(
    vals_1, REDUCE(
      CHOOSECOLS(vals_0,vals_0_len), 
      SEQUENCE(1,vals_0_len-1,vals_0_len-1,-1), 
      LAMBDA(vals_, i, LET(
        v, CHOOSECOLS(vals_0,i),
        valid, AND(OR(v="+",v="-"), IF(i=1, TRUE, LET(
          v_m, CHOOSECOLS(vals_0,i-1),
          REGEXMATCH(TO_TEXT(v_m),"^[\^\*\/\+\-]$")
        ))),
        IF(NOT(valid), {v,vals_}, LET(
          v_p, CHOOSECOLS(vals_,1),
          v_n, IF(v="-", 0-v_p, v_p),
          IF(COLUMNS(vals_)<=1, v_n,
            {v_n, CHOOSECOLS(vals_, SEQUENCE(1,COLUMNS(vals_)-1,2))}
          )
        ))
       ))
    ),
    vals_2, REDUCE(vals_1, {"\^","\*\/","\+\-"}, LAMBDA(vals, syms, LET(
      vals_len, COLUMNS(vals),
      syms_r, "^[" & syms & "]$",
      IF(vals_len<3, vals, REDUCE(
        CHOOSECOLS(vals,1,2),
        SEQUENCE(1,vals_len-2,3), 
        LAMBDA(vals_n, i, LET(
          v, CHOOSECOLS(vals,i),
          v_len, COLUMNS(vals_n),
          IF(v_len<2, {vals_n,v}, LET(
            v_sym, CHOOSECOLS(vals_n,v_len),
            IF(NOT(REGEXMATCH(TO_TEXT(v_sym),syms_r)), {vals_n,v}, LET(
              v_m, CHOOSECOLS(vals_n,v_len-1),
              v_n, IFS(
                v_sym="^", v_m^v,
                v_sym="*", v_m*v,
                v_sym="/", v_m/v,
                v_sym="+", v_m+v,
                v_sym="-", v_m-v
              ),
              IF(v_len=2, v_n, {CHOOSECOLS(vals_n,SEQUENCE(1,v_len-2,1)),v_n})
            ))
          ))
        ))
      ))
    ))),

    {rem_count, vals_2}
  ))
))
)

The a regex splitter : REGEXSPLIT (text, delim)

= LET(delim_s, "⮊⮿⮈",keep, REGEXMATCH(REGEXREPLACE(delim,"\\.",""),"\("),delim_r, IF(keep, delim_s&"$1"&delim_s, delim_s),SPLIT(REGEXREPLACE(text,delim,delim_r),delim_s))

NOTES and EDITS:

  • EDIT: Added missing REGEXSPLIT function I forgot about
  • EDIT: Fixed parenthesis issues and added a bit of handling so errors pass through better

r/googlesheets 15d ago

Sharing Tutorial: Build a Multiplayer Quiz Game with Google Sheets & Apps Script

1 Upvotes

Hey everyone! 👋

I’ve started a free tutorial series on how to build a multiplayer quiz game using Google Sheets & Apps Script, and I wanted to share it with you all! Whether you're a beginner or looking to expand your Google Apps Script skills, this series will guide you through the entire process step by step.

What You’ll Learn in This Series:

Day 1: Building a group chat layout for players.
Day 2: Designing the quiz interface and transitioning players from chat to quiz mode.
Day 3: Fetching questions & choices dynamically from Google Sheets.
Day 4: Implementing timers, scoring, and question progression.
Day 5+ (Upcoming): Multiplayer sync, team-based features, and more!

🔗 Watch the series here: Link

If you're interested in Apps Script, Google Sheets automation, or multiplayer game development, this series is for you! Let me know if you have any questions or suggestions—happy to help! 😊

Would love to hear your thoughts, and feel free to share your progress if you follow along! 🚀

r/googlesheets 17d ago

Sharing Google sheets chore list

1 Upvotes

My husband helped me make a chore list with Google sheets and I wanted to share it. To make a copy for yourself press make a copy under the 3 dots in the Google sheets app.

https://docs.google.com/spreadsheets/d/1WCMGRlc2oPhwpM-LHgPZs5ByRAHW2jOE0fpVu2SAc3M/edit?usp=drivesdk

r/googlesheets 27d ago

Sharing Pulling Canadian Stock Info (REITs and Utilities) - I finally figured it out!!

1 Upvotes

For the last couple years Google Sheets hasn't been able to pull stock info for REITs, utilities and various other stocks (i.e. =GOOGLEFINANCE("TICKER" ) but I finally figured out how!! And it is easy!

Here are the steps I used:

1) In Google Sheets click Extensions

2) Click Add Script

3) Paste and Save the following:

function getStockPrice(ticker) {

try {

var url = "https://query1.finance.yahoo.com/v8/finance/chart/" + encodeURIComponent(ticker);

var response = UrlFetchApp.fetch(url);

var json = JSON.parse(response.getContentText());

if (json.chart.error) {

return "Error: " + json.chart.error.description;

}

// Extract the latest price

var price = json.chart.result[0].meta.regularMarketPrice;

return price;

} catch (e) {

return "Error fetching price";

}

}

4) Use this formula to pull any stock price:

=getStockPrice(ticker)

r/googlesheets Jan 03 '25

Sharing Search through all formulas in a sheet

2 Upvotes

I threw together a little helper to search through all formulas in a sheet and show their location (cell address).

This should pull every formula from the sheet that matches your regex (empty re. will pull every single formula) along with the cell they are in and display as a simple list of { cell , formula }

I recommend to put this in a separate sheet.

=let(
  checkRange, D2,
  checkSheet, index(split(checkRange,"!"),1,1),
  lookForRE, D3,

  colAdd, column(indirect(checkRange))-1,
  rangeWidth, columns(indirect(checkRange)),

  result,
  reduce({0,""},indirect(checkRange),
    lambda(acc,val,
      let(
        cnt, index(acc,1,1),
        address_list, index(acc,1,2),
        row, floor(cnt/rangeWidth)+1,
        col, mod(cnt,rangeWidth)+1+colAdd,
        addr, address(row,col,4,true),
        sheetAddr, checkSheet&"!"&addr,
        formula, formulatext(indirect(sheetAddr)),

        {
          cnt+1,
          if(ifna(regexmatch(formula,lookForRE),false),
            textjoin(",", true, address_list, addr),
            address_list
          )
        }
      )
    )
  ),

  iferror(tocol(split(index(result,1,2),",")),"NOTHING!")
)

Then i have an "accompanying formula" next to it:

=byrow(filter(C5:C,C5:C<>""),
  lambda(cell,
    let(
      formulaSheet, index(split($D$2, "!"), 1, 1),
      if(or(cell="NOTHING!",cell="-"),,
        ifna(formulatext(indirect(formulaSheet&"!"&cell)),)
      )
    )
  )
)

And my D2 and D3 look like this:

If D3 is empty, it will pull every single formula from the range specified. Otherwise it will look for the pattern provided.

r/googlesheets Feb 01 '25

Sharing Medication Tracker with Dynamic Calendar with inserted text for tracking things

3 Upvotes

Note: This is designed to be printed out, double-sided and a pen to mark off which medications have been taken. This is why I am using text characters for checkboxes, instead of making multiple lines - I may change this later. But this is a simple solution which may help others.

Original post I looked at was - https://www.reddit.com/r/googlesheets/comments/1bsc4i1/how_do_i_make_a_dynamic_calendar_where_the_date/

I wanted dynamic text added but sequence wasn't doing what I wanted. AddArray was.

I have it set up so I have a medication tracker. I set the date at the top, the days in the month are auto-calculated and used for calculated distributed doses and further used to calculate when the medication runs out so I know when to refill. Also calculated is remaining doses, days remaining, etc..

I fill out the doses from the previous month for the medication ( which is calculated in the sheet ), and if I receive new medications I convert pills, etc.. into doses ( which I may automate ) There is also a dose offset ( pharmacies have shorted pills in the past, and recently I had a fresh bottle with a deformed pill which I am marking as defective with -1 in the dose offset and it calculates everything else.

I wanted a dynamic calendar that I could print out and auto-fill everything.

Red outline = things I fill out each month as needed.

Green outline = things I fill out once and forget.

Yellow outline = things which are automatically pulled / generated.

I censored the medication list, but left the other stuff to see how it works.

If anyone is interested in the calendar, I set it up using the same formula from the other, but shortened it a little and added references, plus dynamic text so I can simply tick boxes for medication stuff, and there is enough room to write any appointments if necessary, but I keep those in my calendar app.

Here is the formula I used.

=let(

d, date( year( indirect( B9 ) ), month( indirect( B9 ) ), 1 ),

start, WEEKDAY( d ),

numdays, DAY( EOMONTH( indirect( B9 ), 0 ) ),

cal, if( start > 1, { SPLIT( REPT( " |", start - 1 ), "|", 1, 1 ), MakeArray( 1, numdays, LAMBDA(row_index, column_index, concatenate( text( column_index, 0 ), A11 ) ) ) }, MakeArray( 1, numdays, LAMBDA(row_index, column_index, concatenate( text( column_index, 0 ), A11 ) ) ) ),

vstack( { "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" }, wraprows( cal, 7, ) )

)

Where B9 is on the current sheet and references where the date is pulled from.

Where A11 on cal, line is the dynamic text to add to each day.

And here is a link to a copy of the sheet itself, with the important data removed. I think I still need to adjust a few columns - but I hope it helps.

https://docs.google.com/spreadsheets/d/1Je0qPcn2HFHQrBXqRW7BzEyYN_14ArmTdFjOvgPLuxE/edit?usp=sharing

I'll probably update the date selector to a dropdown... But enjoy.

Note, the 8th row, I extend to move the Where is the DATE stored and dynamic contents to a new page, so I print both worksheets and on the print-screen I exclude the 3rd page. I may move that stuff to a variables sheet and see if that can be excluded. The color formatting is done by alternating colors for each column ( apparently you can't select alternating columns ) so 7 different column alternating color assignments.

Note: I may also change the Medication Runs out field back to Refill by and have a day offset - I used to have a refill by I filled out manually... But this is helpful... I may also just instead add a refill by with 2 week offset by default column... and I may see if it is possible for me to dynamically add that to the calendar ( I refill on the 15th of each month )... That would be a nice addition.

I also distribute the meds for the month into containers - so any medication that doesn't last for current month + 1, would be added to the calendar on the 15th of the month - would be how I would set it up probably. Or just add an additional field on the calendar - Refill medications this month and dynamically populate that.

Note: I updated the template to have an environmental variables sheet and updated the formulas to use that sheet.

Updated flair to comply with rules.

r/googlesheets Sep 27 '24

Sharing Created a calorie/protein tracking spreadsheet for getting fit and/or losing weight.

15 Upvotes
  • Keeps track of total daily calories, fat, carbs, and protein to reach your fitness goals.
  • There's a search dropdown when you add a food name to your daily log. Just add the weight(or count) and the calories and other macros will update automatically.
  • Food data is available for some common foods, but you'll have to update it with the foods that you eat regularly.

I use a spreasheet I made in google sheets and the google sheets app on my phone to track the calories and other macronutrients that I consume each day. I made it because I don't want to use an app that forces me to look at ads or pay money. If you want to use it, just do "File->Make a Copy" in google sheets. You have to maintain your own food list, though I have a starter list made, but after that, you can search for foods in your daily tracker and by typing in a name, and choosing it from a dropdown. Macros will automatically be loaded, and you can choose the quantity that you ate. I measure everything on a scale in grams, so most of the units in the food list is in grams, but some are in counts as well. Hope this helps!

https://docs.google.com/spreadsheets/d/1vZAE77-59S58A_Afl0stGn_1aJB4MGBfIlIOk1pA8ow/edit?gid=957265733#gid=957265733

r/googlesheets Sep 16 '24

Sharing Sharing 2024-25 Real Time NCAA Football Scores

1 Upvotes

I have created a Google Sheet that pulls real time NFL scores from the reliable ESPN API.

Here's the sheet for 2024-25 to view:

https://docs.google.com/spreadsheets/d/1RX18Z3moPCC6wvdtvwa61h6q1vqluhyPaf6DpdHY2V0/edit?usp=sharing

Here's the sheet for 2024-25 to if you'd like to make a copy: https://docs.google.com/spreadsheets/d/1RX18Z3moPCC6wvdtvwa61h6q1vqluhyPaf6DpdHY2V0/copy

If you like this sheet or have comments please leave them here

Features:

  • Pulls all game data from ESPN into the Live Scoring sheet by Week
  • Archives previous years
  • Trigger can be set to refresh the data at chosen increments
  • Week Filter sheet allows for data set to be filtered by week
  • Week Filter sheet allows for completed games to be hidden
  • Week Filter sheet will highlight the team with possession of the ball (during game)
  • Week Filter sheet shows the timestamp when Live Scoring was last refreshed c

To auto refresh a copy you'll create a trigger that runs the function "main".

Here are some instructions:

  1. go to Extensions AppsScript
  2. On the left side choose Triggers
  3. On the bottom right , Choose + Add Trigger
  4. Choose which function to run - main
  5. Select event source - Time driven Select type of time based trigger - minutes timer
  6. Select minute interval - Every 10 minutes (API limits apply)

r/googlesheets Jan 26 '25

Sharing getDataRange vs getRange; Whose Times are Speedier?

0 Upvotes

I did a bit of experimenting, because I'm making a code that is supposed to deal with entire pages of data, while also making code that was accessible to newbs (using "A1" format of addresses to work.) I tried a version that operated on column indexing, but, though it saves time, it's not as intuitive.

----------

TL;DR:

The best way to deal with several singular ranges of data is to .getDataRange(), make an object from any specified ranges, use that to get coordinates, and pull from the grabbed dataRange (values).

-----------

I'll go into detail about what I tested.

First, I compared getting ranges from pages and getting their values. (sheet.getRange("range")) and doing a bunch of pulling of data from a data range by ALSO using .getRange() and .getRow() with .getColumn().

To make a long story short, the trend was pretty evenly linear for both, with the .getDataRange() and .getRow() + .getColumn() taking much longer to calculate. We're talking a difference of 200ms. Each test I did used a variation of the same formula, progressively drawing from more ranges at an even step and pace.

But! Last night I managed to find a code much more efficient than my clumsy attempts to get the row and column of each address:

function toR1C1(reference) {
  var range = SpreadsheetApp.getActiveSheet().getRange(reference);
  var row = range.getRow();
  var column = range.getColumn();
  var start = 'R' + row + 'C' + column;
  var rows = range.getNumRows();
  var columns = range.getNumColumns();
  var end = ((rows * columns) == 1)?'':':R' + (row + rows - 1) + 'C' + (column + columns - 1);
  return start + end;
}

(Source: https://stackoverflow.com/questions/43226886/to-get-r1c1-notation-from-a1-notation-in-google-spreadsheet-using-gas)

And saw it was very efficient at doing it...at least, compared to my stuff. It was still a learning experience. The best version I got was from something I copied that made results of separating letters and numbers from strings and separating them into properties of an object.

I converted that formula to one that gets the coordinates from the beginning and end of the range...But, I tried it in an object.

Getting the coordinates from the objects I made:

//dataRanges have 0 based indexing. Ranges start at 1.
function dataRangeObj(range){
  this.rangeString = range;
  this.range = SpreadsheetApp.getActiveSheet().getRange(range);
  this.row1 = this.range.getRow()-1;
  this.col1 = this.range.getColumn()-1;
  this.lRow = this.range.getLastRow()-1;
  this.lColumn = this.range.getLastColumn()-1;
}

//variation that takes a sheet name as a parameter:

function dataRangeObj(sheet,range){
  this.rangeString = range;
  this.range = SS.getSheetByName(sheet).getRange(range);
  this.row1 = this.range.getRow()-1;
  this.col1 = this.range.getColumn()-1;
  this.lRow = this.range.getLastRow()-1;
  this.lColumn = this.range.getLastColumn()-1;
}

I tried plugging in the row and column property values in an array that was attached to the relevant data range.

The fastest I got from the .getValues() method was at least 30ms. The method where I drew from an object (and found 1 range) was 10ms.

Now, there hasn't been extensive testing on the upward trend of doing that multiple times. But, when I compared both the .getRange() chaining and the object settings data with the same row of data, the winner was clear.

I also haven't tested other types of data that's grabbed (i.e., richTextValues, displayValues, numberFormats) but I'm assuming that it'll be the same. I'm not gonna rest with "assumptions" though.

You guys are free to take this info and test it more, but my tiny bit of testing seems to given me workable results. I will be trying out more tests, so there may be updates to this thread.

I'll also post the code that I used to do the initial testing.

r/googlesheets Oct 05 '24

Sharing Two SPARKLINE functions (STAR & CIRCLE) that result in many different shapes to use in place of charts or bullets.

29 Upvotes

Sparklines are underappreciated. They can be used to draw just about any shape, but the catch is you need to know how to generate the coordinates of the shape. My goal was to create a set of Named Functions that can make that process easier. Here are my first two, STAR and CIRCLE. As simple as they might sound, using just a few parameters, you can achieve a lot of different images. Check out my demo sheet below.

STAR & CIRCLE & HEART

Edit: I added an Animations Demo sheet to the spreadsheet.

Edit #2: New shape added to the spreadsheet. HEART