Using Google Sheets with VMIX – Part 1

hello everyone welcome back to streaming alchemy i'm john mahoney and on today's show we're going to look at how you can leverage data stored in google sheets into your vmix productions but before we get there i'd like to take this opportunity to invite everyone please if you have any questions have any feedback become part of the conversation just drop it in the comments below and you know we'd love to have you uh you know engage with the show also if you'd like to join us here live on air uh please you can contact us at the link in the lower third it's also in the show notes and somebody from the team will get you on air with me and you know we can discuss a topic or answer questions directly so we'd love to have you be part of the show all right so let's dig in so one of the things that i wanted to capture here is that you have the ability to leverage lots of external data sources inside of vmix and that's one of the like super powers in vmix uh one of many actually so this is something that uh i think we're gonna go through in in much more detail over different shows but for this show what i wanted to look at was how you could connect data stored in google sheets into vmix and there's two reasons first we've covered xml integration into vmix using you know data sources manager but what makes google sheets different is that this is across the web and can be done remotely so you can have one person making entries in google sheets and somebody in a total totally different location leveraging that data in their vmix production so to get started what i first want to do is we'll switch over to vmix here on the screen and let's go into gt title designer because this is where we have everything set up so we just click on this little hamburger menu down here and go to gt title designer i have it open here already so we'll just slide that in here and this is the template inside of vmix that we're going to be using for this actually before i jump in here let me just capture everybody that's popped in on the messages so we have samuel from norway samuel thank you very much for joining us here uh we have richard from northern california richard always great to see you and uh we also the saint peter from berlin peter thanks for joining us here again we have uh james from arkansas james thank you and uh okay peter just mentioned that uh exo google sheets as data sources is great uh as with uh as with working with some people so uh yeah i mean i think when you have this when you look at how this setup works there's a lot of different workflow things i think you can design around it so that's uh that's great thank you everybody for uh popping in to say hi so let's get back to the gt title designer template we have here so this is a very straightforward template what we have is a background image we have another image that we're going to be using for putting the panelist faces there and then we have layers with text so each one of these is is named so as you look at different elements here you can see when i click on their name on the side they are all highlighted so this allows me now to take elements that are inside gt title designer inside this title that we set up and map data to them because we're going to be using these names as a way to link the data with the specific elements in this title so that was the real reason for diving in here and you know we can do gt title designer in more detail there's a lot of stuff in that on its own but we'll do that in another show so let me slide this off so when you want to take and bind data to a title you go into vmix's data sources manager and that is again going to this little hamburger menu in the lower right very top is data sources manager and so let me see if we click here i want to add something new so we're going to go and do google sheets and this is exactly what you do when you want to add a new data source you just go in click the plus and take the type of data source you want to work with so we're going to give this a pretty specific name because we want to be able to use this so we're going to call this event panel so so that's the name now what it's showing us is that we need two other pieces of information we need a url and we need a google api key so let's start with the easy one first uh and that is we'll open up a google sheet and get the url for that so let me just slide this over here uh so we'll go to google sheets which is just sheets and it will get you into this about page it's typically going to be for most people a personal account so you just say go to google sheets and when you go there it actually gives you a home page similar to what you'd see in excel where it shows all of your current sheets that you have available oh excuse me and or you can create a brand new one just by clicking on the blank we have a sheet already set up so we're going to click on that and open that and it will open up here in google sheets so the way we have this sheet set up is the top row earth will typically be the names of the items that you have in your sheet so we have panelist name we have panelist title we have an organization we included a number in case at some point with scripting or something we might want to do something there and then what we're also doing i don't have a label on top of this because we want to be able to show something a little bit later we have the url of the image that goes with that panelist so this can be lots of different things but for this case we're just using panelist images and we have those stored as urls so this gives us a lot of information that we can use in lower thirds so to if you remember in vmix it said let's get a url of the sheet and the way you do that is way after you've opened your google sheet you click on this share button this green button in the upper right hand corner that will load up this page and what you want to do is you want to say i want to get a link and there's two pieces to this click on this get link box here and it spreads it out a little bit so this is the link that vmix is looking for so we'll just go and copy that link and that link will come up and say it's copied but the other thing you're going to need to do is make sure that you have anyone with this link can access it this will just i mean you may be able to do this in a more secure way but i think for right now this will get you fully connected and somebody actually has to have this link in order to connect so as long as you're not publishing this link you should be okay so with this done we'll close this down and let me slide this out of the way and we now have the first piece of what we need here which is a link of the google sheet so the next thing we need is the google api key and this one is a bit more complicated so i'm going to go through this in detail but you know we'll we'll also post sort of the notes for what you need uh after this so that you'll have this sort of as a checklist you can use to get yourself set up so let's drag this back and we'll click on another tab here and we'll go to google dev so what this is it's uh i don't have it down somewhere but it's really just console.developers.google.com and so uh we'll post that url later as well but this gets you into was basically the developer dashboard and so we're going to need to do a couple of things so the first thing that we're going to need to do is we're going to need to go and create a new project so everything that works with google's api set works around projects so if you see here we have this button on the right hand side called create a project and so this will go and open up a new project and you can give it a name so let's do something that will be meaningful so since this is streaming alchemy we have google sheets demo it's already set here and that will be the name of the project we have set up here so we'll go and click create and it takes a little bit of time to get that through okay so now that we have this here just we have a new project created and you can see now that the project that we're in is google sheets demo so the next thing we can do is we can go to the library and what this will let us do is select okay we set up a project so what is the google technology platform we're going to be talking to what api set are they going to be using so for this it's going to be sheets so we'll just type sheets in there hit enter and it's going to come back and say okay this will be google sheets so with this set we know this is what we want so we're going to enable it okay so takes its own time here so let me just jump back here a couple and what we're going to do now that we have this project set up and that we've assigned google sheets to this project is we're going to go into credentials so this is where we're actually going to get the api key so when you go into here you'll remember where it says right up here we're going to create credentials and the first thing in the list of credentials we can create is an api key so we'll get the api key and you can see here that api key has been published so we're going to copy that so it's copied to the keyboard uh to the clipboard and you know we could do other things we could rename it or whatever but this is actually the second piece to what we need to do so with this api key in hand let me go over to this and we'll paste this in and we should be ready so now with all that information in here we have now connected to our google sheets so now with this connection mate let me jump up and we'll go back to our uh comment section and you know connect with everybody here so who else we have uh david uh ramirez has joined us uh from costa rica david great to see you again uh cesai has joined us hello cesai thank you uh so uh uh the zion christian church uh so they have joined us and they actually had a question uh so they said they uh were using google sheets for translation uh this resulted in higher render times and frames would drop yeah i'm not sure what would cause that uh in particular uh because this shouldn't be something that you'd have to deal with with bandwidth and especially the fact i mean google sheets is very low bandwidth uh there are limitations on uh how many queries you can make so there may have been something with that that was causing some networking delays but i i don't have a specific answer but would definitely uh love to talk to you maybe in the after show uh if other people have ideas we could dig into that uh uh so we also have uh dave christensen from oklahoma dave thank you and i'm told that we have a caller so uh please hello how are you i'm fine how are you very good so who's this i just wanna oh that's uh peter from germany yes sorry i s hard to take the little icon that you see in the corner translated in person so sorry about that peter so don't worry that's fine so thank you yeah um yeah i also use a lot of uh google sheets and uh and excel sheets uh it depends uh on what i'm doing um i have one show it's um it's like a dj show with venue records and i have a sheet for all the records where we have all the titles and all the the covers of the of the records and um i fire them out if the record pop up and then if they yeah that's uh i have to always to because this is um the feeling of the djs they all um they don't want to work on a list so i have a i have a list with about 400 uh records in it and i have to to look what what record it is and then i have to choose this uh item i have on my stream deck i have a lot of shortcuts for this and and then i have a notebook where i search for the title and then i fire it up and then we can we can bring it in and on the other hand i have um i'm working uh from your university and we have every half a year we have um it's like in this covet thing we have a we have a show like um introducing every course and we have we have about uh 20 different departments who all have some courses and they um they say they show all their courses and um what i do is i have a google sheet where everyone can put their names and their subjects and everything inside and then i have a production sheet where everybody who's in the studio um i can see what is next and uh what is the subject and what is to do i also put in this table um for for my info screen and we have it also on a big screen for all the partitions um i put everything what what i have to do for the next show what i have to prepare like um uh what is uh the next topic what uh is there a move is a movie or something what what i have to to bring into and uh or is it a caller i have also um all the callers uh named there with with their numbers so um if i have a little team then first uh some can invite uh the guests where we have a remix just eight callers and uh if we have over 60 people uh speaking on at one day uh we have always to to change and uh to bring out the numbers you wouldn't want to be doing that you wouldn't want to be doing all those lower thirds in photoshop and everything that would that would be brutal so but the yeah i mean i think having data in a way that you can start to integrate it into productions i mean we talk about lower thirds a lot but it does fit with so many different frameworks you know graphical uh it can it can be things like you mentioned that you're also using excel so excel has sort of macros and good search capability so you could almost build something where if you typed an artist or the name of the show you could see a list you could select somebody on that list hit a button and that could get written to a column which would update everything in vmix so you you have these simpler ways to access it you don't necessarily scroll through four or 500 you know names but what i actually do is um i have for all the covers i have also the the pictures as a as a file name inserted so it's always just a number but i um i uh just string cut it in excel so i have the full link and what i also do is um i have in every row i have a link for a shortcut for vmix uh the web web shortcut so if i find so as a title then i click on the link and then a web browser opens and it fires up as a shortcut to vmix that's how i do it so i don't have to search for a number or something so if i have the round the right column i know the right row then i just uh push the link excellent excellent so peter thank you very much for sharing that uh this is great because i i think the whole point is getting people to be motivated to to try to push into that next area and this there's a lot of stuff around data that production automation and data go hand in hand and i think as you start to do more of one you'll do more of the other because it's just how it how it optimizes so peter thank you very much for joining us yeah thank you very much have a nice day you too take care thank you bye okay so let's see uh we before i jump back uh we have uh mike uh made a comment as well he said when i stream to client websites i have someone copy and paste audience comments and questions from a chat box below the video into google sheets so yes so another great use case where you're just taking this out and making it available into vmix so thanks for sharing that mike and peter thank you for giving us a call always always great to have people call us so let's get back to uh starting to tie in this data that we just linked into the uh lower thirds that we have set up here so if we switch over here uh you can see now that i have event panel set up you can see that it has all the data that we pulled in and you'll also notice that there are two sheets inside this google sheet two sort of workbook pages so you could pick either one of them so it's pulling the data from both of these worksheets so that's something to keep in mind and we're updating it every five seconds so there is a limit to how many times you can query uh google for refreshes and that typically is i think they say a hundred queries every 100 seconds uh so if you exceed that then they'll throttle you and you won't be able to uh to do queries uh you know for some period of time until you are freed up again so something to keep in mind also keep in mind that every one of the worksheets so we have two here that's going to be doing two separate queries one for each worksheet so that means that we're doing two queries every five seconds which sort of falls under uh 100 every 100 seconds so i know we should be good but just keep that in mind as you start to think about how to do this and if you up this number you could run out of queries pretty quickly so also keep that in mind so how do we actually link this all together so let me close this up now that we've seen the data come in and we're going to take a look at the lower third panel that i have here this is just the gt title designer and what you do is you right click on this and you'll see there's a tool called title editor so this is not gt title designer what this will let you do is it will let you map basically edit the data in it but also map it to data sources so this is this is where you go if you wanted to put different people's names in already that you had preset you can do all that down here we're going to be focusing instead on data sources so first one we'll put in is name so we go up here and let's see for data sources we want to go to events panel it's the sheet that we're using was panel one and the column we're going to go to will be the second column which should be the name and that you can see here is the panelist name so we say okay now we'll go to the title so we want to get that in so the again the same thing data sources event panel panel one the column is going to be column three which you'll see is panelist title and we'll say okay to that we're going to take the organization text so we'll go here all the same the organization text is the fourth column and then what we want to do is we want to also have a panelist image so we'll go here go to event panel go to the fifth column and the point the reason i didn't just put a title here is we're using a url to do this so that means that we can have these images stored out on the web as well and this means that everything we're doing with pulling in data is coming from a remote source and that makes this really powerful you could have somebody from an organization uh upload all their images to a location you could pull those in you could actually have somebody else outside of the production space handling all this for you as well that's kind of uh a bit what mike was talking about before when he has to deal with people working with sheets external from him so with this in place what i've now done is i've mapped all of the data sources we have over to our vmix title so if i were to just call this title up so just see here you now see it just has the default names in here so what would we have to do in vmix there is there is a series of shortcuts that are dealing with data so let me go over to the settings over here and we're going to open shortcuts and so i've set up a whole bunch of them here but uh let's just take a look the way i'm going to be doing this is we're going to be using a function so let me just edit this we're going to be uh using a function that works around uh setting up all of the uh picking basically picking the row inside the data source that we want to leverage so let me do this here so this is called data source select row and there's a whole set of data source function or shortcuts inside of vmix so data source select row says which row in the data source do i want to use and that is just the rows inside my spreadsheet so i can pick one out specifically that i want but i could also do things where i go to the next and the previous or i can do something as more of a playlist where i just want to run through them so right now we're going to be doing something called data source data source uh select row which gives me lets me pick the row so with this in place we actually are using a dynamic variable for this so whatever arguments we place in dynamic value one that's going to be what the row that we're going to select and the way you select a row let me sort of jump back here and go back to the person i originally opened here is you have to say what's my data source if you remember our data source was event panels so that's when we set this up inside of the data source manage manager we chose event panels for our google sheets data source we then pick the workbook inside of that data source and so we picked panel 1 which is our first workbook and then i'm saying i want to have row 1 in here so this will with this set up i now can when i select any one of these buttons that will set up the dynamic variable and then when i tell it to call up that basically set that into my as my selected row you will end up seeing that on screen so we have these set up using a stream deck so let's actually just take a look at the stream deck too so over here on the stream deck let me get this up just hold on one second so okay so over here on the stream deck i maybe pull that out a little bit here uh well we'll we'll make it work so i have a set of buttons over here from one to four and then a little bit a little button on the top that says select source so if i just i'll push button one so what this is doing is it's setting the dynamic variable to say i want to select row one and then when i say select source i am now pulling the data in from the dynamic variable here so the uh this is giving me the image and the name the title and the company so that's all the information that we have here for this dynamic source and if i were to pick the second one and do the same sequence i now can just call these up one at a time and uh you will now get very simply i can now based on picking a number pick which one of my panelists i want and using that dynamic value push that out on the screen so that's one thing so let me pop this off the other option that i have here is to uh actually put up a lower third then just tab through the different uh shots that we have in this so if i were to do that let me just go here and we'll say we want to pick start with the first guest so let's see jump back here oh so what this will let me do is it will let me pick a guest and then it will come on and the guests will come off when i press it the next time what i end up with now is a uh auto forward to the next row so each one of these things that i'm pressing here let me see if i can slide this over let me make it a little easier so each time i'm pressing this it calls up the next person in the row so it allows me to do these types of things where you're going to be introducing people and i can just that i'm just using data source select next at the end of this and that's a little script that i'll jump into the other thing that we can do is i can just call this up and i have here at the bottom select previous and select next so i can just move through each of these which gives me a very simple way to sort of bounce through each one of the guests and where this could be useful let me just if i take this as a and move over to put my see my title in my preview i can now pull this down will take the display off i can go ahead and select the person that i want and then pull them up so this gives me a very simple way to sort of preview what i'm doing i want to pick no that was the wrong one i want to pick that one no i want to pick the first person and then you just go and say okay now i put this up so this gives us an easy way to do uh this type of selection with the data sets that we've already pulled in so uh let me as sort of as a as the last thing here let me actually just go and show you a little bit of the script that we pulled together for this so first thing is the way i have this set up is in shortcuts if you look right here where i have display plus next this is a script start dynamic and what that is it's basically a shortcut that you can then put text in for doing any type of scripting that you want so i actually have that script right over here so all it's doing and it's it's a fairly simple script so all that we're doing here is we are using the api function call to turn off overlay input one uh uh overlay uh you know which is basically overlay one on input one which is where we have our title so let's make sure that we shut that off and that should take care of that then the next thing we're doing is we're sleeping for a quarter of a second that just provides a little pacing in here so we should be good with that and then what we do is we put the lower third on sleep for five seconds which basically lets that overlay stay up the lower third stay up for five seconds we then uh after that five seconds is up we then pull that overlay down and then the last thing we do we wait a quarter second again and the last thing we do here is we are going to the next data source in the row you know the next row in the spreadsheet which is the next item the next guest that we want to have so i have the api function with uh next data source next row and i'm telling it the same things we basically had in our dynamic value where we're saying it's data sources events panel it's panel number one it's the sheet so it's just it knows what row it's at it's going to the next one so there's next row this previous row but that's pretty much how we're able to do this and it creates a nice little automation for this but uh you know there's a lot more certainly that we can do with uh lower thirds and that's something we're going to be covering in our next show now that we sort of looked at how to connect these uh google sheets up we're going to be looking at some of the cool things that we can do with them here inside of vmix using scripting this time sort of a step beyond the function based scripting here into actual scripting with xml so before we wrap up i just want to check i think we had uh we have one more person has joined a team 4m uh from monster germany uh team 4m thank you for joining us uh i appreciate you taking the time here so that i think covers everything uh i know there was a lot of different you know topics that we we touched on here from working with google to doing things inside of data sources and vmix to a little bit of scripting with function calls so hopefully you know this this got you thinking about some things that you could be doing with google sheets or with data in general but uh that's all for the show so we'd love if you have any questions or want to just hang out and talk it can be about today's show it could be about any streaming or video production topics we'd love to have you stay for the pro show but if you cannot thank you very much uh we'll be back in a few seconds but if you're not going to join us for the post show we'll see you next week thank you for taking the time to join us today take care everyone all right everyone welcome to the po show so uh hopefully uh you found this uh interesting i know this this is topics that we've touched on before but i thought it was really important to look at this with google sheets uh it's there's a lot of external data sources that i think we all could be integrating uh and google sheets is great because it's one that we control so uh we being you know as producers we can set all this up ourselves and have somebody else uh be entering the data for us and have that sort of across the web coordination and uh it's it's really flexible and it's free for you know these types of personal accounts so that's something i think that could be uh useful for everyone the latency in this is actually not bad because that five seconds is kind of the it's polling every five seconds so if you make changes your average change time is going to be somewhere in the middle of that you know somewhere between you know two and four seconds you're probably going to find is from the time an update is made to the time you see it in the studio and i'll be honest that is not bad at all uh this also makes it very easy to have somebody else double check everything so you could actually have a client uh working with a sheet here to you know check all the spelling put pictures up other things like that and that you know when you're dealing with so many different things in a workflow having somebody else outside of the people who are getting the cameras and the audio and everything set up to do switching done uh pushing that off to some other element in the organization or with or the clients you're working with can make things a lot simpler so uh we think that's there are also some interesting things and we when we talked about what we could potentially do for today's show uh we we were thinking a lot about how could you use this to do automation so that may be a future show but we were thinking wouldn't it be cool if i could have somebody remote uh change something trigger a shortcut or a script inside vmix from a remote location uh because you're changing data and you have that ability that could be pretty interesting so that may be something uh we'll take a look at at a future show so mike uh he said great content explanation thank you uh how reliable have you found google sheets to be uh so we don't use i mean because we do everything sort of in a local single location here we don't use them in production i have heard you know because we we had peter f from berlin was on and he uses it all the time in things he does and he seemed you know very uh comfortable with it the functionality inside of google especially google sheets which they really listed as one of their enterprise grade uh apis i i would believe that to be at least as reliable as you know a gmail account a uh you know what you'd have for a corporate gmail account which is what we use as well so definitely uh we're satisfied with that is it 100 i i can't speak to that uh but you know if that were an issue there are probably ways to do things as as backup where you know you could you could actually have you know use it an excel spreadsheet and just export that as uh csv uh from the google sheets into the excel spreadsheet and use that or just use csv files which are also there so there may be some ways to do this which wouldn't be perfect uh but definitely you know we've had no issues like when we followed the steps i sort of laid out here we've had no issues with making sure that connection happens getting the api keys doing the data sources connection in vmix so that's worked really well for us but uh yeah so i mean peter's saying you can change the update time it says sometimes there's a timeout for you know some number of seconds but uh i think that's that probably is is is given that it's a free resource that's probably not a a bad uh a bad option uh one thing to keep in mind and this is this is something that we actually talked about internally so i i figured it'd be good to share we were asking about what if you just wrote something into a shared drive like dropbox or you know onedrive or something like that the one thing to keep in mind with that is that these typically work by having a local cache running on everybody's systems and then centrally there's a component that pulls these caches for changes resolves figuring out who has the latest copy of everything and then propagating any changes out to all of those remote caches so there could be some latency in that and potentially more latency than would be acceptable inside of production so if you're going to be using a shared drive to do these types of things i would definitely check that out make sure the latency is low enough and consistent enough for the types of productions you're going to set up be setting up for so just something to keep in mind when we set these images up we're taking them from urls so those files were written out directly to a sort of a web based space and that url link now is is being pulled in so we're not doing a file system you know mask you know on on each edge this is actually just a link a direct url link to an image on the the internet so that gives us that sort of dependable latency uh and performance so that's something as i said to keep in mind as you think about different ways to work with data remotely but uh so uh what we'd like to do next week because uh you know we realize that there are limitations when we use just the vmix uh shortcuts so what we'd like to do for next week is is actually look at some of the ways that we could script to work with things where you could have multiple panels and pick different panels and have the script keep track of everything and make sure you're pulling the right data from the right places in your uh in your vmix sheets data sources so that's really what we're thinking about doing for next week's show so would would definitely if there's anything specific you'd like or some particular use case you'd like to see us do around that please just share it in the comments or post it in the facebook group but that would be great so peter just also has another point he said uh so there's also a problem if some people make changes in the same file at the same time so uh so peter i'm guessing what you're saying is if you had multiple people writing to that file from different locations that could potentially cause issues as well so uh you know this is one of those things where you know these things aren't uh perfect for the use case we're putting them through but definitely we've we've had a lot of success with the google assets in general you know you know from everything for docs to uh to sheets to gmail and just using things there but mike is saying that he always tells his clients uh it takes about 10 seconds uh so yeah and also hit enter is a very good point if if you just make the change but you don't enter or click to another cell or something that won't get registered so something else thank you for that's a very good point mike uh so thank you for pointing that out the uh you have to make sure you've entered the data and i know that can be tough especially if you're going down like a whole column and you're you're changing something in in a column when you get to that last entry in the column you're sometimes like yep i got to enter and go to the next one even though i'm not doing anything else so uh we've definitely had cases where we do those types of remote updates and one of them doesn't make it uh so definitely something you know to to keep in mind as well for here but uh so if you have any questions uh we'll you know we'll get them in now uh otherwise we'll we'll wrap up the show for this week but uh we uh we want to make sure we we get back to vmix scripting because we know that there was a lot of interest and we want to try to address it at lots of different levels so some things that are more uh foundational and other things that are more advanced so if you have specific topics again just feed them to us in the comments you can always send it via email uh to uh to us that you know john at streamingalchemy.tv you'll get to me uh so different ways to to let us know what you'd love to see here on the show so i guess i haven't heard anything so uh i guess that will wrap it for this week thank you all for joining we as i mentioned uh before we will be at nab this year and we will be live streaming as part of the official nab live stream so we will have an hour show each day sunday monday tuesday so if there are specific topics you'd like for those shows as well please we're uh we're always open to uh the feedback and suggestions here so james says thank you for joining we were i can't tell you how much everybody here really appreciates everybody showing up uh for us on fridays and it is it's always great to have this engagement so thank you all peter thank you uh thank you for calling in and again everybody it's welcome if you feel more comfortable calling in in the after show we have callers throughout the whole thing so please we'd love to have you here thank you everyone see you all next week take care

Channel: The Streaming Alchemy Show

Views: 1,795

Keywords:

Id: Z0FjwzkhkBk

Length: 45min 43sec (2743 seconds)

Published: Fri Sep 03 2021