Using Google Sheets API create a quick MVP app in react native
Create a react native app, that uses a Google Sheet as the database to perform read operations.
Disclaimer — Using a Google Sheet as a database for a React Native app, is generally not recommended as you cannot do things like perform queries to fetch data and plus there is also an API calling quota.
But there can be scenarios where we can use it. For example, I had a client with some videos on his youtube channel and he wanted to have those videos in the app. He didn’t have a big budget to get servers etc and I had firebase in mind but he also needed a place where he could update/add new links and firebase was a bit technical for the client to add/modify data. So, within the budget, it felt like a good idea to use a Google sheet as the backend which will be used to just read the data from and we can display the videos as we want and it would be user-friendly for the client to add/modify data and it’ll reflect on the app in real-time.
It’s also very helpful if you want to quickly create an MVP for a client and give them a place to modify the data.
What are we going to build?
We’re going to build a small app, where we’ll use videos from this channel https://www.youtube.com/@cokestudio because they have about 14 seasons and we can format them accordingly so that we can have something like this:
So, create a new react-native project, and let’s get started.
Create a UI:
I’m not going to show how to do UI but based on the wireframe above I created a UI that looks like this:
The video component is simple:
import React from 'react';
import { Image, Text, View, StyleSheet, TouchableOpacity, ImageBackground } from 'react-native';
import colors from '../colors'; // Just a file I have all the colors in
function VideoPost({ videoData, maxWidthValue = 280, leftMargin = 16, rightMargin = 4, topMargin = 0 }) {
return (
<View style={{ width: maxWidthValue, marginLeft: leftMargin, marginRight: rightMargin, marginTop: topMargin }}>
<TouchableOpacity onPress={() => {
alert("Video Selected: ", videoData.title);
}}>
<ImageBackground style={styles.thumbImage} imageStyle={{ borderRadius: 16, }} source={{ uri: videoData.thumbnail_url }} >
<Image source={require('../assets/circled_play.png')} />
</ImageBackground>
</TouchableOpacity>
<Text style={[styles.heading, { color: colors.darkBlue }]}>{videoData.title}</Text>
<Text style={styles.description} numberOfLines={2}>{videoData.description}</Text>
</View>
);
}
const styles = StyleSheet.create({
thumbImage: {
shadowColor: "#000",
shadowOffset: {
width: 0,
height: 1,
},
shadowOpacity: 0.20,
shadowRadius: 1.41,
height: 160, resizeMode: 'cover', justifyContent: 'center', alignItems: 'center',
elevation: 2,
},
heading: {
fontSize: 16,
fontWeight: 'bold',
marginTop: 8,
marginBottom: 4,
},
description: {
fontSize: 12,
color: colors.lightGrayText
}
})
export default VideoPost;We’ll get the videoData in props soon.
Google Sheets API Implementation
It all depends on how you’ll arrange your data on the google Sheet. It has to be carefully structured as this would be your main database from the app to get its content.
Create a Google Sheet and make it available to be viewed by anyone with a link and add the emails of the people who you would like to give access to be able to modify/add new data.
I’ve added some data to the sheet:
You can decide to either structured data row-wise or column-wise and I’m going to keep it default i.e. Rows
Go to your cloud console and enable the Google Sheets API:
Select your project or create a new one (and then select it).
After enabling the API we’re going to need credentials that’ll be used by our app to access the sheet. Go to APIs & Services > Credentials in your google cloud console and click on + CREATE CREDENTIALS and then select API Key.
Save the key as we’ll use it shortly.
Now we don’t need to install any library because we’re just reading the data, so we can call the API URL directly and it’ll give us the data in JSON. Here is what the API URL looks like:
https://sheets.googleapis.com/v4/spreadsheets/{Sheet-ID}/values/{Sheet-Name}?valueRenderOption=FORMATTED_VALUE&key={API-Key}
Here we need three things:
- Sheet ID
- Sheet Name
- API Key
We already have the API key and the sheet ID is what you see in the URL, and the sheet name is the name of your sheet as follows:
For me the URL will be:
https://sheets.googleapis.com/v4/spreadsheets/1PFhM-PMpTQLP1RQ97vaXLzGup-yRSyAWgUMSMJkSDHA/values/Sheet1?valueRenderOption=FORMATTED_VALUE&key=[MY_KEY]
When I opened the URL in postman I got the result back:
So, we got the data and you can see in the documentation there are many properties you can use to filter the results you want to get one of those properties is valueRenderOption you can see by default majorDimension is ROWS you can add it in the URL if you want to read data column wise as:
https://sheets.googleapis.com/v4/spreadsheets/1PFhM-PMpTQLP1RQ97vaXLzGup-yRSyAWgUMSMJkSDHA/values/Sheet1?valueRenderOption=FORMATTED_VALUE&majorDimension=COLUMNS&key=[MY_KEY]
I’m going to add proper data to my sheet and then we’ll fetch the data again but with filters. My final data looks like this:
I have added total of four entries, 2 from season 14 and 2 form season 13. Our goal is to call the API and display this data in the UI of the app. Here is the JSON response I’m getting now:
{
"range": "Sheet1!A1:Z1000",
"majorDimension": "ROWS",
"values": [
[
"id",
"title",
"youtube_link",
"thumbnail_url",
"description",
"artist",
"season"
],
[
"1",
"Phir Milenge",
"https://www.youtube.com/watch?v=S5FyS7tQuUw",
"https://i.ytimg.com/vi/S5FyS7tQuUw/maxresdefault.jpg",
"Till we meet again.",
"Faisal Kapadia x Young Stunners",
"14"
],
[
"2",
"Phir Milenge",
"https://www.youtube.com/watch?v=fwJdfyDYhHs",
"https://i.ytimg.com/vi/fwJdfyDYhHs/maxresdefault.jpg",
"Embrace your compassion in an ever changing world and remember, you are not alone.\n#YeDunya\n#SoundOfTheNation\n#CokeStudio14",
"Karakoram x Talha Anjum x Faris Shafi",
"14"
],
[
"3",
"Na Tutteya Ve",
"https://www.youtube.com/watch?v=kezdQcNcDCs",
"https://i.ytimg.com/vi/kezdQcNcDCs/maxresdefault.jpg",
"Na Tutteya Ve, listen to the story of resilience and an unbroken heart sung by Meesha Shafi, Sanam Marvi, Fariha Pervez, Zara Madani, Wajiha Naqvi and Sehar Gul Khan ",
"Season Opener",
"13"
],
[
"4",
"Dil Khirki",
"https://www.youtube.com/watch?v=8QIaBikvGoQ",
"https://i.ytimg.com/vi/8QIaBikvGoQ/maxresdefault.jpg",
"Dil Khirki, open the window to your heart and let the indie vibe of #DilKhirki take you away!",
"Mehdi Maloof",
"13"
]
]
}Looking at the data I see that I have arrays. The response[0] are the id and the rest are values. So, I got ChatGPT to write this javascript function which will convert this array into an object.
I installed Axios yarn add axios and then wrote this function in a useEffect so that it gets called when the screen loads.
So my business logic is:
const [allSeriesData, setAllSeriesData] = useState([]);
function getAllSeries() {
const SHEET_ID = '1PFhM-PMpTQLP1RQ97vaXLzGup-yRSyAWgUMSMJkSDHA'
const SHEET_NAME = 'Sheet1'
const API_KEY = 'MY_API_KEY'
const url = `https://sheets.googleapis.com/v4/spreadsheets/${SHEET_ID}/values/${SHEET_NAME}?valueRenderOption=FORMATTED_VALUE&key=${API_KEY}`
axios.get(url)
.then(function (response) {
// handle success
formatResponse(response.data);
})
.catch(function (error) {
// handle error
onError(error);
})
.finally(function () {
// always executed
console.log('ALL DONE LOADING DATA');
});
}
function formatResponse(response) {
const keys = response.values[0];
const data = response.values.slice(1);
const obj = data.map(arr => Object.assign({}, ...keys.map((k, i) => ({ [k]: arr[i] }))));
setAllSeriesData(obj);
}
function onError(error) {
console.error(error);
}
useEffect(() => {
getAllSeries();
}, []);The allSeriesData will be the formatted response we’ll get from our API. The formatResponse function is what does the magic of formatting and I generated it with the help of ChatGPT.
When I loaded my UI to pick from the data I did:
<ScrollView horizontal={true} showsHorizontalScrollIndicator={false}>
{allSeriesData.map(series => <VideoPost key={series.id} videoData={series} />)}
</ScrollView>
I got all four in one row. Now for the sake of this tutorial as I have only two seasons so I’ll write another javascript function that’ll do the magic of separating the data based on seasons so that we can render then in different sections. Again thanks to ChatGPT I updated the formatData code to:
function formatData(response) {
const keys = response.values[0];
const data = response.values.slice(1);
const obj = data.map(arr => Object.assign({}, ...keys.map((k, i) => ({ [k]: arr[i] }))));
console.log(obj);
const seasons = new Set();
obj.forEach(item => {
if (item.season) {
seasons.add(item.season);
}
});
const final_data = {};
for (const season of seasons) {
final_data[season] = obj.filter(item => item.season === season).map(item => ({ ...item }));
}
// const season13 = obj.filter(item => item.season === '13');
// const season14 = obj.filter(item => item.season === '14');
setAllSeriesData(final_data);
}The above final_data will be like this:
Then I did simple UI rendering with looping. I have made a component named Section that is basically a section that has all the VideoPost in it. So I did something like:
// In Home.js
<ScrollView showsVerticalScrollIndicator={false}>
{allSeriesData && Object.keys(allSeriesData).map(season => <Section seriesTitle={`Season ${season}`} seriesData={allSeriesData[season]} nav={props.navigation} />)}
</ScrollView>// Section.js
<ScrollView horizontal={true} showsHorizontalScrollIndicator={false}>
{seriesData.map(series => <VideoPost key={series.id} videoData={series} nav={nav} />)}
</ScrollView>
Now it started to look like an app. Now season 14 should be above season 13 and we can easily do that by first ensuring that the season field on our Google Sheet is of number type and then we can write a simple javascript sort function to sort it out.
Next, to play these videos we already have the link. You can use any library or even webview to open the link. But that’s all on how you can use google sheets as a backend.
Some use-cases/ideas I can think of using this approach:
- If you have to craft an MVP for a client and be able to show changes in real-time this would be a fast approach.
- If you want to test an app for users you can totally use this feature.
- Make an app for your friends where you can keep track of any upcoming plans. You can update the sheet and everyone can see it on the custom-made app. You can use a filter to filter out what is the responsibility of each person to bring for the plan.
- Make a grocery list app that your partner can update and you can see it on the app.
As always if you find this helpful share and press the 👏🏻 button so that others can find it too. If you see a typo feel free to highlight it or if you’re stuck drop a comment and I’ll try my best to help you.
All my tutorials are free but if you feel like supporting you can buymeacoffee.com/chaudhrytalha
Happy Coding 👨🏻‍💻
