88 lines
2.4 KiB
JavaScript
88 lines
2.4 KiB
JavaScript
|
import { google } from "googleapis";
|
||
|
|
|
||
|
|
import { set_fs, readFile, 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});
|
||
|
|
|
||
|
|
/* remove all sheets after the first */
|
||
|
|
if(wsheet.data.sheets.length > 1) await sheets.spreadsheets.batchUpdate({
|
||
|
|
spreadsheetId: id,
|
||
|
|
requestBody: { requests:
|
||
|
|
wsheet.data.sheets.slice(1).map(s => ({
|
||
|
|
deleteSheet: {
|
||
|
|
sheetId: s.properties.sheetId
|
||
|
|
}
|
||
|
|
}))
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
/* read file */
|
||
|
|
const wb = readFile("pres.numbers");
|
||
|
|
|
||
|
|
/* rename first worksheet to avoid collisions */
|
||
|
|
const props0 = wsheet.data.sheets[0].properties;
|
||
|
|
if(wb.SheetNames.map(n => n.toLowerCase()).includes(props0.title.toLowerCase())) {
|
||
|
|
await sheets.spreadsheets.batchUpdate({
|
||
|
|
spreadsheetId: id,
|
||
|
|
requestBody: { requests: [{
|
||
|
|
updateSheetProperties: {
|
||
|
|
fields: "title",
|
||
|
|
properties: {
|
||
|
|
sheetId: props0.sheetId,
|
||
|
|
title: "thistitleisatleast33characterslong"
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}]}
|
||
|
|
});
|
||
|
|
console.log(`renamed "${props0.title}" to "thistitleisatleast33characterslong"`);
|
||
|
|
}
|
||
|
|
|
||
|
|
/* add sheets from file */
|
||
|
|
for(let name of wb.SheetNames) {
|
||
|
|
const aoa = utils.sheet_to_json(wb.Sheets[name], {header:1});
|
||
|
|
await sheets.spreadsheets.batchUpdate({
|
||
|
|
spreadsheetId: id,
|
||
|
|
requestBody: { requests: [
|
||
|
|
/* add new sheet */
|
||
|
|
{ addSheet: { properties: { title: name } } },
|
||
|
|
] }
|
||
|
|
});
|
||
|
|
await sheets.spreadsheets.values.update({
|
||
|
|
spreadsheetId: id,
|
||
|
|
range: `'${name}'!A1`,
|
||
|
|
valueInputOption: "USER_ENTERED",
|
||
|
|
resource: { values: aoa }
|
||
|
|
});
|
||
|
|
console.log(`Created Google Worksheet "${name}"`);
|
||
|
|
}
|
||
|
|
|
||
|
|
/* remove first sheet */
|
||
|
|
const res = await sheets.spreadsheets.batchUpdate({
|
||
|
|
spreadsheetId: id,
|
||
|
|
requestBody: { requests: [
|
||
|
|
/* remove old first sheet */
|
||
|
|
{ deleteSheet: { sheetId: wsheet.data.sheets[0].properties.sheetId } }
|
||
|
|
] }
|
||
|
|
});
|