create uploading pipeline to google sheets in upload.js

1 files changed, 202 insertions, 0 deletions

diff --git a/upload.js b/upload.js
new file mode 100644
index 0000000..7e91f1a
--- /dev/null
+++ b/upload.js
@@ -0,0 +1,202 @@
+const fs = require('fs');
+const crypto = require('crypto');
+
+// load in google credentials json file
+const credentialsPath = 'google_credentials.json';
+const { private_key_id, client_email, token_uri, private_key }
+    = JSON.parse(fs.readFileSync(credentialsPath, 'utf8'));
+
+const spreadsheetId = '1csTrnR5LwSBft1wtczDdxk0aPnHIdIOHX3MTP-IkpPk';
+const templateSheetId = 2006563664; // the ID of the template sheet in the spreadsheet
+
+// url base encoder helper
+const urlBase64Encode = (str) => {
+    return Buffer.from(str)
+        .toString('base64')
+        .replace(/\+/g, '-')
+        .replace(/\//g, '_')
+        .replace(/=+$/, '');
+}
+
+// auth using JWT 
+// (https://developers.google.com/identity/protocols/oauth2/service-account#httprest)
+const createJWT = () => {
+    const jwtHeader = {
+        alg: 'RS256',
+        typ: 'JWT',
+        kid: private_key_id
+    };
+
+    const jetClaimSet = {
+        iss: client_email,
+        scope: 'https://www.googleapis.com/auth/spreadsheets',
+        aud: token_uri,
+        exp: Math.floor(Date.now() / 1000) + 3600, // 1 hour expiration
+        iat: Math.floor(Date.now() / 1000)
+    };
+
+    const signedHeader = urlBase64Encode(JSON.stringify(jwtHeader));
+    const signedClaimSet = urlBase64Encode(JSON.stringify(jetClaimSet));
+    const signature = urlBase64Encode(
+        crypto.createSign('RSA-SHA256')
+            .update(`${signedHeader}.${signedClaimSet}`)
+            .sign(private_key)
+    );
+
+    return `${signedHeader}.${signedClaimSet}.${signature}`;
+}
+
+const postAuthRequest = async () => {
+    // get the JWT
+    const jwt = createJWT();
+
+    // prepare the encoded body
+    let body = {
+        'grant_type': 'urn:ietf:params:oauth:grant-type:jwt-bearer',
+        'assertion': jwt
+    }
+    body = new URLSearchParams(body).toString();
+
+    // make the POST request to the token URI
+    const options = {
+        method: 'POST',
+        headers: {
+            'Host': 'oauth2.googleapis.com',
+            'Content-Type': 'application/x-www-form-urlencoded'
+        },
+        body,
+    };
+    const response = await fetch(token_uri, options);
+    if (!response.ok) {
+        throw new Error(`HTTP error! status: ${response.status}, message: ${await response.text()}`);
+    }
+
+    // return the access token
+    const data = await response.json();
+    return data.access_token;
+}
+
+const randomSheetId = () => {
+    // use date without dashes
+    const date = new Date().toISOString().split('T')[0].replace(/-/g, '');
+    return parseInt(date);
+}
+
+const appendCellsRequest = (sheetId, row) => {
+    const rowsArray = row.map(cell => {
+        return {
+            values: cell.map(value => {
+                return {
+                    userEnteredValue: {
+                        stringValue: value
+                    }
+                };
+            })
+        };
+    });
+    
+    return {
+        sheetId,
+        rows: rowsArray,
+        fields: '*',
+    };
+}
+
+const duplicateSheetRequest = (sheetId) => {
+    const date = new Date().toISOString().split('T')[0];
+    return {
+        sourceSheetId: templateSheetId,
+        insertSheetIndex: 0,
+        newSheetId: sheetId,
+        newSheetName: date,
+    }
+}
+
+const batchUpdateSpreadsheet = (sheetId, rows) => {
+    return {
+        "requests": [
+            {
+                "duplicateSheet": duplicateSheetRequest(sheetId)
+            },
+            {
+                "appendCells": appendCellsRequest(sheetId, rows)
+            }
+        ],
+    };
+}
+const postBatchUpdateRequest = async (batchUpdateRequest) => {
+    const accessToken = await postAuthRequest();
+    const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}:batchUpdate`;
+    const options = {
+        method: 'POST',
+        headers: {
+            'Authorization': `Bearer ${accessToken}`,
+            'Content-Type': 'application/json'
+        },
+        body: JSON.stringify({
+            requests: batchUpdateRequest.requests
+        })
+    };
+    const response = await fetch(url, options);
+    if (!response.ok) {
+        throw new Error(`HTTP error! status: ${response.status}, message: ${await response.text()}`);
+    }
+    const data = await response.json();
+    return data;
+} 
+
+const getRowsFromTSV = (tsvPath) => {
+    const tsvData = fs.readFileSync(tsvPath, 'utf8');
+    const rows = tsvData.split('\n').map(line => line.split('\t').map(cell => cell.trim()));
+    // remove the first row (header)
+    rows.shift();
+    return rows;
+}   
+
+const main = async () => {
+    // // get the data from the tsv
+    // const tsvPath = 'sp500_formatted_data.tsv';
+    // const tsvData = fs.readFileSync(tsvPath, 'utf8');
+    // const rows = tsvData.split('\n').map(line => line.split('\t').map(cell => cell.trim()));
+    // // remove the first row (header)
+    // rows.shift();
+    // // create a random sheet id
+    // const sheetId = randomSheetId();
+    // // create the batch update request
+    // const batchUpdateRequest = batchUpdateSpreadsheet(sheetId, rows);
+    // // write the request to a file
+    // const outputPath = 'batch_update_request.json';
+    // fs.writeFileSync(outputPath, JSON.stringify(batchUpdateRequest, null, 2));
+    // console.log(`Batch update request written to ${outputPath}`);
+
+    // Authenticate and get access token
+
+    // try {
+    //     const accessToken = await postAuthRequest();
+    //     console.log('Access Token:', accessToken);
+    // } catch (error) {
+    //     console.error('Error:', error);
+    // }
+
+    // create a random sheet id
+    const sheetId = randomSheetId();
+    // get the rows from the tsv file
+    const tsvPath = 'sp500_formatted_data.tsv';
+    const rows = getRowsFromTSV(tsvPath);
+    // create the batch update request
+    const batchUpdateRequest = batchUpdateSpreadsheet(sheetId, rows);
+
+    // write the request to a file
+    const outputPath = 'batch_update_request.json';
+    fs.writeFileSync(outputPath, JSON.stringify(batchUpdateRequest, null, 2));
+    console.log(`Batch update request written to ${outputPath}`);
+    // post the batch update request
+    try {
+        const response = await postBatchUpdateRequest(batchUpdateRequest);
+        console.log('Batch update response:', response);
+    } catch (error) {
+        console.error('Error:', error);
+    }
+}
+
+main();
\ No newline at end of file