How to Migrate Data From Sheets to Postgresql

Goal

Prerequisites

Overview

1. Create a Google Sheets Datasource 

   Appsmith makes it extremely easy to connect to your existing data. To connect to your data from your Google Sheets, go to the Appsmith and create a new app (or use an existing app), then go to the Data section in the left menu, then create a new Datasource and select Google Sheets. 

   

   Then select how you want Appsmith to access your Google Sheets content, granular to the given sheets all or your content, then after your selection, click on Save & Authorize, and authenticate with your Google Account 

   

   After authenticating, you will be prompted with your Google Sheets to select the one you want to import, choose the one you want, and click "Select". In my case, I want to import a list of Events I will be migrating to a PostgreSQL Database. 

   

   After you select it, you will be able to see your newly created Datasource and sneak a peek at the content in it; now, we can write queries against this data source. 

   

   Now, let's connect our PostgreSQL data source, which will be the destination of the data.  

    

2. Create a PostgreSQL Datasource 

   Now, let's add the PostgreSQL Database. Go to the Data section in the left menu, then create a new Datasource and select PostgreSQL. 

   

   Enter your database credentials and click Save. You can also test that the connection works by clicking "Test Connection" before saving. 

   

   Once you save, you will see the Datasource and sneak a peek at the data on that Database. 

   

   We can see that in the PostgreSQL Database, we have 1 event; this is where we will write the migration logic to move all events from sheets to PostgreSQL. 

   Now to prepare for the migration script, let's add a new Query for this data source to insert a new Event in the database; this is the Query we will call when writing the migration logic using the following statement: 

   INSERT INTO public."event" ("title", "description", "category", "speaker", "topic", "datetimeutc", "excerpt") 
   VALUES ({{this.params.title}}, {{this.params.description}}, {{this.params.category}}, {{this.params.speaker}}, {{this.params.topic}}, {{this.params.datetimeutc}}, {{this.params.excerpt}})

   In this case, this Insert query basically uses this.params values to insert the Event in the database which means we'll pass parameters to this query to make it dynamic, name it Insert_Event. 

   

3. Displaying both Datasouces in the UI 

   Now, let's build a page where we can see both datasources, then we will write a custom action that will migrate the data. Go to the Editor section on the left menu and add a new Table widget to the canvas. 

   

    

   Then click on the "Connect Data" button to show the sheet's data in this table, and on the right menu, select the Sheets data source. 

   

   Then click on "Connect Data" and this will save the table binding. Now drag and drop a second Table and do the same with the PostgreSQL database. As a result, you will have a page showing both data, the Source sheet and the destination PostgreSQL like this: 

   

   As you can see, we have 20 events in the sheet and just 1 in the destination database. Now, let's write Javascript logic to migrate the data over. 

4. Write the Migration Logic

   Now let's add a new Javascript object by going to the JS tab in the Editor, and add a new JS Object and giving it the name you want, mine will be called "Migration" 

   

   In that file, add the following piece of code: 

   export default {
   	migrateEvents: async() => {
   		
   		const migrateAllEvents = Total_record_Tech_Conference_Events_20241.data.map((event) => {
         Insert_Event.run({
   				title: event.Title,
   				description: event.Description,  
   				category: event.Category,
   				speaker: event.Speaker,
   				topic: event.Topic,
   				datetimeutc: event["Date and Time"], 
   				excerpt: event.Excerpt
   			})
       });
   		
   		try {
         const results = await Promise.all(migrateAllEvents);
         showAlert("Events Migrated Succesfully", "success");
   			Select_public_event1.run()
   			return results
       } catch (error) {
         console.error("Bulk Migration of Events Failed", error);
         throw error;
       }
   
   	}
   }

   It should look like this: 

   

   What is going on in this piece of code? 

   Basically we are defining an async function called migrateEvents and in this function, we will go over each event in the seets using a map function: 

   Total_record_Tech_Conference_Events_20241.data.map

   inside that map function, we will call the Insert_Event query that we defined in step 2, and we will pass the parameters that Query expects using each event in the .map function. 

    Insert_Event.run({
   				title: event.Title,
   				description: event.Description,  
   				category: event.Category,
   				speaker: event.Speaker,
   				topic: event.Topic,
   				datetimeutc: event["Date and Time"], 
   				excerpt: event.Excerpt
   			})

   As you can see, we are passing the parameters using the event object, which contains the event data on each pass of the map function, inserting that event in the PostgreSQL database. 

   In this function, you can do any data manipulation, custom logic, or modification as you desire. In my case, I'm passing the data as it comes from the sheet, but if you need to edit it this is where you would do it. 

   Now, let's add a UI button that triggers this functionality: 

   

   And bind the Click event to the JS function we just wrote: 

   

    

   And that's it; now we can deploy this app and click the button, which will migrate the data and display a confirmation toast: 

   

Conclusion