Api from your existing project simply select that project click on enable apis and just search for sheets here and then you just need to enable uh the api by just clicking on this button okay for this demonstration i'm gonna just create a new project we'll just call this uh sheets api and for vba okay uh whatever name so it's here you can just go for that okay and then yep this is the new.
Project that uh just got created i'm gonna click there or you can just go back and um go back to dashboard and then from here uh you can select this new project okay so now this project is selected first let's enable the api i'm going to search for sheets and just click on this button this will enable the sheets api for this.
Particular project okay so that's done um if you want to enable more apis you follow similar pattern like for um gmail or calendar and so on and now let me go back uh okay credentials let's set this up first before we can set up the credentials again we'll set up the consent screen this will be the page that the user will see once you they use your application we're gonna go.
For external everyone so that everyone with google account can access this uh use this project and here this will appear in consent screen so i'm just gonna say um sheets api vba demo and then let's just put down logo as well okay so that should be it uh the scope and all are already you know based on the.
And this is going to be your key okay and this is your password or client secret so just keep this confidential and you can now download this um json file which contains the keys okay so here's the file i'm just going to leave it there for now now let's set up the service account as well so click on service account we'll try to use this.
Information we'll just i'll just call this service and this is going to be the email id for this service account and i don't know um okay this works now we'll set up the owner uh the role so just set up the owner for this service account this email id is gonna be the the owner click on continue okay so that's pretty much it if you want to assign more roles you can also do that for now just click on done.
And here once you go into this service account you will be able to add the keys so you scroll down here uh you can upload existing keys if you have or you can use the new key as well so here you can either go for json format or p12 um so let's try and create two keys i'll just use this first okay and then here is the key let's add one more and this is going to be p12 so as soon.
As you create it's going to automatically download it for you okay the password is not a secret okay um so since the program is going to take care of all this uh automatically um you don't have to worry much just save the file in a place where you can access them okay and then um now going back so we have created the keys uh we have set up the service account so let me navigate back back to dashboard.
Okay so this is the one we've created and then um so far we have enabled google sheets api so if you have enabled more apis you'll be able to see them here once you go in here you'll be able to see the stats uh the metrics um the error count number of requests that has been sent all these informations are gonna come in here later on okay so for now let's go um the project is ready we can go and try to use this in vba now um first we'll try to use this demo uh.
- How to Design Cover Page in MS Word for Business Case Report DIY Tutorial Office
- Cara Buat Slide Title PowerPoint dengan Gambar Animasi Bergerak PowerPoint
- Create Your Own Excel Custom Ribbon Tab From Scratch Employee Manager Pt. 21- FINAL Office
Db sheet that i have created and then we'll try to play more with - ID Card Make
New sheets and all this okay so this is a dummy one that i've created here okay now for those of you who have followed the prior ones uh prior video there are some changes that has been done to the methods and some of the functions so i'll just talk about that as well in a bit um okay so once you uh download the the library uh from the video description you can find the link once you download it you'll find these files so you can.Either run the setup or uh this one the msi file if you have installed the prior version please go and uninstall it first and then you run the setup or this one okay so for now i'm gonna run this setup.exe just follow the screen and take a note of this particular path just gonna copy it and put it here.
You have to confirm that and now it's going to be done okay so the program has been installed we can quickly confirm this from so here is a program by publisher vba to set and and so on okay so once we have installed this we can go and check this folder so here are all the dll files this is the file that will link from vba and then this file will connect to you know all these dll files where all these.
Methods and programs are stored to connect to uh google api okay okay so for now going back to that folder um so this is the all where all the sample codes has been placed uh you can just open that and i'll give you some demonstration and then it should be fairly easy to follow this okay so i'm going to go to downloads folder and just cut these three files.
And bring it here let's just call this new folder as credentials paste it here okay so this one is the user and then this is service one and this is service 2 okay now let's try with the user first this key first.
Now when you open up this uh spreadsheet uh go to editor you'll see um all the functions that are available so for uh for google drive there are some changes that i've done um this method account credentials now has been set to start service so you update that accordingly similarly uh there was a typo in this method so i've just renamed the method to uh list files folders and then now google drive has become google apis vba so that we can include more apis maybe.
We'll try to include maps or we can even include gmail calendar and so on okay in the future maybe even youtube and all if uh you guys use that and now coming down here uh these are all the methods and functions and all the pattern parameters that you can pause and what it's gonna return to you okay and then for sheets as well these are you know like to list the name of the sheets uh to append the data to retrieve the data and then it returns the array in two.
Dimensions so we'll just demonstrate that and then this will just return you the last connection information if you're interested to know that now again nothing have changed uh except the ones that i mentioned for google drive so they are still intact you can go ahead and try it again using this new um you know i renamed methods now coming to sheets first let's um set up this information here the credentials so we have this uh user.
Bots i'm gonna place the key file here okay and i put in some information about each of these parameters and then uh this is going to be user this parameter is going to be user and then for the service account let me just grab the email id so i'm just gonna get this service account email id.
And replace this one here okay so as far as the creation source is concerned you just need to set
This uh information uh if you're going to use the service account you specify service here the part will be as of this particular you know like either json or p12 if you use this p12 certificate um then you'll need to specify the service account uh email id as well here otherwise this is uh optional okay.Okay now uh in order to for us to connect to that particular google sheet we have to go and get the google sheet id so i'm just gonna so um the id that comes between these two um backslash after d and before adding okay so i'm just gonna copy this part and then um i'm just gonna put a constant variable here but if you want to use.
Multiple sheets then you can you know replicate this variable for yourself okay so this is the the id and the first uh we can just try to run this first before that um in case you install it to a different location other than this you copy this part go to your references and then we'll have to link it to that this tbl file okay so here if you can see it's already linked to that.
Otherwise you can just go there by putting the path like this and then you select the file and just click on okay for now i've already done this so i'm not gonna redo this one so if you've installed this correctly once you run this it should give you um the app information so the first time you run it it's also now gonna run the service as well okay so this is basic information that the application has been installed successfully and then your vba can now start using those.
Methods now let's try to use this first basically this function is just going to return all the the name of all the sheets that are there in this particular spreadsheet i mean yeah google sheet um so let's try and run this let me code this data let me first run this and then i'll demonstrate that probably will have to give a consent to that particular.
Spreadsheet so at least one client secret should be installed just give me one second i think i renamed this incorrectly i think this one is the user i'm just gonna change this as soon as you download this it's better to rename it otherwise it gets confusing otherwise you can open the file and then you should be able to figure out okay so now it was telling.
Me that uh at least one client secret should be you know like uh should be set up so i'll talk a little bit more about that error but let's try to run this again okay this time is prompting us so the consent screen is the one that we set up is gonna show up here now so choose the account to continue for now um using in this drive where this database is uh there.
So i'm using alpha my double seven so let me go and select that because that's where the spreadsheet is click on advanced go to api and then it's gonna tell you what are the things that this app can have access to see your files upload delete files and so on organize the drive click on hello and then hello again you can confirm the id as well from there so receive verification code you may.
Close this so if you go back to your folder you'll see that there's a token so till the time it doesn't expire the the program will keep using this um this token to access your files in google drive okay now coming back uh to our file here so it retrieved these two uh name of these two sheets from there now if i just run again this time it's not gonna prompt us just return the name of the sheet so let.
Me go back there and create one more tab i'll just have this as sheet 4 so there it is okay now uh very quickly how to use this particular library so here we've declared this object or variable as new the class or object and then once you set this up you should be able to access the methods.
Within it for example here this is uh to get the sheet names and then this is the parameter that you need to pass so this is the name of the the id that we got it initially so we passed this particular id and then once you this particular this function is executed it's going to return um the list of all the sheet names in array and then this part is basically just printing that for example if i run this now again.
Stop here and if you go to your local window you should be able to see there are three items here okay and basically this part is basically just uh resizing the range and then printing it out in transpose format for this array okay now um similarly you know like uh there are two more of them one is to happen and one is to retrieve okay so let's see the append uh example.
So in this example uh here um we're saying that the data will have uh this tree is the maximum like the upper index of this array this v data is going to be array and this is going to be string and then this name of the sheets where we want to write the data to and then this is the name of the tab that we're writing the data to so if we go to google this is the tab we're trying to write data to and then in the first field we want to put the date so if i just move here.
We're going to write a date here second we want to put the client id so for example if i just uh just get rid of this let's give me one second so let's see we want to just put clt 17 and the next one is the amount so let's say that we want to put this amount and the state as fl something like that.
Again coming down here these are the values that we are assigning to array so if you you can also set up from one to you know like four for example but if you just specify one number this is starting from zero so zero one two three four these are the values that you're assigning to array and if you have more uh fields then you can first increase the you know like the number of fields that are gonna be the values that you're gonna send to uh this method and then you can keep replicating this array down here okay let me just get rid.
Of this and here again we are setting up this object and then we're referencing into this particular custom library and then using this method we are calling uh this object we are calling this append data in open data so here first we are starting the service using this credentials that we've set up here this part okay and then um so starting the service and then we're calling this append data method.
And this is the name of the the google sheets uh the database where we're writing to and then this is the name of the tab okay sheet name which is here and then the data this is the data we're writing to so let's try and run this once this executed if there is no service it's going to automatically set up the service and then the user is going to get a prompt if you're using the user file okay otherwise if you're using the service file then probably there will be a prompt.
Okay now coming down here it's just clearing the memory so let's try and run this so you can see it automatically um you know i mean it brought whatever data was sent from here so if we just try again uh another amount something like that okay so it's as simple as that so when you uh update this for your program and you know in your in your application you just need to update the name of the.
Tab and then based on the the number of fields that are there in your um google sheets you update you know like you increase or decrease this array accordingly and then uh everything remains the same here you just start you know like using this particular object you call this method first start the service you just keep replicating this before you can actually call this methods and then this will clear the memory if it errors it's going to go.
Down here and it's going to print you so it'll give you a basic idea about that now let's try and retrieve this uh this information to our excel sheet here okay i'm gonna clear this um so test retrieve let me quickly walk you through this again uh just activating this particular sheet and then we're saying we want to retrieve from this particular uh google sheet and then um this is the.
Address from where we want to bring it now very quickly you can specify uh you know like an address like this uh to a12d or d20 2000 whatever it is okay but if you want the entire data to be pulled in you do not have to specify the last row so you just specify the column and then it's automatically gonna bring in everything okay um and then this is gonna be the name on so basically it is the full address of uh the type you want to pull in the data from in case you did not mention the.
Name of the tab like this it's just gonna bring in the data from the first step from the google google sheet here okay uh so let's try this i'm gonna bring in the data all this data from this particular tab again setting up the service and then it's gonna call this retrieve method and then this is going to be the spreadsheet id this is the file we're referring to and then this is the name of the tab and then this is the range from where we want to retrieve the data from.
And then once it's here it basically prints this data again after one demo i'll give a quick walkthrough about that array so let me run this okay so this is all the information from that burglar now let's say that we want to retrieve just the three rows you can specify it like this if you want to specif uh bring in you know only one row that would be just a header yeah like this.
Okay and let's say that you have more columns let's say this is um country or something like that now from here you can specify um just increase the column index and then it brings that information and then if you just specify the last column it's just gonna bring in everything now you can also try to do in this fashion.
Okay that that didn't work here there was a problem that was not working out okay so maybe we can try to do a1 is to a okay it retrieves all this information so you can try it out um you'll find more uh information in the video description you can try to play around with this okay and then if there is something that you have a suggestion please do let me know in the video description okay now uh now you know how to write.
The data to google sheets using this custom library and also to um to retrieve it and a little bit more about this for example like at the moment here again i'm just creating the the range okay dynamically based on the array that it's returning and then we're printing the data there using this uh sh data variable which is uh um which is an array variable um so for example if i run this i'll just stop here.
And let's go back to local windows here you'll see that uh the data that is coming in from so here the area is uh the data stored in broadband in this area okay so you can also use this method to loop through for example like um you can look from lower bound this is basically like uh for if you want to easily understand this uh.
You can consider this uh l bound this i will be like the rows okay i will look from lower lower um l bound means lower bound array okay the starting index and the u bound will be like less index so in this example there are 17 of them so if i do this u bound of this is gonna return 16 and since the index is starting from zero there are 17 okay and then uh this j is going to return the lower bound again.
And uh like how many columns are there so within this array uh one single there's one two three four and five so if i do this u bound of the column within the array so it returns four okay again the index starts from zero so you can also just uh use this for example if i just uncomment this out and let me drag this up here and if we print this out you can also.
