How to Connect a Database to Google Sheets

Let’s say you produce millions of rows of customer data, including your customer’s location, number of orders, total spend, etc.

Yet you can’t upload all of it to Google Sheets because of its 10 million cell limitation. You really need a database, but who has the time to learn how to set on up and query it using SQL?

So, how do you extract meaningful insights from the transaction data, especially if you’re not familiar with databases and don’t possess technical skills?

You can just use Gigasheet as a database, and import specific data to Google Sheets based on your specific needs. That sounds way easier!

Why Use Gigasheet as a Spreadsheet Database?

Gigasheet has a billion-row capacity making it the perfect system to handle very large datasets. You can use it as a database but with the interface of a spreadsheet - an interface you’re already familiar with. Let's call it a spreadsheet database!

The platform offers seamless integration with BI tools and spreadsheet platforms through a live data URL for a smooth transfer between the two ecosystems. 

So, you can say bye-bye to downloading, uploading, and copy-pasting huge datasets manually.

Gigasheet's URL for BI & Spreadsheets feature features a dynamic, refreshable data connection so that your analysis and reports are always based on the latest data, saving you time and simplifying data-driven decisions. 

This means that you can easily load a specific customer transactions in Google Sheets with Gigasheet acting as a powerful, actionable database loaded with customer insights. 

Basically, Gigasheet is a big data spreadsheet platform that can handle massive amounts of data. 

And as you scale, you don’t have to worry about outgrowing your tech stack. 

And if, like me, you’re not a programmer by trade or don’t have the technical skills, Gigasheet is your no-code, intuitive, and automated data powerhouse. 

Gigasheet How to Import Your Database in Google Sheets

Let’s say you want to run a spring campaign, where you offer your most premium customers (sales > $20,000) a massive 50% discount. Let’s set the criteria:

• Customers with >$20,000 in sales till date: 50% discount
• Customers with $10,000 - $20,000 in sales till date: 25% discount
• Customers with <$10,000 in sales till date: 10% discount

You have created a simple tool for your sales team using Google Sheets. They simply select the customer ID from a drop down and the transaction history is summarized.  Since Google sheets can't load all of the data at once, it will refresh the data with just a single customer's transactions.

You can load your customer data (including their purchase histories) inside Gigasheet and use it as a database. Then, you can connect it with Google Sheets using Gigasheet’s live data URL feature. This will let you seamlessly transfer the required data from Gigasheet to Google Sheets.

With a simple script, we can filter Gigasheet to the selected customer and only their transactions will return, and our sales tool will work!

Step1. Create the Sales Tool in Google Sheets

We set up our Google Sheets such that, when you select the Customer ID from the drop-down menu, it’ll fetch you all their details from a second tab that we named a Data Tab. Details include customer location, total spend, total dates purchased, average spend, etc. 

‍

Step 2. Load the Transaction Data into Gigasheet

Upload all 2.6 million rows of data into Gigasheet. No way was this ever going to fit in Google sheets!

Step 3. Enable the Live URL that Will Send Data to Google Sheets

This is as easy as flipping a switch! The URL for BI & Spreadsheets will pass the current sheet state along. Thus, if we apply a filter to Gigasheet, only the filtered data will be passed to Google Sheets. This is what we will do since Google Sheets can't handle all 2.6M rows.

Step 3. Add a Script to Google Sheets to Filter the Gigasheet Data based on Customer ID

By hitting Extensions > Apps Scripts, you can add code to Google sheets. We are going to use the Gigasheet API to apply a filter to our sheet using the customer ID selected in our sales tool. It sounds complicated, but it does not require a lot of code. You can modify the code below to make a version that works for you. Or ask AI to write it (seriously)!

Here is the step where it imports data from the Live URL.

Step 4. The Filtered Data Will Load into the Data Tab

When you select a customer ID from the drop-down menu, Google Sheets will first fetch information for that specific customer in the data tab of the Sheet from Gigasheet. This will then be summarized on the main tab of our applicaton.

Step 5. Profit!

Since Google Sheets fetches only specific information as requested, you don’t have to worry about overloading the platform with massive data and going beyond its 10 million cell limitations.

Your sales team can look up transaction history on a customer by customer basis and offer them applicable discounts. Deals will roll in!

Market Like a Ninja with Gigasheet!

Who needs a database when you can quickly and easily fetch and analyze huge amounts of customer information with Gigasheet by integrating it with Google Sheets. 

Gigasheet handles large datasets without complex coding, so it's perfect for growing ecommerce businesses without in-house tech teams.

The best part? We scale with your business, keeping your data management systems efficient and effective. 

Contact our sales team to talk about your specific requirements.

‍

As promised, Our Script

_{function atEdit(e) {}

  _{// Check if the event object is defined and if the event occurred in a spreadsheet}

    _{Logger.log(JSON.stringify(e));}

    _{var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();}

    _{var range = e.range; // Get the range that was edited}    

    _{var editedCell = range.getA1Notation(); // Get the A1 notation of the edited cell}

    _{Logger.log(editedCell);}

        _{// Check if the edited cell is B1}

    _{if (sheet.getName() == "Application" && editedCell == "B1") {}

      _{// Run your custom function here}

      _{updateSheetAndPassToGigasheet();}

    _}

  _}

_{function updateSheetAndPassToGigasheet() {}

  _{// Get the active Google Sheets spreadsheet}

  _{var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();}

    _{// Specify the sheet name and the cell to update}

  _{var cellValue = sheet.getRange("B1").getValue();}

    _{var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Tab");}

  _{var importDataCell = dataSheet.getRange("A1"); // Change A1 to the cell containing the IMPORTDATA function}

  _{importDataCell.clear({contentsOnly: true, formatOnly: false});}

  _{importDataCell.setValue("Updating...");}

  _{Logger.log("got this far");}

    _{// Construct the URL for Gigasheet API with the updated value in the payload}

  _{var apiUrl = "https://api.gigasheet.com/client-state/06287dc2_411e_4700_9d73_c9274556de69/filter-model";}

  _{var payload = {"filterModel":{"_cnf_":[[{"colId":"B","filter":[cellValue],"filterType":"text","type":"equalsAny"}]]},"alwaysConfirm":true,"alwaysConfirmChangeCase":true,"alwaysConfirmDeleteRow":true,"isPivotEnabled":false,"expandedNodes":[],"reduxSavedFilterHandle":""};}

  _{Logger.log(payload);}

  _{var options = {}

    _{method: "put",}

    _{contentType: "application/json",}

    _{headers: {}

    _{"accept": "application/json",}

    _{"X-GIGASHEET-TOKEN": "[GIGASHEET API KEY]"}

_},

    _{payload: JSON.stringify(payload)}

  _};

    _{// Send the PUT request to Gigasheet API}

  _{var response = UrlFetchApp.fetch(apiUrl, options);}

  _{Logger.log(response)}

  _{// Utilities.sleep(1500);}

  _{// Check the response status}

  _{if (response.getResponseCode() == 200) {}

    _{// Successful request}

    _{Logger.log("Value successfully updated in Google Sheets and passed to Gigasheet API.");}

  _{} else {}

    _{// Request failed}

      _{Logger.log("Failed to update value or pass to Gigasheet API. Status code: " + response.getResponseCode());}

      _{Logger.log("Response: " + response.getContentText());}

  _}

_‍

_{importDataCell.setFormula('=IMPORTDATA("https://api.gigasheet.com/dataset/06287dc2_411e_4700_9d73_c9274556de69/liveshare/be927a59_20b9_477e_a8ad_46c5544d85dd")');}

_}

‍