CODEWITHSUNDEEP
google-apps-script
Arif Ahmed
Arif Ahmed

Reputation: 385

Add the data after the header row in google sheets

I am successfully able to retrieve data from an API and paste the data to the sheet. This works well.

Code.gs

const sheet = SpreadsheetApp.getActiveSheet();
const headers = Object.keys(data.data[0]);
const values = [headers, ...data.data.map(o => headers.map(h => o[h]))];
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);

This is the problem, I am facing:

When I run the query or the function for the last 30 days, it pastes the data to the sheet which is all good. But when I run the query again, it pastes the data with the headers and rows again hence the header data is duplicated.

What I want

If I run the query for yesterday's date, it should paste headers and row with yesterday's data. And then if I run the query in today's date, it should only add a row with today's data.

Upvotes: 0

Views: 836

Answers (1)

Tanaike
Tanaike

Reputation: 201378

In your situation, how about the following modification?

From:

const values = [headers, ...data.data.map(o => headers.map(h => o[h]))];

To:

var values = data.data.map(o => headers.map(h => o[h]));
if (sheet.getLastRow() == 0) values.unshift(headers);
  • In this modification, by checking whether the 1st row is existing, the header row is added.

Upvotes: 2

Related Questions