[Fixed] How to loop in a range of Google Spreadsheets

Issue

I’m trying to render the data from a Google Spreadsheets with Express and after placing the range of the cells to render and loop into it, I have a error: "Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client."

The spreadsheet has 3 rows and 6 columns and I’m not sure what I’m doing wrong as the error is persistent and I cannot get rid of it of anyway.

My code:

 const express = require('express');
 const router = express.Router();
 const {google} = require('googleapis');
 const keys = require('../credentials.json');

 const {
   google
  } = require('googleapis');
  const keys = require('../credentials.json');

  /* GET portfolio page. */
  router.get('/', function (req, res, next) {

  const client = new google.auth.JWT(
  keys.client_email,
  null,
  keys.private_key,
   ['https://www.googleapis.com/auth/spreadsheets.readonly']
  );

  client.authorize(function (err) {
   if (err) {
     console.log(err);
     return;
     } else {
       console.log('Connected');
       gsrun(client);
     }
    });

     async function gsrun(cl) {
     const gsapi = google.sheets({
     version: 'v4',
     auth: cl
    });

     const optPort1 = {
     spreadsheetId: '1W1OKGmGU6Io-1FhWjZLyPkGZz9Ky829zurAzcwmXiHg',
     range: ['Portfolio Page!A4:F6']
   };


   let spreadvals1 = await gsapi.spreadsheets.values.get(optPort1);


   console.log(spreadvals1.data.values);


    const cols1 = spreadvals1.data.values || [];

    const colsdata = cols1.map((element) => {
    res.set('Content-Type', 'text/html');

    res.render('portfolio', {
       headlinePortfolio: element[0],
       subheadlinePortfolio: element[1],
       image1: element[3],
       client: element[4],
       campaign: element[5]
     })
   });
 }
 });

  module.exports = router;

My HTML looks like this:

   <div class="page-header">
   <div class="text-headline">
       <div class="salutation">{{headlinePortfolio}}</div>
   </div>
   <div class="text-subheadline">
       <div class="descr">{{subheadlinePortfolio}}</div>
   </div>
   <div class="port-row">         
       <ul class="flex-container-port">
           <li class="flex-item-img-mob">
              <img src="{{image1}}" alt="header-image" />
           </li>
           <li class="flex-item-img-desktop">
             <img class="img-port" src="{{image2}}" alt="header-image" />
           </li>
           <li class="flex-item-descr">
              <p class="bg-text">{{client1}}</p>
              <p class="descr-text">{{campaign1}}</p>
           </li>
       </ul>
   </div>
   </div>

So, some dummy data will be:

   Name Age Year
   John 21  5
   Paul 22  6
   Mark 23  7
   Maggie 24 8
   Beth 25  9
   Patsy26  10

Solution

The problem it is in this line:

const colsdata = cols1.map((element) => {
res.set('Content-Type', 'text/html');

res.render('portfolio', {
   headlinePortfolio: element[0],
   subheadlinePortfolio: element[1],
   image1: element[3],
   client: element[4],
   campaign: element[5]
 })

})

You cannot render more than once. Render calls the method res.send() which sends data to the front-end. So what is happening is: You are sending (res.send()) several times (because res.render is inside the map) thus the error.

For you prevent the render to send the data you have to provide a callback like so:

res.render('portfolio', {
   headlinePortfolio: element[0],
   subheadlinePortfolio: element[1],
   image1: element[3],
   client: element[4],
   campaign: element[5]
 }, ()=>console.log(`template created do something`))

and the when you are ready to send everything you will just try to send everything in one go with: res.send(data)

Also you router.get has to be async in order to the await keyword work.

More update:

You have updated your answer therefore this is a more dumb down approach of everything that I had shared above.

You are trying to create one template but the problem is that you are creating multiple templates because you are using a function inside a loop. What you should be doing is to remove the function from the loop.

Unfortunately I cannot teach exactly how to achieve what you want to achieve. I will reproduce a minimum output so you can work from there:

Note: In order to simplify, everything that is not here should stay the same.

 /* GET portfolio page. */
  router.get('/', async function (req, res, next) {

     ...
    const cols1 = spreadvals1.data.values || [];


  // I have removed the .map fn. You should do the same

    res.set('Content-Type', 'text/html');
    res.render('portfolio', {
       headlinePortfolio: cols[0][0], //row 1 - elem (col) 1
       subheadlinePortfolio:cols[0][1], //row 1 - elem  (col) 2
       image1: cols[0][3], //row 1 - elem (col) 4
       client: cols[0][4],//row 1 - elem (col) 5
       campaign: cols[0][5], // row 1 - elem (col) 6
     })

 }
 });

  module.exports = router;

With the example above you will have no errors and it will only get the first row. If you want the next rows you will have to increase for row[1], row[2], row[3] and so forth…
If you wanna multiple (separated) templates from different rows you will have to dig deeper and understand what I am saying about callbacks and implement it with map.