Excel Macros with JavaScript & Node JS - Read Excel File to JSON, Modify & Write Back to Excel

alright so in this video I'm gonna show you how to do Excel automation using javascript and nodejs so based on that to be able to do this you're gonna need nodejs installed which you can find at node.js org you can see I'm on the website right now you will also need Visual Studio code or really any text editor if you don't have any preference you can use Visual Studio code that will be fine so let me show you what I have here so I have this folder and in this folder I have an excel file that's pretty much where I'm gonna start this is that excel file so it's basically a file with two tabs this is one tab with some information there's a second tab called subtotal that's the second tab this is pretty much the data we'll be working with now first I'm gonna install nodejs now this installation is available both on Mac or Windows systems so whichever system you're on just download and install for me I'm on a Mac so I'm gonna click on this download for a Mac I'll use the recommended version but technically you could use current shouldn't be an issue I'll go ahead and download that and then pretty much like any installation next next next next if you want to change some options you can so I'm going to really scroll through this installation and get back when it's done so Don we this installation go ahead and heat closed and I'm good with that again if you're on Windows just open Windows installation run it's pretty much the same experience and very quickly that should be done now the second piece of software you're gonna need for this is Visual Studio code or any other text editor i've recommend using this if you don't have any preference so there are different versions for different systems I am on a Mac so I'll be using the Mac version so again your download and that's gonna download this and then you install so yet again I'm gonna really quickly run the installation and get back when this is done so after you run your installation and open visual studio code we will just need to open the folder in Visual Studio code so this is that folder that had that excel file in it so what I'm gonna do I'm just gonna drag it right in Visual Studio code and that will open that folder in Visual Studio code and that's pretty much where I am now so X out of this so now we have that excel file here on the left I don't really want to do anything with it so that's just that file and that's good to go so in Visual Studio code if we go on top here on there view we should have the option to use the integrated terminal here so I'll click terminal that will open this so with this we should be able to create our node project now first you probably want to make sure that node was installed successfully so if you just type node in this command line and hit enter that should give you this node windows so if you get this this pointer instead of getting like this is not a recognized command or something like that then you're fine it was installed successfully now to get out of this because we're now inside of our node application we're gonna do period exit and if I enter that should get me out of that application so if you get here that means your installation was successful for your node and you're good to go if you get some other message you need to figure out why your node installation didn't go through well so now in our node application we're gonna run this command so node comes with a package manager and for now if you don't understand what package manager is or you're not familiar with node just don't worry about it so we'll just run an initial command to initialize a new application so I'm going to run this command NPM in it and hit enter and basically now it's gonna ask us what's the package name we're trying to create this is just a name you can really call it anything you want so I'm just gonna leave it as default whatever it suggests is good enough for me just hit enter version hit enter description you can write something I'm just gonna hit enter and entry points so entry point is basically the first file that's gonna run the main file so we can leave it see it says index J s I'm gonna rename this and call it script dot J s hit enter and test command I'll just leave it as this repository we're not gonna do it keyboard basically just enter enter enter until we're done and that is gonna ask you this okay I'm gonna say yes hit enter and that should create this file so what it really does it just creates this JSON file here with information about our applications so mainly what we're concerned about that this is our main app descriptor is and that's what we're going to be using to do our script now this script J s file doesn't exist so I have to make it so I'm gonna go here on the Left panel and go here click on the new file the first plus button that's gonna create a file I'm gonna type script dot jsut want to make sure you do the dot J's part to get it as a JavaScript file I'm gonna hit enter and that created that file script J s which is where I'm gonna write my node application now we have our Visual Studio code installed we have our node.js installed now another thing we're gonna need in addition to this is the package that's gonna let us interact with excel files and for that package for this particular tutorial I'm gonna use this one see Shige Escom so that's their website to get this installed we need to run an install command and this is right here see we have to do this command npm install xlsx so I'm gonna go to again my Visual Studio code and I'm gonna do here in a terminal NPM install xlsx hit enter and that should just install the package so now that that package is installed we have everything we need installed so the first thing we'll have to do we'll have to take that script j/s and import that package so we can use it so I'm gonna start by running require command I'll do require and then the name of the package which is gonna be xlsx all right so I'm gonna use the old JavaScript version so if you're familiar with newer language you can use that too but since most of the people who watch this channel are probably familiar with Apps scripts from Google sheets I'm gonna stick with the older JavaScript version here so first I'm gonna create a variable I'm gonna call xlsx I'm gonna make it equal to this I know you can make it a constant but again as I said I'm gonna use the old version I start by requiring the package so now that package is going to be inside of this variable xlsx and i should be able to use methods and etc out of it so if I do xlsx dot C I'm gonna get some methods one of which is read file and that's what I want to do I want to read that excel file and the file should be the filename as string so if the filename is not in the same folder you would have to do the full path to the file in this case this file is in the same folder as my script is so what I'm gonna do I'm gonna use the name of the file this is the name of the file it's called Mac pivot 2017 dot xlsx and it should be the full name of the file so again I'm gonna save this in a variable I'm gonna say VAR WB I'll call it WB I guess for workbook and that's gonna read that file into that variable so now that we're able to read our workbook into this WB variable we might want to just log some things out just to make sure this works so I'm gonna start with doing a console log so if you're coming from Google sheets scripts this is similar to logger.log and I'm gonna do WB that's at work work and sheet names and that should return an array of worksheets that are in that file so let's try to run this and see how it goes so I'm gonna first make sure I save this file ctrl s command as file save whichever works so once it's saved I'm gonna run this so this file is called script J s so to run that file I'm gonna go here in my terminal window I'm gonna do node space and the name of that file the file is called square dot J s if I had enter see what I get is the return array and it gives me an array of two worksheets one is called data the other one is subtotal and if you remember the file this was our file data is the first tab subtotal is the second tab so that means we're successfully able to read the file and access it so that gives us this workbook with worksheet names in it so I'm gonna go on top here I'm gonna do W B dot and I'll do sheets and then we'll use the name of the worksheet we're trying to target so let's say I want to target this second tab called subtotal so I'm gonna go here and do subtotal in here and then I'll save it in a variable so this will be our worksheet so I'll do VAR w s equals to this save so that's our worksheet now we opened our workbook in that workbook we're gonna open our worksheet now in this worksheet we can have different things as you can see it can have like columns margins etc let's just try to log it out and see what it looks like so I'm gonna do WS that's our worksheet I'm gonna save and try to run this so again we'll go here node space script dot j s which is the name of the file and i have to make sure i type that right there this so that should bring us all the information about this worksheet it gives us the margins in this worksheet we have all this different cells in this worksheet so if I keep scrolling up there it is so we have this reference that gives us the range a 1 through f-22 so if I go here see that's a 1 through f-22 that's the range of data and then basically it says in a 1 cell we have of this text whatever that text is the value that's the date the V stands for value here tax date and that's exactly what we got there now if we keep scrolling down a little bit let's try to find what's in that a to sell the state so if I keep scrolling down a little bit see a two it will give you that the type of that cell is a number and and the value is 4 2 1 3 3 that 4 2 1 3 3 would be that cell in general formatting which is that number so I'm gonna undo that so that's fine so you might be ok with this you might want to read your files and get them in this excel date serial number format or you might want to do JavaScript dates instead of doing this so if you wanted to make sure that these are actual proper JavaScript dates what you have to do when you do this read file you have to pass it some options so if I go here and do a comma see the second one is like options that we can pass and one of those options is this so we can do a little object here and in this object we're gonna pass our option and that option is if we go back to our documentation there it is read file I'm gonna scroll down see this option cell dates so by default it's false so we're gonna change it to true so if I do this I'm gonna copy that go back to this and as an object I'm gonna say that should be true and I have to write that correctly so I'm gonna save this I'm gonna rerun this again and scroll down now if you don't want to type this note space script J s every time you can just hit up arrow key and that will just type the last command you did so I'm gonna hit run and that should run that so now if we look at one of the dates where is it there it is that will be a JavaScript date instead of being that Excel number all right so that's good now we were able to get our worksheet now the next thing we're going to do we're gonna read this worksheet data into an array so I'm gonna create an array of data I'm gonna call it data and what do you do you take that variable and there are multiple options you can do and you can do it directly on this as a method but what you can do you can do this xlsx dot and there are these utilities in there and here we have this sheet two methods so we can convert it to a CSV we can convert it to whatever these things are and one of those is JSON which is our JSON object so I'm gonna open this and then it's gonna accept worksheet so the worksheet is that WS object so that let's lock that out and see what we get save that and I'll go here up and enter to run that and what we get is a JSON object so now instead of getting that weird see this this B cell with all of its stuff we converted it to a nice JSON object and basically it's an array and each row is an object in this array and so it goes so basically it's an array of JSON objects so now we can work with this so let's say what I want to do I want to calculate the difference between sales and cost and get the net so I want to just calculate sales minus cost of goods and then calculate the net here and then I want to get rid of sales and cost of goods columns just have the net so to do something like that I'm gonna go back here now this data is an array so if you look through that data array so that's data is that array and I'm gonna map that array to get a new array of results and that will go to a new variable I'll call variable new data equals to this and this map will accept a function a callback function right here and each time it's gonna accept a record so that record is gonna be this object and each time it's gonna be one of those rows of our data now what I want to do in this object I want to be able to first calculate sales minus cost of goods so to do that I'll go here and do record that sales minus record that Coast so that sales and Coast are the names of those columns and they are case-sensitive so I have to make sure I type it right and I'll save that in a variable I'll call that variable net so after I do that calculation I'm gonna save it in a variable net and what I want to do I want to assign that back to that record so I'm gonna say record which is gonna be that object I'm gonna call it a new column I'll call it net and I'll make that equal to that net which is that now I don't really have to do this two steps actually let's just not do this let's just make it as a one-liner so I'll just do this and not have that variable so I'll just say that the record net which is a new property net record object is going to be sales minus the coast the next thing I want to do I want to also delete sales and cost of goods out of if you remember so I'm gonna say delete record dot sales and then I'll also delete record that coasts so that should delete those properties from this object and then I'm gonna just return that record after all this modifications are done at this point hopefully this new data will be a new array with our information so let's see what's in it so I'm gonna save this go back here and arrow key up Ronda's take a look here see now I have the date region brand state and then it jumps to net sales and cost of goods are gone we got our results so now I want to put this in a spreadsheet so I'm gonna save this in a completely new file so I'll do variable and that will be my new workbook so the new workbook is going to be coming from that xlsx util it is again there this so we're gonna do a book new so we're gonna create a new workbook good so that's gonna be saved in this variable right here so now that we have this I don't need this console.log let me actually just comment that the utilities thing so I'm gonna do utilities and in this utilities there's gonna be a way for us to add and you worksheet to our workbook let's see where it is there this book a pan sheet so and that's gonna accept a workbook see the workbook is gonna be that new WB that workbook and then we can also have a worksheet that we need to pass to it so which we don't have yet so we need to create that and then the third parameter is the name of the worksheet if we want to name it something now for me I'm gonna just call it new WS which we don't have yet and finally I'm gonna give this tab a name I'll call it new data as the name of the tab but the problem is we don't have this new WS yet so we're gonna have to make that so I'm gonna do a variable and to create that again we're gonna go through this xlsx utilities and disutilities there's gonna be a way to convert json to a worksheet see there's this method jason to sheet and this was that json so open parenthesis and it's gonna accept our json that's that so now that we do that JSON array that's gonna create a worksheet out of this and then we're going to pass that worksheet to this to add it to our existing workbook so now finally we need to write that workbook to a file so to do that again we'll go through this xlsx but this time we're gonna do write file it's gonna accept a workbook and that workbook is gonna be this our new workbook comma and then the file name so we can name this file I'm gonna call it new data file and that will be xlsx semicolon save so now I'm gonna run this so we go here arrow up run this and see that new file is created new data file and if I go to that folder it should be there if I open it there it is and that's our new file so that's it that should create our script so we're able to read the file do something with the day and then output it to a completely new file and that should be it for this video thanks for watching please subscribe and I'll see you the next one

Channel: Learn Google Spreadsheets

Views: 96,624

Keywords: Excel, JavaScript, Node, js, nodejs, JSON, macro, automate, read, write, file, tutorial, how to

Id: tKz_ryychBY

Length: 24min 3sec (1443 seconds)

Published: Wed Apr 03 2019