Build a CRM with Google Sheets + PostgreSQL + Stripe API Tutorial (Ecommerce Course)

Key Takeaways

hello everyone here on the free codecamp channel wanting to learn how to make your own crm with google sheets or postgres sql i say or as i will be showing you how to build the crm with both so if you are more comfortable with google sheets we will start off with that or if you want to use a database i will be showing you how to make it in postgres sql 2. my name is annie cooper and i'm a software developer and course creator on youtube i am also your host for this actionpap video for all skill levels it is my personal mission to show you that anyone can make a crm or customer relationship management system and make it customizable to them and their business a crm is essentially a way to manage your customer data for example imagine having thousands and thousands of customers it would become virtually impossible to remember which one of your customers bought what when or when the last time that you emailed them was crms allow you to add this data to the system filter through it to find customers based on a variety of variables and even automate your processes such as sending thank you emails or payment reminders the main purpose of crms has traditionally been improving customer service and driving sales crm softwares actually have been around since the late 1990s but have really flourished in the recent years some examples of famous crms are salesforce oracle netsuite for larger enterprises and hubspot and freshwork for smaller businesses now there are many reasons why you might want to build your own crm for me the most obvious is that crms can be expensive and often you are paying for features that you don't really care about that much using low code solutions such as the one we will be using in this course allows you to create your own personalized crm experience totally for free or at least at a fraction of the traditional price this crm will allow you to store your data in a visual way meaning we will be creating dynamic graphs and charts as well as allows you to add or edit data from the platform itself it will also allow you to perform refunds directly from the platform using the strike api as well as send out automated emails based on templates of your choice api stands for application programming interface they allow for technologies to essentially talk with each other and are essential to so many services that we rely on today they are in fact behind most apps we use on a day-to-day basis and they can shape the information pass between one technology to another apis are everywhere we are in fact going to be using the stripe api and a mailing api in this course but there are so many you can choose from to improve your crm offering this course is going to adopt what is considered a low code approach to creating platforms this means there are no hard coding prerequisites for doing this course although unlike no code and understanding in the fundamentals of html css and javascript are advised if you want to take your project to the next level in terms of styling and functionality to break it down for you here is what we are going to learn step by step in part one we will build out our crm from google sheets in which we will fetch all of our data into the platform but then also learn how to add new data to the existing google sheet directly from the platform we have built we will also learn how to edit data 2 so that it is saved back in the google sheet afterwards next in part 2 we will integrate stripe for processing refunds thanks to the pre-configured stripe api after that in part 3 we will learn how to send out automated email notifications with the mailing api and finally in part 4 we will upgrade and transition the backend away from google sheets and to postgres sql for scalability and to handle more complex features ok so what are we waiting for let's go to building out our crm okay so welcome to the retool platform this is the platform that we will be using for this tutorial in order to create a dashboard that looks like this okay so in order to do this i'm gonna start off by creating a new project on the main dashboard here are some previous projects that i have done we're gonna go ahead and just click here to create a blank app and let's go ahead and give this a name so i'm going to call this fcc crm and just click create app which will take me to this blank dashboard okay so here we go here is our blank dashboard with a table that has been created for us just as a demonstration i'm going to simply go ahead and delete that table to truly make this a blank slate now we are going to be working with some data now in any sort of situation say you're a small business owner and you have a lot of data that you then want to use to create an app that's the angle that we're going to go with okay so we are going to take this sample data from northwind right here this sample data will provide us with a table of categories regions for our customers territories customer demographics customer demographics again a table of employees to show which employee deals with which customer and order employee by territory supplier product shipper the sales order itself the order details and so on and then we're going to insert all that data so this is the data that we are going to work with for the first part of this tutorial i have gone ahead and put all of this data in a google spreadsheet for you as you will see here here all our customer there is a lot of data we also have the sales orders and so on and so on if you would like to make your own table all you have to do is go to your google drive or sign up if you don't have an account create a spreadsheet here by clicking on google sheets and start creating columns and rows you can even label the tabs down here as previously seen and create new tabs with new tables so that is just some background for you on google sheets and how to create your own but for now and for this tutorial if you want to follow along please go ahead and just use the table with the northwind data i pre-made this data mimics a generic company that receives orders these orders are represented by rows here in this table which we have put in a tab called sales orders data in the sales order table can also link to specific products that can be cross-linked to here employees that deal with the orders the customers that make the order and so on and so on can also be linked i would advise taking a moment here to really understand what is going on in this database so i will be sharing this link with you in the description below please go ahead and have a look at that once again here are all our orders there are up to 831 orders that we are going to be working with so great a lot of data let's carry on so the first thing i'm going to do is create a new resource so let's go ahead and go to our resource page here you can see all the resources available to us i'm going to go ahead and select the google sheet one on this occasion as we want to be able to connect the google sheet we are looking at to the platform so these are the steps you will need to do in order to make that happen great so now let's go back to our app and don't forget to refresh the page now i'm going to make a new query and as the resource i'm going to choose the new resource we just made which is called northwind crm data so there we go we have officially linked up our google sheet to this platform so now let's go back to the app we are building we are just going to use a component the table component to be precise from the ui panel on the right in order to map all our data onto it okay so that is what we are going to do if it helps i like to think of this platform as having a bottom panel that is to make queries a panel on the right that is for all our ui components and a panel on the left which is for seeing the shape of our data that comes back to us after we make a query i will be showing you this later on okay so now we can actually pick a spreadsheet id and i'm just going to select northwind data as that is what we called our google sheet here and for the sheet name well the first thing that i want to get is all the sales orders so let's go ahead and select that here sales orders just typing that in making sure that it's spelt exactly the same way that we did right here sales order sales order great and just click save and run so now we should see all our data coming up down here wonderful i'm just going to rename this to get sales or the data so that it's more readable for us later on wonderful so we are now getting all our sales order data and to create a table with it well what i'm going to do is just drag a table in here and there we go we will see our table populate with all the sales order data this is because we are on this query our query here is selected so it knows to use this query in order to populate our table while we're here i'm just going to get rid of this query as we are not using it at the moment wonderful so there is our table it is looking good let's carry on before we move on i'm actually just going to edit this table a little bit so that it looks a little bit neater for us so let's do that now so i can simply click on the columns here and rename the column if i want so for example maybe let's call this order id like so and that will update in here let's also get the customer id so i'm just going to go just like so and so on so while we are here i'm also going to show you that you can format each of the columns so for example the required date well if i click on this i can even choose date time local time zone or original time zone it is completely up to you whichever one you'd wish let's go with original time zone like so and that will format the date to be a little bit more readable for us as opposed to this format right here let's do the same on the shipped date so once again let's go ship date and just select column type original time zone and you will see that changing and finally on the order date i'm just going to click here column type date time original time zone wonderful so this is already looking a bit better while i am here let's have a look at what else we can do we can of course also hide columns so for example perhaps i don't want the ship region all i would do is simply go here and click on the little i to hide it and i'm also going to hide the shipper id and the freight number okay so that is looking better with the information that i want one thing to note here is that i actually added this charge id this charge id is from stripe i have added it in the sales order right here so this column has been added by me and i have done this so that we can demonstrate how to issue straight refunds on particular orders so once again this was not with the northwind data here this is a column that i populated myself and created myself but don't worry if you're using the link in the description below you should see this too okay so while we're here i'm just going to rename all of these as well so let's maybe change this to amp id for employee id let's also do order date shipped date required date ship name i'm actually going to hide i don't really think we need this ship address i'm just going to put ship address like so ship city i'm just going to give us city postcode i'm going to put postal code let's just leave this as country and the charge id wonderful so this is already looking much better for me great so we have our table this is looking good let's go ahead and add a few more tables while we're here so i can go ahead and perhaps let's add the table of customers as i think that is some good data that i would want as a startup so once again i would just go into here click new resource query making sure that i am on the northwind crm data as that is the google sheet that we have linked here i'm going to read data from a spreadsheet i am also going to select which id so northwind data is what we call this spreadsheet and then the sheet name well we know that is customer so i'm simply just going to type customer here and click save and run and while i'm here i'm going to rename this query so that we can keep track of them better get customer data just like set great so now we can put another table in however i'm going to show you a really cool way to organize your data we can use the tabs component so if i just go ahead and work in the tabs here i can now put one table on tab one and another table on tab too so i'm going to put all my sales order i'm just going to drag that table drag this table up in here and there we go so these are now our sales orders we can even put some text here in order to make this more obvious to everyone so i'm just going to grab some text and i'm simply going to put sales orders like so we can also make this bold if we wish we are using markdown to make this bold markdown is a lightweight markup language for creating formatted text using a plain text editor it's the same thing you use when writing github readmes you can find all the syntax you need here if you wish so there we go now it's more clear that sales orders and then i can also do so on tab two so on tab two this is going to be customers so i'm just going to put customers making sure that it's spelled correctly like so and making sure i'm on the query i'm just going to grab a table and just put it here in order to populate this with the data that we see from our query displaying right here after we have run the query so there we go great once again if you wish you can choose to hide certain things perhaps let's go ahead and hide a few now because this is quite a lot information so perhaps i don't want the fax number i can go ahead and hide that i can also hide the region if i wish it is totally up to me so perhaps i will either regen i think this is enough data for me and of course we can just pull that out like so great so we've got our customer data and then we also have our sales orders we can also of course rename the tab so i can do so right here sales orders and then we have customers as well so there we go i'm gonna actually do one more table before we continue adding other things another table that i would like to add is the table of orders themselves because at the moment we can see here the order number and it gives us the information about the customer and the shipping date and the status however i just want to know what is exactly associated with this order so for this i'm actually going to use the order detail so i'm going to get this table in here so once again new resource query making sure that i am on northwind crm data i'm going to read data and i'm going to choose the sheet northwind data and the sheet name is order detail so i'm just going to go order detail like so making sure that it's for correct and save and run and there we go so we have that here let's also rename this to get order details i'm just going to drag that once again up here so that we can see the order details just like that okay wonderful so this is looking good once again perhaps let's put some text here just to let everyone know that these are order details and i'm going to get rid of that emoji we can actually do some editing on here too so for example here is my tab we can style the tab with all these pre-configured things or we can you know google fully custom at the moment i'm simply just going to choose to change the header like so so i'm going to choose to make this white and that's already looking a little bit more stylish for me please feel free to go wild that part is totally up to you so there we go we have now completed the part where we put in all our tables now i'm going to show you how to create pie charts and visually show this data thanks to these components right here so i'm just going to go and use a component such as chart in order to do this or we can make a progress circle that part is completely up to us for now though i'm just going to create a new tabs item here so tabs component i'm just going to drag that in so that we can display different types of data on different tabs so i'm just going to make this a little bit bigger like so and great and once again i'm just going to change the header background to be white so i can simply just drag this up here or i can put in f f f fff to symbolize white by myself so tab one i think the tab one is going to be an overview so let's make this an overview and it's going to just be an overview of the amount of orders that we have shipped so i'm going to use the table right here i'm going to use the ship date in order to show us how many orders have been shipped and i'm also going to use the table data here in order to show us how many items have been sold at full price okay because you can see discounts have implied to some i want to know exactly how many orders we have sold at full price so to do this i'm simply going to get the progress circle and drag it in here just like so so here we have a progress circle and now let's link it up at the moment the value is hard coded however we don't have to hard code it we can use the table data in order to make this dynamic so as we said we want to show how many orders have been fulfilled or in other words shipped so we are going to get our sales order data which is an array and we are going to use the javascript method of filter to filter out each order that has a ship date of not an empty string as if it is not an empty string it means a ship date exists and we can say this order is fulfilled next we are going to find out how many of those exist and we will do so with the length property on the array we will then get that value and divide it by the total amount of orders in the get sales order data array and multiply it by 100 as that is how percentages work okay so fulfilled orders over total of orders multiplied by 100 will give us the percentage of fulfilled orders so to do this this is the syntax for doing so in here i need to get these two curly braces well we know that this table is made up of get sales ordered data so let's get says order data get sales order data and we need to get it data so we can use this in order to see how the data is formed so here is get sales order data and you will see that comes back as an object that object has data in it which is why we are getting data and data is an array it's an array of different objects so let's filter our array i'm going to use the javascript method of filter to filter out each item in the array let's call it an order so like so order and for each order in our array i'm going to get the orders shipped date and if it does not equal an empty string then i want to get this array's length and then just divide it over the get sales order data data length so the length of the entire array ah there was a spelling mistake here let's go ahead and correct get sales order data there we go get sales order data and now let's multiply by this 100 because that is how you would get a percentage so there we go a percentage so just talking this through again what we are doing is getting the sales order data that we see here so as you can see here this is the object that returns back we're then going into data which is an array essentially of all this information and for each item in this array which we have called order we're going to filter out by the shipped date so this right here and if ship date is null so it doesn't contain anything well then we know that it's not been shipped and then we're going to divide that by the length of this entire array so by the amount of items in these sales orders and multiply by 100 because that is how you'd get the percentage so as you will see here 97 of our orders have been shipped so in other words 97 of our orders have a shipped date wonderful so hopefully that was easy let's carry on so now once again i'm just going to drag over some text let's drag that over here and let's do shift orders just so people could know what we are talking about shift or just let's again just get rid of the emoji we don't need that and then we can also have some text to tell us exactly the percentage so once again i'm just going to drag that up here and we can do something like this so i'm just going to get rid of that currently and then we can put the percentage in there if we want currently 97 of all orders have been shipped just so it's super clear to whoever's reading and once again this should be easy we can actually grab the value of this if we want so this is progress circle one so i can instead of doing the calculation again just get progress circle one and i can get its value so there we go and we can use some javascript on this to make it to two decimal places if we wish to fix and then just pass through a two just like so great so this is looking good let's just add a percentage here and wonderful so now that we've done that how do you think we would make a percentage for all the orders that have been sold at full price well once again i would simply grab a progress circle so this is progress circle number two this time i'm just going to put that in here and this time let's get the get order details so get order details data that's correct so i don't want the value 6 to be card coded i want to get the order details data get order details data which is the array and this time i'm actually going to filter by the discount so very similarly to what we did last time we can use the filter javascript method to filter each order so i'm just going to put order i'm going to call every item in the array an order and for each order i'm going to get the order discount discount because that is what it's called here the order it's going to an item discount and if the order discount equals the string of 0 because that is what we have here we have strings of zero not numbers then i want to get the length of the array so i want to get exactly how many have the string of zero under discount and divide this by the array itself so get order details data dot length and of course multiply it by a hundred to get a percentage so there we go making sure that this is in two curly braces because that is the syntax for doing so okay great so we can see 61 of my orders have been sold at full price so i'm just going to copy that and then once again let's just get the progress circle 2 value this time currently 61.11 of all orders have been sold at full price and then let's just make sure we have no spelling mistakes here either and great and once again let's perhaps just give this a title this time saying full price orders so full price orders just like so i'm just making sure that they are all aligned correctly to make them look good next to each other so there we go okay great so we have now learned how to use the progress circles the next thing i'm going to show you is how to show a statistic so let's go ahead and perhaps this time show something like the total of all the orders so for this i'm just going to get the unit price and add it all up okay so that's what i want to do and multiply it by the quantity sold so unit price by quantity and for this i'm actually going to use a statistics component so we already have this here it's already sort of nicely styled for us to look like a statistic so there we go let's also perhaps move these down now i'm just going to move these down of course the styling is completely up to you please feel free to go wild on the styling however you wish i'm also going to put in a divider so just whack that in here again this is just a styling preference please feel free to style this up however you wish once you have got two grips with the retool tool okay so just putting these circles back like that that's making them smaller again you could just fiddle around as this forever so please just bear with me and great so here i want to show the total of all the oldest salts i'm just going to put total in like so total and then here well we don't want to hard code this once again let's get up our curly braces because we want to get we can also do it by table so this is table three let's get table three let's get the data in it and we just want to go by selected row so i can use selected row selected row and then i can get the data and what i can do is get its quantity and then i just have to multiply it by the table 3 selected row data unit price okay so now whatever row i click on it will show me the total so that is looking pretty good perhaps let's maybe make this clear what this is total of selected row and now we can simply get the total just by clicking the row so that is quite handy for us as small business owners i also just want to perhaps get rid of this as it doesn't really mean anything so let's get rid of the caption here and i don't really need this increase amount so we don't want a positive trend i'm just gonna delete that like so great so there we go now we've just made a useful tool to essentially show us the total of the selected row the next thing i want to do is just show us the total amount of customers that we have in this dashboard and the total amount of orders okay so this is really an overview this tab is everything to do with an overview of the total dashboard so let's go ahead and do that so this time i'm simply just going to use text to do this and in here i want to be able to show all the customers that we have so i'm just going to once again perhaps i'm just going to hard code customers here like so and then in here well i do want to display the amount of customers that we have so to do this i'm going to use get customer data and then just get the length of all of this data right here because this data is the customer data so if i just simply go by length of the amount of essentially rows that we have that should give me the customer amount that we have in this dashboard so there we go once again if we look at this table all i've done is simply get the length of the array and the array symbolizes the rows okay so each array item is essentially a row so if i just count the rows it will show me the amount of customers that we have great so there's our customer data i can of course spread this out a little bit if i wish and here we can have the order data so this is going to be the amount orders and once again to get the order amount well i'm simply going to get the sales order data so get sales order data data and then just get the length great so now we can see the amount of orders that we have we can also see the amount of customers and if i add orders to this table this should change dynamically so this is looking good there is our overview let's carry on let's have a look at tab 2. for tab 2 i want to show exactly what the work distribution is by employee so if we have a look at these sales orders you will see that each order we can actually have a look here as well has an employer id by it so right here employer id so we know what order is assigned to which employee but we want to see that visually so for this i am just going to use another component this time i want to create a pie chart so to do this i'm going to go to charts and i'm just going to drag this over like so and i'm also going to select pie chart and this time we are getting the sales order data that is correct however i just want to show the data by its employer id so under value labels let's select employer id just like so and there we go so now we can see all the work done by employer id so essentially what is happening here is that we can see that employer number four has the most work as the most orders assigned to them so this is looking good let's carry on the next thing i want to do is actually show me the employer's name by this number right because we have the data here if you look here we have the employees along with the employer id so once again i'm going to go in here and click a new resource query making sure that northwind crm data is selected but this time making sure that northwind data is selected too as the spreadsheet name i want to get the employee making sure that it's spelled exactly the same as it's spelt here so emplo and just click save and run and once again i'm just going to get a table making sure that on this query let's call this get employee data and i'm just going to drag in a table right here and that will show me all the employee data just like so so this is looking good i can now get the employee data i can also hide things of course like for example let's say i just want their name i can do so very easily like this by hiding all the other tabs great so now we have the employee id we have their last name their first name as well as their title wonderful and let's just rename this tab to distribution so work distribution distribution like so and i'm just going to make another tab and this time the tab that i want to create is a tab called team and a title another one called demo graphic to show us where most of our orders come from great now for team well i'm actually just going to get the same table right here and i'm just going to show us all the data that we sort of had before because i think it's useful to have you know all the employee data not just the first name and the last name so that we can filter through it so this is going to hold all our team data just like so and demographic well as we said the demographic i want us to show exactly where most of our orders are coming from so i can do so easily once again i'm going to use a pie chart for this so let's go ahead and find a pie chart and this time i actually want to get the customer data let's get the customer data let's do it by where the customer is based customer data just like so making sure this is a pie chart and this time i just want to get the country because that's what i want to display i want to show what the breakdown is by country for all our customers so just like that we can even position this uh however we want so perhaps this is a good way and wonderful you will see that most of our customers come from the us just by simply dragging a pie chart like so okay great so this is looking good let's move on i'm just going to drag this a bit lower to make sure that's on the same level we will be doing all the styling a bit later on this is looking good for now so great we are displaying all the data that we have in the google spreadsheet we are picking out which sheets we want to use and then we are also getting an overview of the data that we have imported in here in various tabs that we have made the next thing that i want to do is actually add data from our spreadsheet so i'm going to show you how to do this now so as we mentioned at the moment we're just reading data i can also add data to this spreadsheet from this platform to do this i'm going to show you how to do this first off let's actually create a modal so i'm going to grab the modal like so i'm just going to drag it in here under the sales orders title and i want this model to pop up and essentially be able to create a new order that i add in here so let's go ahead and do that a modal is a pop-up window that we are going to see to add the data to our spreadsheet as we just mentioned we will do this by populating the model with form field inputs and a submit button so first off what do we want in our module well we're going to have lots of inputs essentially input all of this data but first off let's just start perhaps with the title i want my title to say create an order as that is what is happening here so create an order just like so we can also change the background so if i perhaps want it red so like a red like this that is also possible too okay so there we go and there is an option to create a form to make our lives easier so let's go ahead and find that form first so i'm just going to search for form and drag that in here so now this is good as it comes with all the button and the styling and it just makes it a little bit nicer for us to look at so here's our order form just dragging this all the way up here and let's just drag out the title as well great and now let's go ahead and get inputs for all of this data so all of the data you see in the row we're going to have to collect so the first thing that we're going to collect is the order id so let's go ahead and do that here perhaps the value we're going to leave empty enter value or we could have an order number as the placeholder it is totally up to us the label we're going to put order id as that's what we want to put in here just like so and we can hide the label we can show the label we could also make the label appear at the top or bottom it is completely up to you so there's my input for the label let's carry on the next input that i need is to get the employer id so let's go ahead and do that because the order date we're going to have to select so i'm just going to make that up here so here's a label and then for this let's call this employee id just like so and once again let's also make the same for the customer id so let's put customer id so customer id employee id the next thing we do is some dates so let's go ahead and get a date picker up so i can do so quite simply just move these down a little bit perhaps and this time let's get the date picker and just drag that in here so here we have the date picker i'm going to call this ordered by or ordered date and then we can select a date just copy paste that okay so we've got the order date the next thing we need is the required by so required by date and then finally we need the address fields so perhaps let's put a divider in there so i'm just going to drag a divider in just so we can separate the two visually and now let's get an input so a text input just drag it all the way over here and this is going to be for the address let's put that below the divider so it's going to drag the divider up here we're also going to have one for the city the country and the region so here we have four inputs all text inputs if whatever reason you need a number input there is one specifically four numbers so just bear that in mind so these are all text inputs so we've got the address the next one i want to be city this one i want to be country so let's just change the label of that and this one i want to be region and we have one more thing so we're not actually done in this address section because we also need the postcode so let's also have the post code okay so that is it for the region or address parts once again we let's split this out because we do need some more things even though we can't see the ship id and the ship id and the freight id and they're shipped on date because we hid some of them we are going to have to put this into our form as this data is required i'm simply just going by the fields here okay so i'm just going through all of them and just making sure we are collecting data for all of those to be populated so once again let's just get the divider out just drag that across here and then let's go ahead and just put in some more inputs so once again i'm just going to drag that below here this time let's perhaps make the label at the top because we're going to have three here so one oops two three and then just position them a little bit so label one two and three and let's have this as ship name let's have this as shipper id and let's have this as the fright field freight shipper id not shipped and finally we're just going to have a date picker to pick the date that this was shipped on so once again let's get the date picker and just drag that in here so just like so and give this the label of shipped on great so this is looking good and of course we have the submit button as well so nice i'm really happy with how this is looking so far okay great so we're collecting all that data but now we need to essentially get all this data and get this data to make a query with it to add data to the sales order table so what i'm going to do is create a new query this time this query let's call this add sales order okay so that's what our query is called this will be to northwood crm and then we're going to append data to a spreadsheet choose the spreadsheet well that's northwind data and the sheet we want to add to is sales order so let's just go ahead and put sales order like so and in here what do we need to do well we need to add data that looks like this so let's look in here let's look at the data okay and we need to make an object that essentially looks exactly like this i'm going to copy that and i'm going to make an array so open up the square braces and then we could just make an object and paste all the data like so but i think this is quite messy so i'm going to take you through this step by step okay so open up the array make an object and we're going to copy this object right here so the first thing that we need to put in is the we could start with a charge id let's go ahead and do that let's just make sure that everything's in order so the first one is charge id always making sure to spell everything exactly the same as you see it so charge id and for now i'm just going to put the string of test put a comma and the next thing is the cust id so once again let's put the string of cost id and put the string of test the next is id for employee id and once again i'm putting test and you get it so fright is next test by a comma it's going to copy this actually because there's a lot of repetition fright order date then we have the required date just paste next we have the ship address and paste next we have the ship city and paste ship country is next so ship country ship name then we have the shipped date and the ship region okay so that is it let's quickly make sure that we didn't make any spelling mistakes even though we will get an error so that's all the data that we have okay and let's check it out so i'm just gonna click save and run this so i'm just gonna get this up a little bit so we can see what returns back to us okay so that runs successfully we saw the little pop-up now let's have a look here and go all the way to the bottom of sales orders as we did append the row to this table so it should appear at the very end of this table and there we go i did press it twice so two rows have been offended and everything is working great wonderful so now that the test is working let's replace these test strings with actual values from our form field right here so all i'm going to do is match the input value to the key okay so there we go charge id well we know that the charge id at the moment i'm going to hard code it because all the charge ids are the same this is just a test charge id that i'm getting from the stripe api that we will go into later so this one i can hard code so let's go ahead and do that and put that as a string the next thing is the custom id so we know that that's going to be text input choose value so i'm just going to replace this let's get our curly braces up and i'm going to grab this element which we called test input 2 test in put two and i'm going to get its value just like so that should say text input value apologies text input value okay great so this is looking good we can of course if we really want to change these so if i really wanted to rename them i could so just know that there is an option to do that i'm not going to do this for this tutorial as that will take a lot of time but to know that if i wanted to change this i could great now let's get the employer id so for this we're going to use text input 3. get the two curly braces and go text input 3 dot value great let's do the same for the freight so here we go that's text input 9. once again let's just get rid of these quote marks let's get the test word here get our curly braces and this time we need to get text input nine so text input 9 dot value making sure to spell text input correctly just like so the order date well the order date is date time two so let's go ahead and replace the test value here with date time two dot value the required date once again that is date time one so i'm just going to get the test and use the curly braces to get date time 1 what value so it really is this simple that's all i'm doing the shipping address or the ship address in other words that's going to be text input 7. so i'm just gonna look at the curly braces and get text input seven we can use the drop down to make our lives a little bit easier the shipped city is going to be six country is five and region is eight so let's try remember that just gonna copy this one ship city we said was six ship country i think we said that was five but we'll double check and the ship region is h do we have ship region here uh here we go ship region so let's make that eight and just a few more left we need the postcode which is four so let's find the postcode in here there we go and replace it with text input and then we just have the ship name which is 10 ship id 11 and i think that should be it so ship name is text input 10 ship id is 11 and i think there's just two dates left so let's have a look again back here uh one date left date one is the shipped on date so let's just go in here and this is date one date one dot value so this is looking good let's test it out so i'm just going to save that and this time to test it out i'm just going to input some data so let's go ahead and do this employee id i'm going to say 2 customer id 3 order id again this is just for tests let's pick a date so i'm just going to pick the date and the time for this one the address again i'm just going to put test for now city test country test region test postcode 363 ship name a a and then fright a and then shipped on let's just have a date for this one let's go with the tenth of love and then click run so now okay we did get an error why is this so we can actually read right here what has happened and you will see the line has not been added there was an error and i think it is something to do with this uh we have left a little apostrophe there so that's gone and i think we also don't have the order id so that is one we didn't pick out if i'm not mistaken so i'm just going to put that in the front or after the order date so just in here just to make sure it's the same as here for readability so order id and the order id is going to be the text input one so text input one dot value and once again just make sure to format that correctly so put a comma there and great so now if we save this and run it and have a look in here well there you go the row has been updated with the correct values in each column great so that is now working we have now figured out a way to add data from this platform to our google sheet i just need to do one last thing is that is connect the button right here so that it sends when we click this button so i'm just gonna click that right here so i've just selected the button if you can see that so here we have it and we need on submit we could make the button full width if we wish on submit so there's the submit button we also want some events and i just want to trigger the query add sales order so i want to run this query rather than us running it manually i could do it by attaching it to this button okay so that's what i want to happen just like so and we could also add other things so for example i can also add instead of trigger a query just to send confetti so that we know that is done and then as a third thing so after we get the sales order data i want to display it in here so in the table which means i essentially need to make a request to get all of this data again so i need to run the query to get sales ordered data so i'm just going to do that in here so in the query rather than the button and i'm going to trigger the query get sales order data and save that so now when we fill out this form and click the submit button i'm just going to zoom out a little bit so you can see the submit button and when we click the submit button so let's do another one this time this time i'm going to put order id www at the end and i'm just going to click submit so we are making a query to add data to this google sheet we are then sending the confetti and we are making a get request to get all the data in here which means that now if i look here and just go to the end so let's go to the very end i'm going to put in 76 there is the new line that we have just added so i'm just going to zoom back in again so you can see it a little bit better so great this is all working fine i'm really happy with how this is working of course on here there are input tools to download all the data you can also filter and you can also reload manually if you wish okay so these are all options that you can do on the table as well great um while we are here i'm just going to change the color of this as i think blue is a bit passive let's go ahead and make it red so just like that wonderful and i'm just going to go back to the first page so page one great so we can now add data directly to our database here using the platform but there are other things we could do as well so i can actually also delete data from this table so that it shows up here as well so that is the next part we've just added data next i'm going to show you how to delete data 2. okay so for this part i'm actually going to have to write a new query to delete data however first off let's go ahead and add a button to this table so i can do so easily by selecting the table and this time i'm not actually going to add another column i'm going to add an action so here are my actions and i'm just going to call this the delete action so here's my action i just created and let's call this delete okay so just like so so there we go that has now been appended to the left of my table and on here i want to run a query well that query is going to be the delete row query so let's go ahead and write it i'm just going to create a new resource and make sure that it is using the northwind crm data and next i want to not append data not update i want to delete a single row from the spreadsheet so that is looking good let's choose a sheet well that is the northwind data sheet and the sheet name that i want to work with is the sales order sheet so making sure that i'm on this sheet right here and now i'm just going to have to filter to match row and let's go by order id so i'm just going to select the order id table making sure to spell it exactly the same as we did in here so i'm going to filter by order id and make sure that equals the table so table 1 however it's spelt here and i'm just going to put selected row data order id so just like that so we're literally going to search for a match by order id and we're going to find that order id and delete the entire row if it equals the selected order id of this row okay so i'm just gonna save this and let's rename this to delete selected row great and hit preview so at the moment i'm on this row that's the selected rom on and this is the row that would get deleted from our spreadsheet if i hit run okay so there we go and of course this is great but we don't want to run this manually we want to attach it to the actions so i'm just going to get that and run a query the query i want to run is the delete selected row and there we go so now let's check it out it's going to move that down here and let's delete this row so i'm just going to click here and great so now if we look in here that row is now deleted and of course we need to refresh this data so on success of this i want to add one more thing and i want to trigger a query and that query is to get sales order data again and just click save so let's go ahead and delete another row so this time let's delete this row and i'm just going to click delete so now that is deleted you'll just see that disappear and ta-da this table refreshes with the fresh data from this spreadsheet great so we are now adding data we are deleting data this is looking awesome now we have one last thing to do and that is process a refund so for this i'm going to show you how to do this using the stripe api so to do this i'm actually just going to create a container so that we can store our refund in here so that it looks a little bit nicer visually so i've just gone ahead and created that and now let's also create another form that we're going to put in here so i'm just going to put in a form into here so let's again just find a form that we can use let's go ahead and put that in here like so let's perhaps also change the button color so i don't really like this button let's change the color let's make it some stripe colors perhaps so i'm just going to use the hex curve that i have here just like so and then i'm also going to make this button the full width and not only that let's change what it says i'm going to say refund and then also notify as we want to send an email notifying the person that the refund has been made so great let's go ahead and just put in some text so we know what's happening in here i'm just going to drag that in here and this should say create refund just like so i don't need the exclamation point now let's put in some text and then we'll just make a little note so people need to select a row from the sales order orders to refund an amount okay because we want to pick out all the information from a row we select just like we did with the delete in order to make our refund okay so there we go the next thing i want to populate so i want to now populate fields by me just selecting a row so we don't have to do much manually and this is going to be the charge id that we want to refund so i'm just going to use that we also want the refund amount so for this i'm actually going to use a number input as this has to be a number so i'm just going to drag that in here like so and put the label as refund amount that we decide and then let's also have a refund reason for this i'm gonna actually have a select becau