CODEWITHSUNDEEP
javascriptreactjsgoogle-sheetsnext.js
Monu Patil
Monu Patil

Reputation: 355

didn't write data in google sheet in next js

am new stuck with a problem, am not able to write data in google sheet , read data from google sheet is working fine, can anyone try to tell me where am wrong please try to fix my code. If you have any question please free feel to ask any time.

Error Image

Error

sheet.js

import { google } from 'googleapis';

export async function getDataFromSheets() {
  try {

    const target = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
    const jwt = new google.auth.JWT(
      process.env.GOOGLE_SHEETS_CLIENT_EMAIL,
      null,
      (process.env.GOOGLE_SHEETS_PRIVATE_KEY || '').replace(/\\n/g, '\n'),
      target
    );

    const sheets = google.sheets({ version: 'v4', auth: jwt });
    const response = await sheets.spreadsheets.values.get({
      spreadsheetId: process.env.SPREADSHEET_ID,
      range: 'sheet'
    });

    const rows = response.data.values;

    const res = await sheets.spreadsheets.values.append({
      spreadsheetId: process.env.SPREADSHEET_ID,
      range: 'sheet',
      valueInputOption: 'USER_ENTERED',
      requestBody: {
        values: [['']],
      }
    })

console.log(res)


    if (rows.length) {
      return rows.map((row) => ({
        title: row[0],
        description: row[1],
      }));
    }
  } catch (err) {
    console.log(err);
  }

  return [];
}

index.js

This is the index.js file. where we write our output code.

import Head from 'next/head'
import Image from 'next/image'
import styles from '../styles/Home.module.css'

import { getDataFromSheets } from './libs/sheets'

export default function Home({ data }) {
  return (
    <div className={styles.container}>
      <Head>
        <title>Nextsheet 💩</title>
        <meta
          name="description"
          content="Connecting NextJS with Google Spreadsheets as Database"
        />
        <link rel="icon" href="/favicon.ico" />
      </Head>

      <main>
        <h1>Welcome to Nextsheet 💩</h1>
        <p>Connecting NextJS with Google Spreadsheets as Database</p>
        <ul>
          {data && data.length ? (
            data.map((item) => (
              <li key={item}>
                {item.title} - {item.description}
              </li>
            ))
          ) : (
            <li>Error: do not forget to setup your env variables 👇</li>
          )}
        </ul>
      </main>
    </div>
  )
}
export async function getStaticProps(context) {

  const sheet = await getDataFromSheets();


  return {
    props: {
      data: sheet.slice(0, sheet.length), // remove sheet header
    },
    revalidate: 1, // In seconds
  };
}

Upvotes: 0

Views: 273

Answers (1)

Tanaike
Tanaike

Reputation: 201513

When I saw your script, it seems that you use the scope of https://www.googleapis.com/auth/spreadsheets.readonly. In this case, the values can be retrieved. But, when the values are put, such an error showing in the image occurs. So how about the following modification?

From:

const target = ['https://www.googleapis.com/auth/spreadsheets.readonly'];

To:

const target = ['https://www.googleapis.com/auth/spreadsheets'];

Reference

Upvotes: 1

Related Questions