What is the process for making changes to Google Sheet information?

Encountering an issue when trying to update data in a Google sheet using Next.js. The error message ReferenceError: row is not defined keeps popping up and I can't figure out where I'm going wrong. Any help in resolving this error would be greatly appreciated. Feel free to ask any questions if needed, don't hesitate.

https://i.sstatic.net/Zm80A.png

sheet.js

This file contains my updated code.

export async function updateDataFromSheets() {
  try {

    const target = ['https://www.googleapis.com/auth/spreadsheets'];
    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 });

    let values = [
      [
        '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="f6868497809f989d979a93cececfb6919b979f9ad895999b">[email protected]</a>',
        'pravin kale',
        'BE Comp',
        'Success'
      ],
      // Additional rows ...
    ];
    const resource = {
      values,
    };
    const response = await sheets.spreadsheets.values.update({
      spreadsheetId: process.env.SPREADSHEET_ID,
      range: 'sheet',
      valueInputOption: row[6],
      resource
    }, (err, result) => {
      if (err) {
        // Handle error
        console.log(err);
      } else {
        console.log('%d cells updated.', result.updatedCells);
      }
    });
  } catch (err) {
    console.log(err);
  }

  return [];
}

index.js

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.Email} - {item.Subject} - {item.Body} - {item.Status}
              </li>
            ))
          ) : (
            <li>Error: do not forget to setup your env variables 👇</li>
          )}
        </ul>
      </main>
    </div>
  )
}
export async function getStaticProps() {

  const sheet = await getDataFromSheets();

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

dynamicid.js

import { getDataFromSheets } from '../../../libs/sheets';
import sendmail from '../../../libs/ses/sendmail';
import { updateDataFromSheets } from '../../../libs/sheets';

    export default function handler(req, res) {
      var data;
      getDataFromSheets()
        .then(sheet => {
          data = sheet.length
          for (var i = 1; i < data; i++) {
            if (sheet[i].Status != 'Success') {
              sendmail(sheet[i].Email, sheet[i].Subject, sheet[i].Body)
            }
          }
          res.json({ status: 'success', message: 'email has been sent' })
        })
        .catch(err => {
          console.log(err)
          res.json({ status: 'fail', error: err })
        })
    
      updateDataFromSheets();
    }

Answer â„–1

It appears that the error message you are receiving is related to the line valueInputOption: row[6], in your script. The variable row seems to not be declared, causing this issue to occur. It's important to specify a valid value for valueInputOption, such as RAW or USER_ENTERED. In this scenario, I would recommend using USER_ENTERED.

Original Code:

valueInputOption: row[6],

Updated Code:

valueInputOption: "USER_ENTERED",

Note:

  • I have tested your modified script with a sample Spreadsheet shared via email with the service account and confirmed it works correctly. However, if the Spreadsheet is inaccessible to the service account, an error may still occur. Please keep this in mind.

Reference:

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Newbie: Troubleshooting Vue Errors - "Vue is not recognized"

I'm currently at the beginning stages of learning Vue and am practicing implementing it. However, I keep encountering the errors "vue was used before it was defined" and "Vue is not defined". Below are excerpts from my HTML and JS files for reference. ...

Tips for retrieving selected items in an array object

Within my UI, I have a select field that, on change, populates corresponding data in a div with nested ul and li elements. What I am attempting to achieve is to convert the selected items within the list (which include checkboxes) into an object of arrays ...

In Next js, switching to a different route may require a hard refresh in order to update the React state

Currently using Next 14.1.4 in conjunction with React 18. Encountering an issue where updating state without a hard refresh seems impossible. It appears that the state's value is being cached client-side, and nothing I've tried has been successfu ...

Clickable element to change the display length of various lists

I am working on a project where I have lists of checkboxes as filters. Some of these lists are quite long, so I want to be able to toggle them to either a specified length or the full length for better user experience. I have implemented a solution, but th ...

EasyWaySaveArchive in ninja training - mastering the art of retrieving components

Recently started learning about dojo and JavaScript in general. I am currently working on a code snippet that requires a button to change based on the result of a php database query. The PHP script is already complete, and the code I am using so far looks ...

Delivering a Captivating JavaScript Pop-Up upon Page Loading

I have a simple pop up window (with no content) that triggers with the 'onclick' event. How can I modify the code below to make the popup appear automatically when the page loads? <head> <title>Popup Display</title> < ...

The search functionality for the MongoDB module is not functioning properly within my Next.js application

Working on my nextjs application, I have encountered an issue with using the mongodb node module to find all documents in one of my collections. Despite successful usage of .findOne and .updateOne for other pages like login and password reset, when I use . ...

Using the splice method on an array is only effective when done in a specific sequence

I've encountered a small issue. I have checkboxes that correspond to different divs, and when checked, the name of the div is sent to the server. However, when unchecking the checkboxes in a specific order, the array doesn't update correctly. $ ...

Tips on retrieving a value nested within 3 layers in Angular

In my Angular application, I have three components - A, B, and C. Component A serves as the main component, Component B is a smaller section nested inside A, and Component C represents a modal dialog. The template code for Component A looks something like ...

invoke a function upon successful completion of an ajax call in a datatable

Can we trigger a JavaScript function after a successful AJAX call in a datatable? Here is the code I am attempting to use: var dataTable = $('#app-config').dataTable( { "bAutoWidth": false, ...

Ways to conceal a child div element without using any specific ID reference

I encountered an issue where I need to conceal all divs within a parent div except the first one. The challenge is that these divs do not possess any unique identifiers. Is there a way to achieve this task using CSS or pure JavaScript? <div role="list ...

Encountering issues with bidirectional data binding functionality

I have integrated a pagination component from ng-bootstrap into a generic component that includes a select dropdown to choose the number of items per page. I triggered an event from this generic component and caught it in the parent component (member-list. ...

Ordering ng-repeat in AngularJS using a separate arrayDiscover how to easily order your

Imagine I have an array containing keys in a specific order orderedItems=["apple","banana","orange]; and there is a JSON object that I want to display using ng-repeat but following the sequence specified in the array: {"fruits": { "apple":{ ...

Error message: Attempting to access the property '_layerAdd' of an undefined object in the context of vue and leaflet integration

Encountering an issue while attempting to include a feature group on my map: TypeError: Cannot read property '_layerAdd' of undefined The section of code causing the error: <l-map id="mapid" :zoom="zoom" :center="center" /> var ...

Items added to a form using jQuery will not be submitted when the form is posted

Issues with posting data from dynamically appended options in a form select using jQuery have been noticed. When an appended option is selected and the form is submitted, the value does not get posted (in this case, in an email template). Adding another no ...

What is the best way to establish a header in the login route that allows the browser to remember the user's login status?

I have successfully implemented a user login backend and everything seems to be working fine. However, when I try to access a user detail after logging in, I am faced with an authorization issue preventing me from exploring other routes. How can I store th ...

Sorting an array based on shortest distance in Javascript - A step-by-step guide

I need to organize an array so that each element is in the closest proximity to its previous location. The array looks like this: locations=[{"loc1",lat,long},{"loc2",lat,long},{"loc3",lat,long},{"loc4",lat,long},{"loc5",lat,long}] Here's the funct ...

Conceal Bootstrap 3 Modal and AngularJS navigation using $location.path

In my AngularJS App, I am utilizing the Bootstrap 3 modal as a dialog confirmation. However, when I hide the modal and redirect, the backdrop of the modal still remains visible. $scope.delete = function () { DataService.delete() .then(function () ...

How to Stop Element Flickering While Hovering in Selenium IE Webdriver

My code is functioning perfectly in Firefox, but when I try it on Internet Explorer, there is flickering. Here is my code: WebElement mouseOver= driver.findElement(By.linkText("abc")); //I'm locating the element by link text. Selenium finds the ...

Php file not receiving data from ajax post method

forget.php PHP: if (! (empty($_POST['emailforget'])) ) { echo "here in the function"; } else { echo "here"; } AJAX: $("#passreset").on('click', function(e) { var emailforget = $("#tempemail").val(); alert(emailforget); ...