How to take JSON information and give it to a React component?

Question

The fetch request (from Google Sheets) returns stats, which is an array of arrays. In stats is a list of teams with their wins and losses (one of them looks like this ['Cannons', '4', '2'], like you see in teams. I want stats to automatically fill in my teams array or give it to the data prop in Table. Thank you for your help!

const getSheetValues = async () => {
    const request = await fetch(`https://sheets.googleapis.com/v4/spreadsheets/${SHEET_ID}/values/A1:C7`,
    {
        headers: {
            "Content-Type": "application/json",
            Authorization: `Bearer ${ACCESS_TOKEN}`
        }
    });
    const stats = await request.json();
    console.log(stats);
    return stats;
}

const teams = [
    {
        team: 'Cannons',
        wins: 4,
        loss: 2
    },
    {
        team: 'Rhythm',
        wins: 1,
        loss: 5
    }
]


const Standings = () => {
    return (
        <div className='cont'>
            <div className='standings'>
                <Table columns={columns} data={teams} className='tbl' />
            </div>
        </div>
    )
}

unclear what you want, do you want to add stats to teams array ? or do you want to just use info from teams array in table? — dippas, Jul 12 '22 at 21:37
@dippas either one works, I want the end result would be populating my table with the stats from the Google Sheets. I suspect that adding stats to teams array would be easier. (The data inside teams array is just a placeholder for right now) — huardy, Jul 12 '22 at 21:41
@dippas this is what it looks like when I do console.log(stats): ['Teams', 'Wins', 'Loss'] ['Cannons', '4', '2'] ['Racers', '6', '0'] and so on. — huardy, Jul 12 '22 at 21:49
You have to use state to achieve that. Please see some examples how to use fetch with React. — Konrad, Jul 12 '22 at 21:52
@Pipe I'm not using it anywhere right now, I don't know where to use it — huardy, Jul 13 '22 at 01:04

score 0 · Answer 1 · answered Jul 13 '22 at 10:44

I assume that your code is still an idea. But no problem, let's do it.

Here we will use NextJS to fetch the data from Google Sheet and pass the result via an API endpoint to the client-side. Keep in mind that it is a bad idea to show your ACCESS_TOKEN off publicly. So never put the ACCESS_TOKEN at your client-side app.

Server Side

You can fetch the information from googleapis using two ways:

# /api/spreadsheets/[id].js

export default async function handler(req, res) {
  const { id } = req.query;

  const ACCESS_TOKEN = "yaXX.A0XXXXXXXXXX0-UsdRJ3DmO_sOrM-gexCZXXXXXXXXXX9bQ-VpOF_nETNoT-yWgIaj4LgJi5NIdoXXXXXXXXXXqiE0XYuOlVvIpXD3cw_fQDhXXXXXXXXXXP9Gs-VVjHnKXXXXXXXXXX7249iEHJ2-2x8PiXXXXXXXXXXBVEFTQVRBU0ZRRTY1ZHI4MGXXXXXXXXXXYURwYUdOTjh5Z0NrQQ0163";

  const spreadsheet = await fetch(
    `https://sheets.googleapis.com/v4/spreadsheets/${id}/values/A1:C7`,
    {
      headers: {
        "Content-Type": "application/json",
        Authorization: `Bearer ${ACCESS_TOKEN}`,
      },
    }
  );

  const response = await spreadsheet.json();

  res.status(200).json(response.values);
}

or

# /api/spreadsheets/[id].js

import { google } from "googleapis";

const CLIENT_ID = "840XXXXXXXXX-o5md8ddhsXXXXXXXXXi9fhu40v5n2XX2.apps.googleusercontent.com";
const CLIENT_SECRET = "GOCSPX-XXXXXX_-1-heXXXXXXAzW_7mr_1_";
const REDIRECT_URI = "https://developers.google.com/oauthplayground";

export default async function handler(req, res) {
  const { id } = req.query;

  const oauth2Client = new google.auth.OAuth2(
    CLIENT_ID,
    CLIENT_SECRET,
    REDIRECT_URI
  );

  oauth2Client.setCredentials({
    refresh_token: "1//0XXXXXXXXXKS_CgYIXXXXXXXXXXF-L9Ir_MTdXXXXXXZ1V9-ZlBFXXXXdz3ywAV9XXXXXXXXXXMr6Jm9O8mvLeLXXXX8iaPSVKAc",
  });

  const sheets = google.sheets("v4");

  const spreadsheets = await sheets.spreadsheets.get({
    spreadsheetId: id,
    auth: oauth2Client,
    includeGridData: true,
  });

  const datas = spreadsheets.data.sheets[0].data[0].rowData.map((row) => {
    return row.values.map((value) => value.formattedValue);
  });

  res.status(200).json(datas);
}

and the response of both approaches will be the same:

[
  [ 'Teams', 'Wins', 'Loss' ],
  [ 'Cannons', '4', '2' ],
  [ 'Racers', '6', '0' ]
]

but keep in mind that the first approach will not work when the ACCESS_TOKEN is expired. So, please use the last one. You can read here on how to get the REFRESH_TOKEN.

Next, at the client-side. Here we use antd Table component to render the data.

import { useEffect, useState } from "react";
import { Table } from "antd";

export default function Home() {
  const [columns, setColumns] = useState();
  const [data, setData] = useState();

  useEffect(() => {
    fetch("api/spreadsheets/1G8XS3aseeaIqEURr1Bt0rvogBb-hgqvAglw83b5qxSA")
      .then((res) => res.json())
      .then(
        (response) => {
          const columnNames = response.shift();
          setColumns(
            columnNames.map((name) => {
              // to follow antd column properties
              return {
                title: name,
                dataIndex: name.toLowerCase(),
                key: name.toLowerCase(),
              };
            })
          );

          setData(
            response.map((row, i) => {
              // to follow antd dataSource properties
              return columnNames.reduce((a, v, j) => {
                a.key = `${i + 1}`;
                a[v.toLowerCase()] = row[j];
                return a;
              }, {});
            })
          );
        },
        (error) => {
          console.log(error);
        }
      );
  }, []);

  return (
    <div>
      <div>App</div>
      <Table dataSource={data} columns={columns} />
    </div>
  );
}

Done.

How to take JSON information and give it to a React component?

1 Answers1

Server Side