40 lines
1.1 KiB
JavaScript
40 lines
1.1 KiB
JavaScript
|
import { google } from "googleapis";
|
||
|
|
|
||
|
|
import { set_fs, writeFile, utils } from 'xlsx';
|
||
|
|
import * as fs from 'fs';
|
||
|
|
set_fs(fs);
|
||
|
|
|
||
|
|
/* Change this import statement to point to the credentials JSON file */
|
||
|
|
import creds from './sheetjs-test-726272627262.json' assert { type: "json" };
|
||
|
|
|
||
|
|
/* Change this to the spreadsheet ID */
|
||
|
|
const id = "SOME-SPREADSHEETJS-ID";
|
||
|
|
|
||
|
|
/* connect to google services */
|
||
|
|
const jwt = new google.auth.JWT({
|
||
|
|
email: creds.client_email,
|
||
|
|
key: creds.private_key,
|
||
|
|
scopes: [
|
||
|
|
'https://www.googleapis.com/auth/spreadsheets',
|
||
|
|
'https://www.googleapis.com/auth/drive.file',
|
||
|
|
]
|
||
|
|
});
|
||
|
|
|
||
|
|
const sheets = google.sheets({ version: "v4", auth: jwt });
|
||
|
|
|
||
|
|
/* get existing sheets */
|
||
|
|
const wsheet = await sheets.spreadsheets.get({spreadsheetId: id});
|
||
|
|
|
||
|
|
/* create a workbook */
|
||
|
|
const wb = utils.book_new();
|
||
|
|
|
||
|
|
for(let sheet of wsheet.data.sheets) {
|
||
|
|
const name = sheet.properties.title;
|
||
|
|
const res = await sheets.spreadsheets.values.get({
|
||
|
|
spreadsheetId: id,
|
||
|
|
range: `'${name}'`
|
||
|
|
});
|
||
|
|
utils.book_append_sheet(wb, utils.aoa_to_sheet(res.data.values), name);
|
||
|
|
}
|
||
|
|
writeFile(wb, "SheetJSExport.xlsb");
|