1

I'm trying to use Google Sheet as a database, using Next.js to build this app. Unfortunately, I've suffered some problems over a few days.

The problems are I want to get data by searching for a specific id, but I can't figure out how to solve this problem. Here is the data example:

enter image description here

How to use the report id to get the name and the row of data? If I search for reportId = 12, I want to have three data in the response.

And the other question is how to update a specific ID by using Google Sheets API? I've seen using batchGetDataByFilter, but still don't know how to use it.

Here is my code

import { google } from 'googleapis';
import moment from 'moment';
import { auth } from '../../lib/google';

const handler = async (req, res) => {
  if (req.method !== 'GET') {
    return res.status(405).send('Only for GET requests');
  }

  try {
    const { reportId } = req.query;
    const sheets = google.sheets({ version: 'v4', auth });
    const sheet_response = sheets.spreadsheets.values.batchGetByDataFilter({
      spreadsheetId: process.env.GOOGLE_SHEET_ID,
      requestBody: {
        valueRenderOption: 'FORMATTED_VALUE',
        majorDimension: 'ROWS',
        dataFilters: [{}],
      },
    });
    const data = sheet_response?.data?.values;
    return res.status(200).json({});
  } catch (error) {
    return res.status(500).send({ message: error.message ?? 'Something went wrong' });
  }
};

export default handler;
hao
  • 197
  • 1
  • 3
  • 11
  • Can you provide your current script? – Tanaike May 22 '22 at 02:23
  • @Tanaike I update in the question, hope it's help – hao May 22 '22 at 04:32
  • Thank you for replying and adding your script. I apologize for my poor English skill. About your 1st question, when `reportId = 12` is used, what result do you want to retrieve? I cannot understand `I want to have three data in the response`. About your 2nd question, can I ask you about the detail of `update a specific ID` you want to achieve? If you can do, please provide the sample input and output situations you expectr? By the way, in your showing script, your `auth` can be used for updating the Spreadsheet? – Tanaike May 22 '22 at 05:04

0 Answers0