Utilize multiple sheets within 1 Google sheet?

dwalters

I’m currently making in-show graphics for a minor league baseball team and using one sheet for every game, but I have nested sheets for the roster and another one that will be updated with the batting order for each game.
Is there a way to use these separate sheets that reside in one sheet? Or is there a better, more practical way to handle something like this that I’ve not thought of?

This seems confusing typed out, so if it doesn’t make sense I can do screenshots or something to help explain.

Jon

@dwalters You can use spreadsheet formulas to access data in other worksheets within a “master” worksheet. Just be aware that if you are using the Bot to process your jobs, a quirk of the Google Sheets API makes it so that Templater must “flatten” the formulas in any row that it processes. If you are using the Render/Replicate to process jobs, you don’t need to worry about that particular issue.

ariestav

@dwalters Definitely watch out for the formulas in Sheets when using Bot. Also, you can use a local JSON formatted file to avoid that issue and to gain some flexibility in how your data is structured. In a JSON file, each “object” can have different properties so you’re not locked down to a two-dimensional data structure like an array. You can use the aep property to have Templater load the correct project file for that asset. So the JSON source might look something like:

[
  {
      "aep" : "C:\\templates\\summary.aep"
    , "score": "43"
    , "time": "1 hour"
  },
  {
    "aep": "C:\\templates\\team-stats.aep"
   ,"player-count": "22"
   ,"team-name": "Longhorns"
   ,"city": "Austin, Texas"
  }
]

So you can see there are two “jobs” in the data souce, but from job-to-job there are different properties. Google Sheets is easy, but you do gain some flexibility using JSON.

dwalters

@ariestav So, in your example, I’m not quite sure how I could set that up. Are you pointing to specific comps within a project, or different projects? I’m trying to utilize different pieces of information within the same project, but that may just not be possible in a clean, easy way for outsiders to easily update information I suppose.

I’ll give you a working idea of my particular implementation:

I currently have a “scoring box” in the top left. It lists the team name, current score, the inning, what bases are loaded (which is being driven by manual checkboxes) and how many outs there are (which is also being driven manually by checkboxes)

Then in the top right I have a “Due Up” box which simply lists the player and their respective number.

I also have another info box that swaps spots with the “Due Up” box I call an “Info Box” which houses the current batter along with the next 3 batters and the numbers for ‘Runs’, ‘Homeruns’ and ‘Errors’.

Right now I have a controller setup using mostly if/else statements and dropdown menus to control what boxes are being shown.

So, I guess my question is, is there a better way to do this? I would sort of like to keep this within Google sheets just because it’s an easy to understand way for the team to update the numbers, but if there’s a better way, I’m all for it.

Thanks for your help thus far, you’re very kind and I appreciate that.

Jon

@dwalters You can select individual comps within a project to re-version with the target property, or you can switch between projects with the aep property (and you can combine the two). Templater will parse through the entire project, not just the target comp, so you can set up really complex, modular systems that all update at once. You can use data from your data source to drive you controller/checkbox layouts.

dwalters

@Jon Very nice, are there any examples of this anywhere? I’m not really sure where to even start to accomplish something like this. However, it sounds amazing, and I’m definitely going to be diving deeper into this.

Jon

@dwalters There are videos demonstrating setting a target as well as switching between project files. For data in expressions, see the link in my previous post under the section “referencing dynamic text layers.”