I'm creating a spreadsheet in Node.js environment in shared folder using service account with Google Sheets API v.4 in few steps:
- Create spreadsheet itself in shared folder with "Can Edit" permission for service account.
- Inserting some data and performing some text formats using spreadsheet ID received as callback from previous step.
- Inserting chart using as income data the data inserted in prevoius step.
As the result I have a spreadsheet with expected result (text data and horizontal bar chart on same sheet). But when I'm trying to send it on printer, or download as PDF-file - chart area becomes completely invisible. I didn't find any option in official documentation about possible chart visibility during printing or something like this. And when I'm replacing this created chart with manually created one - everything is ok, I can print it and export to PDF.
So, what is the problem? Am I missing something? Or it's some bug?
index.js
const fs = require('fs');
const { google } = require('googleapis');
const express = require('express');
const bodyParser = require("body-parser");
const app = express();
const PORT = 3000;
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
app.listen(PORT, () => {
console.log(`Server started at http://localhost:${PORT}`)
})
const SCOPES = ['https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/spreadsheets'];
const FOLDER_ID = '1xG3xHhrucB4AGLmnd8T2TmCyqhmPux5Q';
var timeStamp = new Date().getTime();
console.log(`timeStamp at startup = ${timeStamp}`);
const auth = new google.auth.GoogleAuth({
keyFile: 'credentials.json',
scopes: SCOPES
});
process.env.HTTPS_PROXY = 'http://10.5.0.20:3128';
const sheets = google.sheets({
version: 'v4',
auth: auth,
proxy: 'http://10.5.0.20:3128'
});
const drive = google.drive({
version: 'v3',
auth: auth,
proxy: 'http://10.5.0.20:3128'
});
function createFileName() {
const now = new Date();
let date = String(now.toISOString().slice(0, 10));
let hours = String(now.getHours()).padStart(2, "0");
let minutes = String(now.getMinutes()).padStart(2, "0");
let seconds = String(now.getSeconds()).padStart(2, "0");
let humanDate = date.replaceAll('-', '.');
humanDate = `${humanDate}_${hours}-${minutes}-${seconds}`;
return humanDate;
}
async function saveFileLocally(filePath, data) {
fs.writeFile(filePath, JSON.stringify(data), error => {
if (error) {
console.error(error);
return;
}
});
return true;
}
app.get("/check", (req, res) => {
res.send('server is online...');
});
app.post("/motivation", async (req, res) => {
if(!req.body) return res.sendStatus(400);
try {
const prizv = req.body.prizv;
const name = req.body.name;
const father = req.body.father;
const sex = req.body.sex;
const age = req.body.age;
const factors = req.body.factors;
const testName = 'motivation';
const clientData = { prizv: prizv, name: name, father: father, sex: sex, age: age, factors: factors, testName: testName };
const fileName = `${createFileName()}_${prizv}`;
const filePath = `files/${testName}/${fileName}.txt`;
const isSaved = await saveFileLocally(filePath, clientData);
if (isSaved) {
console.log(`file is saved locally....`);
const sheetID = await createSheetToGoogleDIsk(clientData, fileName);
res.send(sheetID);
}
} catch (error) {
console.log(error)
}
});
async function createSheetToGoogleDIsk(clientData, fileName) {
const file = fileName;
var sheetsMetadata = {
name: file,
mimeType: 'application/vnd.google-apps.spreadsheet',
parents: [FOLDER_ID]
};
const res2 = drive.files.create({
resource: sheetsMetadata,
fields: 'id'
}, function (err, file) {
if (err) {
console.error(err);
} else {
console.log('SheetID: ', file.data.id);
const gSheetID = file.data.id;
pasteDataToGoogleSheet(clientData, gSheetID);
insertChartToGoogleSheet(gSheetID);
return file.data.id;
}
});
}
async function insertChartToGoogleSheet(spreadsheetId) {
spreadsheetId = spreadsheetId;
let requests = [];
// set font size for whole Sheet as 14
requests.push({
"repeatCell": {
"range": {
"sheetId": 0,
"startRowIndex": 0,
"endRowIndex": 100,
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"fontSize": 13,
},
},
},
"fields": "userEnteredFormat.textFormat.fontSize"
},
});
// set header text format as Bold and 18 pt
requests.push({
"repeatCell": {
"range": {
"sheetId": 0,
"startRowIndex": 0,
"endRowIndex": 2,
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"fontSize": 18,
"bold": true
},
},
},
"fields": "userEnteredFormat(textFormat)"
},
});
// set subheader text format as Bold and 15 pt
requests.push({
"repeatCell": {
"range": {
"sheetId": 0,
"startRowIndex": 3,
"endRowIndex": 4,
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"fontSize": 15,
"bold": true
},
},
},
"fields": "userEnteredFormat(textFormat)"
},
});
// set client data as Bold
requests.push({
"repeatCell": {
"range": {
"sheetId": 0,
"startRowIndex": 5,
"endRowIndex": 10,
"startColumnIndex": 3,
"endColumnIndex": 5
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"fontSize": 13,
"bold": true
},
},
},
"fields": "userEnteredFormat(textFormat)"
},
});
// set 1st column width as 20px
requests.push({
"updateDimensionProperties": {
"range": {
"sheetId": 0,
"dimension": "COLUMNS",
"startIndex": 0,
"endIndex": 1
},
"properties": {
"pixelSize": 20
},
"fields": "pixelSize"
}
});
// set 4st column width as 150px
requests.push({
"updateDimensionProperties": {
"range": {
"sheetId": 0,
"dimension": "COLUMNS",
"startIndex": 3,
"endIndex": 4
},
"properties": {
"pixelSize": 150
},
"fields": "pixelSize"
}
});
// set bold factors Values
requests.push({
"repeatCell": {
"range": {
"sheetId": 0,
"startRowIndex": 33,
"endRowIndex": 45,
"startColumnIndex": 4,
"endColumnIndex": 5
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"fontSize": 13,
"bold": true
},
},
},
"fields": "userEnteredFormat(textFormat)"
},
});
requests.push({
"addChart": {
"chart": {
"chartId": 1,
"spec": {
"titleTextFormat": {
},
"basicChart": {
"chartType": "BAR",
"axis": [
{
"position": "BOTTOM_AXIS",
},
{
"position": "LEFT_AXIS",
}
],
"domains": [
{
"domain": {
"sourceRange": {
"sources": [
{
"sheetId": 0,
"startRowIndex": 33,
"endRowIndex": 45,
"startColumnIndex": 1,
"endColumnIndex": 2
}
]
},
},
}
],
"series": [
{
"series": {
"sourceRange": {
"sources": [
{
"sheetId": 0,
"startRowIndex": 33,
"endRowIndex": 45,
"startColumnIndex": 4,
"endColumnIndex": 5
}
]
}
},
"targetAxis": "BOTTOM_AXIS"
}
],
},
},
"position": {
"overlayPosition": {
"anchorCell": {
"sheetId": 0,
"rowIndex": 11,
"columnIndex": 1
},
"offsetXPixels": 0,
"offsetYPixels": -7,
"widthPixels": 800,
"heightPixels": 450
},
},
"border": {
"color": {
"red": 1,
"green": 1,
"blue": 1,
"alpha": 0
},
}
}
}
});
const batchUpdateRequest = { requests };
sheets.spreadsheets.batchUpdate({
spreadsheetId,
resource: batchUpdateRequest,
}, (err, result) => {
if (err) {
// Handle error
console.log(err);
return false
} else {
console.log(`${result.updatedCells} chart inserted`);
return spreadsheetId
}
});
}
async function pasteDataToGoogleSheet(clientData, sheetId) {
ecxelID = sheetId;
let data1 = [
["Тест «Мотиваційний особистісний профіль»"], [""], ["Результати тестування"], [""], ["Прізвище"], ["Імя"], ["По-батькові"], ["Вік"], ["Стать"]
];
let data2 = [
[clientData.prizv], [clientData.name], [clientData.father], [clientData.age], [clientData.sex]
];
let factorsLabels1_6 = [
["1. Матеріальна винагорода:"], ["2. Комфортні умови:"], ["3. Структурованість роботи:"], ["4. Соціальні контакти:"], ["5. Довірливі стосунки:"], ["6. Визнання:"]
];
let factors1_6 = [
[clientData.factors.factor1], [clientData.factors.factor2], [clientData.factors.factor3], [clientData.factors.factor4], [clientData.factors.factor5], [clientData.factors.factor6]
];
let factorsLabels7_12 = [
["7. Досягнення мети:"], ["8. Влада і вплив:"], ["9. Відсутність рутини:"], ["10. Креативність:"], ["11. Самовдосконалення і розвиток:"], ["12. Цікава і корисна діяльність:"]
];
let factors7_12 = [
[clientData.factors.factor7], [clientData.factors.factor8], [clientData.factors.factor9], [clientData.factors.factor10], [clientData.factors.factor11], [clientData.factors.factor12]
];
const data = [{
range: "B2:B10",
values: data1,
},
{
range: "D6:D10",
values: data2,
},
{
range: "B34:B39",
values: factorsLabels1_6,
},
{
range: "E34:E39",
values: factors1_6,
},
{
range: "B40:B45",
values: factorsLabels7_12,
},
{
range: "E40:E45",
values: factors7_12,
}
];
const resource = {
data,
valueInputOption: 'RAW',
};
sheets.spreadsheets.values.batchUpdate({
spreadsheetId: ecxelID,
resource: resource,
}, (err, result) => {
if (err) {
// Handle error
console.log(err);
return false
} else {
console.log(`${result.updatedCells} cells data inserted`);
return spreadsheetId;
}
});
}
