-
Notifications
You must be signed in to change notification settings - Fork 1
/
run.js
114 lines (92 loc) · 3.24 KB
/
run.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
// const req = {
// // The spreadsheet to request.
// spreadsheetId: '1dgk1inO4DWA89jQGYqVmWgKvQXGW2dsMrOnbGMsAo9M',
// // The ranges to retrieve from the spreadsheet.
// range: 'master!A1:Q',
// auth,
// };
const Promise = require('bluebird');
const request = Promise.promisifyAll(require('request'));
const { google } = require('googleapis');
const sheets = google.sheets('v4');
const ss = Promise.promisifyAll(sheets.spreadsheets.values);
const mkdirp = require('mkdirp');
const fs = require('fs');
const path = require('path');
const parse = require('csv-parse/lib/sync');
const moment = require('moment');
const _ = require('lodash');
require('dotenv').config();
const SPREADSHEET_ID = process.env.SPREADSHEET_ID
// TODO: should have a script to export the crentials from env vars to file?
const CREDENTIAL_JSON = process.env.CREDENTIAL_JSON
const GOOGLE_APPLICATION_CREDENTIALS = './tmp/credential.json'
process.env.GOOGLE_APPLICATION_CREDENTIALS = GOOGLE_APPLICATION_CREDENTIALS
mkdirp.sync(path.dirname(GOOGLE_APPLICATION_CREDENTIALS))
fs.writeFileSync(GOOGLE_APPLICATION_CREDENTIALS, CREDENTIAL_JSON)
async function readFromSpreadSheet(spreadsheetId, range) {
// This method looks for the GCLOUD_PROJECT and GOOGLE_APPLICATION_CREDENTIALS
// environment variables.
const auth = new google.auth.GoogleAuth({
// Scopes can be specified either as an array or as a single, space-delimited string.
scopes: ['https://www.googleapis.com/auth/spreadsheets']
});
const req = {
spreadsheetId,
range,
auth,
};
const data = await ss.getAsync(req);
return data.data.values || [];
}
async function uploadToSpreadSheet(spreadsheetId, range, values) {
const auth = new google.auth.GoogleAuth({
// Scopes can be specified either as an array or as a single, space-delimited string.
scopes: ['https://www.googleapis.com/auth/spreadsheets']
});
const data = [{
range,
values,
}];
// Additional ranges to update ...
const resource = {
data,
valueInputOption: 'USER_ENTERED',
};
return ss.batchUpdateAsync({
spreadsheetId,
resource,
auth,
});
}
async function main() {
const spreadsheetRecords = await readFromSpreadSheet(SPREADSHEET_ID, 'master!A1:Q')
spreadsheetRecords.shift();
const headers = spreadsheetRecords.shift();
const records = await readCSV();
// remove header
const csvHeaders = records.shift()
records.forEach(r => {
const index = spreadsheetRecords.findIndex(sr => sr[0] === r[0])
if (index < 0) {
spreadsheetRecords.push([...r, moment().format('YYYY-MM-DD hh:mm:ss')])
}
})
if (_.isEmpty(headers)) {
spreadsheetRecords.unshift([...csvHeaders, 'created_at'])
} else {
spreadsheetRecords.unshift(headers)
}
const values = [['This spreadsheet is generated by bot.', `Last Updated at:${moment().format('YYYY-MM-DD hh:mm:ss')}`], ...spreadsheetRecords]
const result = await uploadToSpreadSheet(SPREADSHEET_ID, 'master!A1:Q', values)
console.log(`Total ${result.data.totalUpdatedRows} rows updated.`);
}
async function readCSV() {
const data = await request.getAsync(process.env.REMOTE_CSV_PATH)
const records = parse(data.body, {
columns: false,
skip_empty_lines: true
})
return records
}
main().catch(console.error);