Using the Importrange function to have a live draft feed into your googlesheet

This post follows on from this initial post – you may wish to read that first, though not essential.

If you have built a googlesheet with your HFFL stats tailored to your liking and you would like a live feed of the drafted players appearing in your sheet, so that you can have them automatically removed from your stats rankings in real-this post explains how.

Note this will only work in googlesheets, not in excel.

Note also you need to give The Emperor a big hug for sharing this with you.

The following example is for the HFFL 2023 draft. In future years you can follow the same steps but will need to use/reference the new HFFL Drafting sheet for that year.

  • Within your own tailored stats googlsheet, create a new sheet called “Drafts”
  • Copy and paste the Draft sheet from HFFL 2023 Drafts into this newly created sheet of your own called “Drafts”
  • You don’t have to copy everything but I’d at least copy columns A-H so you include the column “Player Picked” and their “Club”
  • Then in your sheet called “Drafts” enter the following formula (copy and paste it) into the first cell in the Player Picked column (cell G2)
    • =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1Siahxp6FPRPgJPV8bnWAqjq5fX0muGCUZ55aUIcjgZc/edit#gid=0″,”Draft!G2:G310”)
    • This formula makes your spreadsheet go to the HFFL 2023 Drafts spreadsheet and ask for permission to live feed the data in the Player Picked column into your sheet.
    • If copying and pasting that formula into the cell doesn’t work, then you just need to recreate that formula from scratch. See Google’s Importrange information showing how. It only has 2 parts – the web link of the sheet you are feeding data from and the sheet/range of the cells you are importing.
    • Then you need to approve this request. If you look at the HFFL 2023 Draft sheet it should ask you to approve this request there and as an Editor you should be able to approve the request. You only have to every do this once.
    • From then on every drafted player typed into the Player Picked column will appear on your new Drafts sheet live.
    • If you would like their AFL Club to also appear in your Club column then repeat the above steps and copy and paste this formula but this time into the top cell in the Club column (cell H2)
      • =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1Siahxp6FPRPgJPV8bnWAqjq5fX0muGCUZ55aUIcjgZc/edit#gid=0″,”Draft!H2:H310”)
      • Right, now that you have the live draft data feeds of Players Picked coming into your Stats sheet, you to set up your sheet in a way to use this data to remove players from your stats sheet as they are drafted. To do that copy what I have done in the 2022 Stats – Undrafted sheet.
        • Essentially you will need to insert an “Undrafted” column then add the “Match” and “Count” columns on the very right of the sheet and use those formulas.