Database & APIs
M
Marcello

Assistance Required: Sending Data from Google Sheets to Firestore Database

Hello FlutterFlow Community,

I'm currently working on a project where I need to transfer data from Google Sheets to my Firestore database. I've successfully made a GET request to the Google Sheets API and received the JSON response. The response includes an array of arrays, where each sub-array represents a row from the spreadsheet, and the first sub-array contains the column headers.

My JSON structure from the Google Sheets API is as follows:
{

"range": "room_nights_revenue!A1:Z1000",

"majorDimension": "ROWS",

"values": [

["Date", "Day of Week", "Room Nights", ...], // Headers

["1-Nov-2023", "Wednesday", "58", ...], // First row

["2-Nov-2023", "Thursday", "57", ...], // Second row

}

I'm trying to map these values to my Firestore database, where each row should become a new document in my RoomNightsReport collection.

Here's where I'm facing challenges:

  1. Properly indexing and mapping the keys from the JSON to Firestore fields, including type conversions from string to the appropriate data types (e.g., converting date strings to DateTime objects, stripping currency symbols, and converting percentages to doubles).

  2. Setting up the correct loop in FlutterFlow to iterate over each row of data and create a new Firestore document for each one.

  3. Dealing with conditional logic in FlutterFlow to only execute the loop if the API call is successful.

What I've done right so far:

  • The API call to Google Sheets is working, and I'm getting the expected JSON response.

  • I've set up my Firestore RoomNightsReport collection with the correct fields.

  • I'm using FlutterFlow's action flow to handle the API call response and attempting to map the data to Firestore.

I would greatly appreciate any guidance on how to proceed with the mapping and data conversion within FlutterFlow, or if there's a better approach to accomplish this task. Additionally, if there are any built-in functions or custom scripts that could facilitate this process, I'd be keen to learn more about them.

Thank you in advance for your help!

1