1

I develop a simple app which uses Google Sheets as database. So far I was only using it to get the rows and it was enough for me. But now I’d like to add the feature where user can search by sheets values. Unfortunately, I couldn’t find in docs or on the Internet any examples using batchGet method for that. It allows only to specify ranges and fetch such content - not by keyword. I found some examples with Query API but, I think, it isn’t compatible with approach I provided below. So, my question is: can I search and get some specific rows by keyword using batchGet()? Or at least extend the provided code as much as possible, without rewriting everything?

const { google } = require('googleapis');

const client = new google.auth.JWT(
  process.env.GS_CLIENT_EMAIL,
  null,
  process.env.GS_PRIVATE_KEY.replace(/\\n/g, '\n'),
  ['https://www.googleapis.com/auth/spreadsheets.readonly']  
);

client.authorize((err) => {
  if(err) {
    console.log(err);
    return;
  }

  return client;
});

const gsrun = async (lang, page) => {
  const gsapi = google.sheets({ version: 'v4', auth: client });
  const sheetId = lang.toUpperCase();
  const spreadsheetId = process.env[`SHEET_${sheetId}`];

  const fileInfo = await gsapi.spreadsheets.get({
    spreadsheetId
  });
  
  const sheetNames = (page === null) ? fileInfo.data.sheets.map((d) => d.properties.title) : [fileInfo.data.sheets[page].properties.title];

  const data = await gsapi.spreadsheets.values.batchGet({ 
    spreadsheetId,
    ranges: sheetNames.map((name) => `${name}!A1:D200`)
  });

  const sheetValues = data.data.valueRanges.map(({ values }) => values);
  
  return {
    totalPages: fileInfo.data.sheets.length,
    items: sheetValues
  }; // [[[],[],...[]],[[],[],...[]],...[],[],...[]];
};

exports.gsrun = gsrun;
chudy91
  • 408
  • 3
  • 18
  • First, I deeply apologize that my answer was not useful for your situation. And, I have to apologize for my poor English skill. Unfortunately, I cannot understand `Or at least do it somehow without rewriting the all provided code?`. Can I ask you about the detail of it? – Tanaike Feb 21 '22 at 00:36
  • I just don't want to rewrite everything that I posted in code section :) I'd like to use what I've already written as much as possible. – chudy91 Feb 21 '22 at 09:13
  • Thank you for replying. I understood your reply of `I just don't want to rewrite everything that I posted in code section :) I'd like to use what I've already written as much as possible.`. – Tanaike Feb 22 '22 at 00:01

1 Answers1

1

Issue:

If I understand you correctly, you want to retrieve rows in which a certain cell has a certain value.

Unfortunately, there is no way to filter rows based on a column value during the Sheets API request.

Workaround:

I'd just filter the values returned by the API after receiving them.

For example, replace this:

const sheetValues = data.data.valueRanges.map(({ values }) => values);

With this:

const KEYWORD_COLUMN = 1; // Change according to your preferences
const KEYWORD = "My keyword"; // Change according to your preferences
const sheetValues = data.data.valueRanges.map(({ values }) => values.filter(row => row[KEYWORD_COLUMN - 1] === KEYWORD));
Iamblichus
  • 18,540
  • 2
  • 11
  • 27
  • I was thinking about this but it doesn’t seem performance efficient. Maybe it would be if I was cashing API response…. – chudy91 Feb 22 '22 at 08:11
  • @chudy91 Sure, it would be more efficient to retrieve only the desired rows in the API, but as I mentioned, that's not possible. If you want to add this feature to the API, please consider filing a feature request in Issue Tracker using [this template](https://issuetracker.google.com/issues/new?component=191608&template=823917). – Iamblichus Feb 22 '22 at 08:17