Appsmith recently released a new Google Sheets integration with a huge list of options, including a method to Bulk Insert Rows to an existing sheet.

☝️ So let's say you want to take the response data from a GET request, transform the data and send it to Google Sheets:

For this example, I will be using data from the RandomUser.me API to build on my previous Appsmith sample app . However, I'm going to try to break down the steps so this post can be applied to saving data from any API to Google Sheets.

✅ Project Requirements

• Download multiple records of JSON data from an API
• Transform/flatten the data
• Add modified records as new rows in Google Sheets

👉 Prerequisites

1. Existing GET request is already configured in Appsmith
2. Destination spreadsheet is setup with desired column names

Sample Request

GET: https://randomuser.me/api/?seed=foobar&results=50&nat=us

The response data has several nested fields, but we want to 'flatten' the data and only send certain values to the spreadsheet.

⚙️ Setup

1. Add a new Google Sheets Data Source and authorize it for your Google account.
2. Add a new API and choose the method Bulk Insert Rows.
3. Copy/paste in the function below

   {{
   get_users.data.results.map(
   u => {
    return {'name':u.name.first, 'email':u.email, 'id':u.id.value};
   }
   )
   }}
   

4. DEPLOY! 🚀

The Bulk Insert Rows method expects an array of objects, with keys that match the the sheet's column names:

[{key:value},...]

And the map() method returns an array. So we can map! over the get_users data and build our rows.

That's it! 😁

It really is that easy to transform and push data to Google Sheets from an API response!

---

This is just the beginning in a series where I will cover transforming the data in more detail, filtering and sorting the results, and eventually, syncing a Google Sheet with an API endpoint on a timer.

Thanks for reading!