Upload/Export Data To Google Sheets Excel VBA Macro

hello and welcome to the channel in this video we'll see how to export or upload data from Excel to Google Sheets and for that we're gonna use this small table and there are several ways to do it we cannot do it through a Google form another possibility would be using the Google Sheets API but that's a bit more complicated so we're gonna need a recipient worksheet so I'm gonna create a new Google Sheets or you can use an existing sheet and and we're gonna link a Google form to this Google spreadsheet as you see it has created a new tab for formed responses we can rename that to data for example by default it has a column for the timestamp this column cannot be removed but we can hide it and then we can go to edit the form and add those three fields so we're going to have a text field with a short answer that's for the name then we will have another short field or short answer for the country and then we're going to have a number for the ID and that could also be a short answer you can choose any of the other options depending on the data after adding the fields in the form it has updated the headers here not for this one so I'm gonna put here name and we cannot delete the column for the timestamp it must be there but we can we can also change the name of the spreadsheet that say that's members so we're going to have the name the country and the ID of each member and the same for the for the form we can say members form and also here members form now we need to get the form URL let's check how that looks like so that's the form and we're going to need the form response URL along with the keys for each of the entries so for name country and ID to get that we press F12 and go to network we can reload the form and if we submit the form just let's leave it empty as a test we get up here the form response URL so that's one of the things we're gonna need so I'm gonna copy that one and and paste it here for now and if we look here up at the post at the first post which says form response let me make it bigger so this one that says form response and we look at the request we're gonna see the entry IDs for the three Fields so the name the country and the ID and of course the the fields are empty because we didn't feel anything but what we need are these entry IDs so I'm gonna so I'm just gonna copy all also here and we don't need that we have the three um entry IDs for each of the fields that's what we need in our macro so now let's go back to the Excel file and go to the Visual Basic editor and here we're going to have the macro upload to Google Sheets and first of all we need to add a Microsoft XML Library so we go to tools references and down here we have Microsoft XML 6.0 we click OK and that's because we're gonna send a HTTP request in the same way we've done in some previous videos to get data from either from a website or from Google Sheets or from an API so now instead of getting the data we are going to post the data into Google Sheets through the Google form and the URL we're gonna use in this case is going to be so let's declare URL as a string and that URL is going to be the Google form response URL that we have saved here earlier and let me continue in the next line and make this a bit bigger we're going to add each of the entries and for that we need to have ifq and the entry ID for each of the fields so what we've saved earlier here and we close here and the value of that field which is the name but let's call it field one and we'll get back to that to add the name there and we open again the quotes to add the second entry so and the entry closing quotes fill two foreign and then the third entry and well we would add as many entries as we have in the data set and which are which must be the corresponding entries in the Google form of course so that's and entry and field three so that would be our URL and the field one fill 2 and field three correspond to the three fields in our data set in our table so the name the country and the ID so what we're gonna do is we're gonna have a loop to get the data for each row and for each row we will submit a HTTP request to post that data through the Google form so to do that we're gonna have here uh 4 let's say R for row equals 2 we're going to start in row 2. to skip the headers two and in this case we only have two three and four but otherwise you can put here the last row with data and then we will have the next r so the fields are gonna be field one is going to be range a an R dot value field 2 so I'm just copy pasting that field 2 is going to be column B and field three is going to be column three column column C which is the ID and now that we have the data and the URL we can send the HTTP request but for that we need to First declare HTTP request for example as a new Microsoft XML to dot server XML HTTP and now inside of the loop for every row with data we're going to say with HTTP request dot open a post request so we're gonna post a request to the URL and asynchronous set to false and the next thing we need to do here is to set the response header so I'm gonna just copy paste this code to set the request header and specifying the content type as this here and finally we can send the request and end the width statement here so let's run it and see how it works I'm gonna put from two to two so only for Row 2 as a test and if we run it it should have added row two to the spreadsheet and if we go back to the spreadsheet we see there is a form response just at this time that is empty so something something went wrong and that's because I forgot to add the equal sign so this the correct URL has an equal after the entry ID for all three so equal equal and equal despite we have here two dots in the macro we need to put the equal sign so so the URL is built with the equal sign so if we if we run the macro now and go back to to the spreadsheet now we see we have our entry and if we want to upload all the data from two to four run the macro is gonna go three times and upload each entry through the Google form so that's how we upload or export data from Excel to Google Sheets through a Google form linked to the Google spreadsheet and that's how we do it in Excel using vva macros thanks for watching

Channel: Excel Macro Mania

Views: 26,022

Keywords: export to google sheets, upload to google sheets, excel data to google sheets, excel data export, excel data google form, upload through google form, from excel to google sheets

Id: bebQ-Rws0rs

Length: 10min 57sec (657 seconds)

Published: Sat Sep 24 2022