I’m trying to do an API using the Google Sheets API. But in my application I always get an empty array as response. These are my codes:

index.routes.js

import { Router } from "express";

const router = Router();

const funciones = require("../functions/index.functions.js");

router.get("/", (req, res) => {
  res.render("index.hbs");
});

router.post("/trabajador/agregar", (req, res) => {
  console.log(req.body);

  res.send("Trabajador Agregado");
});

router.get("/trabajador/obtener", (req, res) => {
  res.send(funciones.obtenertrabajadores());
});

export default router;

index.functions.js

const {google} = require('googleapis');
const sheets = google.sheets('v4');

async function obtenertrabajadores () {
  const authClient = await authorize();
  const request = {
    // ID DE LA HOJA
    spreadsheetId: 'ID',
        
    // RANGO
    range: 'C1:C',

    // UNFORMATTED_VALUE.
    valueRenderOption: 'UNFORMATTED_VALUE', 

    // SERIAL_NUMBER
    dateTimeRenderOption: 'SERIAL_NUMBER',

    auth: authClient,
  };

  try {
    const response = (await sheets.spreadsheets.values.get(request)).data;
    return JSON.stringify(response, null, 2);
  } catch (err) {
    console.error(err);
  }
}

async function authorize() {
  let authClient = 'AuthKey';

  if (authClient == null) {
    throw Error('authentication failed');
  }

  return authClient;
}

module.exports = {
    obtenertrabajadores: obtenertrabajadores
};

If I console log the response, the code works, but when I make the GET petition to /trabajador/obtener, returns ‘{}’

I would like the response to be a json with the data obtained from the request to Google Sheet

I suppose it is because it is returning a promise, but how can I fix it?

>Solution :

I suggest you use functions as middleware since they tend to repeat.

in index.functions.js:

const {google} = require('googleapis');
const sheets = google.sheets('v4');

async function obtenertrabajadores (req, res, next) {
  const authClient = await authorize();
  const request = {
    // ID DE LA HOJA
    spreadsheetId: 'ID',
        
    // RANGO
    range: 'C1:C',

    // UNFORMATTED_VALUE.
    valueRenderOption: 'UNFORMATTED_VALUE', 

    // SERIAL_NUMBER
    dateTimeRenderOption: 'SERIAL_NUMBER',

    auth: authClient,
  };

  try {
    const response = (await sheets.spreadsheets.values.get(request)).data;
    req.response = response //Appending response to req so that you can catch it later
    return
    next()
  } catch (err) {
    console.error(err);
  }
}

async function authorize() {
  let authClient = 'AuthKey';

  if (authClient == null) {
    throw Error('authentication failed');
  }

  return authClient;
}

module.exports = obtenertrabajadores

in index.routes.js:

router.get("/trabajador/obtener", obtenertrabajadores, (req, res) => {
  const response = req.response
  res.json(response)
});