theoephraim / node-google-spreadsheet

Google Sheets API wrapper for Javascript / Typescript
https://theoephraim.github.io/node-google-spreadsheet
The Unlicense
2.34k stars 390 forks source link

[Bug] addRows replace heading row when there are hidden columns #706

Open nktnet1 opened 1 month ago

nktnet1 commented 1 month ago

Below is the code I'm currently using:

const serviceAccountAuth = new JWT({
  email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
  key: process.env.GOOGLE_PRIVATE_KEY,
  scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});

const doc = new GoogleSpreadsheet('<some sheet ID>', serviceAccountAuth);
await doc.loadInfo();

const data = getRowData();

if (data.length > 0) {
  const sheet = doc.sheetsByTitle['some title'];
  await sheet.clear();
  await sheet.setHeaderRow(Object.keys(data[0]));

  // This replaces the heading row if there are hidden columns
  await sheet.addRows(data);
}

This works fine when there are no hidden columns. However, if I were to hide some columns using Google Sheet's GUI interface, the heading gets replaced by the first row when using sheet.addRows(data)

By hidden columns, I mean this:

hide-columns

narendra-paiteq commented 1 month ago

hello, @nktnet1 have you found any workaround for this issue?

nktnet1 commented 1 month ago

Not really - other than manually un-hiding the columns, run my script, then hiding them again.

Other alternatives might be to use the official Google API, or for a hacky solution, include the header row as the first array item in addRows.