CSG Power Ranking GoogleSheets 2023

Update Nov 15, 2023

Yahoo sheet:

  • they changed some xml references again which was causing issues importing team rosters.

Update Oct 25, 2023

ESPN Sheet:

  • some api changes I think led to errors importing names, should be fixed.

Update Oct 11, 2023

Sleeper Sheet:

  • sheet imported data in a different order so fixed some formulas

Update Sep 19, 2023

Yahoo sheet:

  • I forgot to drag the updated formulas across when updating the matchup imports so the week 2 scores and onward won't import - re-download for a fix.

ESPN Sheet:

  • OVW was getting errors because of empty teams being imported incorrectly from ESPN.

Making a new post for 2023, but the ESPN and possibly Sleeper Power Ranking sheets you downloaded from last year should still work for this year. For ESPN you just need to update the year to 2023 and for Sleeper you'll need to update the leagueID and the year.

  • For Yahoo I had to make some changes as many of you know they revamped their website so some of the code getting imported needed to be altered to grab the correct data this year, so you'll need to re-download that sheet. Let me know if you run into any problems with Yahoo as I only had 2 leagues to test with.

If you have issues try making a new copy here and let me know if that doesn't fix it. I did minimal testing but all seemed to be in order.

What it does:

  • creates a nice looking Power Ranking table based on /u/GATOR7862 's spreadsheet that can be screenshotted and shared with your league Picture
  • Supports up to 20 teams

Did up 4 versions, one is all manual entry and the other imports almost all the data from your ESPN/Yahoo/Sleeper league.

ESPN/Yahoo/Sleeper Version

  • First you'll have to make sure your league is publicly viewable (check your league settings) or this won't work (ESPN/Yahoo Only)
  • Next enter your league ID and the year and hit the Update ESPN Data button Screenshot
  • you will need to grant access to the script, if you want to dig into it before doing so go to Tools > Script editor, the code for the ESPN button is in the ImportData.gs, the code in the Code.gs file is for using ImportJson to get the league data from the ESPN api into the sheet
  • click allow > choose your account > Advanced > Go to JSON import (unsafe) > Allow
  • This script allows me to pull in API/JSON data from the sites and I also use a script to copy the imported data and paste it into a new sheet because google sheets sometimes runs into errors if you continually run an importxml, importrange etc. to grab data from webpages.
  • After about 10 seconds the import should finish and your data should be pulled in
  • go to the PowerCalculations tab to update modifiers and check on the data Screenshot
  • The sheet pulls in all rosters and organizes them in a tab that calculates roster scores for each team based on FantasyPro's ROS rankings (similar to the MyPlaybook Power Ranking) Screenshot
    • Then all you have to do is copy and paste the calculated power rankings into the weekly power ranking table Screenshot
  • Each week you just go back the PowerRankInput page, update the week, then click the Update ESPN Data button and paste in the power ranks.

Make a Copy of the ESPN Version

Power Ranking GoogleSheet 2023 (ESPN)

ESPN IDP Version 2023

  • was unable to fully test the IDP version this year, if you have issues let me know/ provide me with your league ID

Yahoo Version

Make a Copy of the Yahoo! Version

Power Ranking GoogleSheet 2023 (Yahoo)

  • let me know if you run into any issues, I was only able to test this with one league.

Sleeper Version

Make a Copy of the Sleeper Version

Power Ranking GoogleSheet 2023 (Sleeper)

Manual Entry Version

  • Enter in teams, points scored each week, wins etc. in the power rank calculation page. Screenshot
  • add links to pictures to use for each team Screenshot
  • enter in the power rankings each week to keep track of which teams are moving up and down Screenshot (without the ESPN button)
  • you do not need to enter in all this data for the sheet to work, it's just there to use if you want.

Make a Copy of the Manual Entry Version

Power Ranking GoogleSheet 2023 (Manual)

Additional Details

  • The majority of the power rank calculations are from /u/GATOR7862 and his spreadsheets
  • FantasyPro's ECR data is pulled in from https://www.fantasypros.com/nfl/rankings/ros-overall.php (the sheet detects if your league is standard, 0.5PPR or 1.0PPR and pulls in the appropriate rankings for ESPN only)
    • turning these ranks into values I used a formula developed by the guys at DynastyProcess.com (https://dynastyprocess.com/values) to create dynasty values for players. That formula is: Value = 10500 * eFP ECR*-0.0235

  • Data pulled in from ESPN includes: Team Names, Owner names, Team Pictures, weekly matchup data, team rosters

  • Everything else should be explained in the screenshots above Imgur album here