Use Google cloud function for stream data from firebase to gsheet

gsheet-add-data.ts

import * as functions from 'firebase-functions';
import { OAuth2Client } from 'google-auth-library';
import { google, sheets_v4 } from 'googleapis';
import admin from 'firebase-admin';

// Use firebase functions:config:set to configure your watchedpaths object:
// googleapi.client_id = Google API client ID,
// googleapi.client_secret = client secret, and
// googleapi.sheet_id = Google Sheet id (long string in middle of sheet URL)
// watchedpaths.data_path = Firebase path for data to be synced to Google Sheet
const FUNC_CONFIG = functions.config()
let CONFIG_CLIENT_ID: string
let CONFIG_CLIENT_SECRET: string
let CONFIG_SHEET_ID: string
let CONFIG_DATA_PATH: string
let USER_ACTIVITY_GID: number

CONFIG_CLIENT_ID = FUNC_CONFIG.googleapi.client_id;
CONFIG_CLIENT_SECRET = FUNC_CONFIG.googleapi.client_secret;
CONFIG_SHEET_ID = FUNC_CONFIG.googleapi.sheet_id;
CONFIG_DATA_PATH = FUNC_CONFIG.watchedpaths.data_path;
USER_ACTIVITY_GID = parseInt(FUNC_CONFIG.watchedpaths.user_activity_gid)

// The OAuth Callback Redirect.
const FUNCTIONS_REDIRECT = `https://asia-east2-${process.env.GCLOUD_PROJECT}.cloudfunctions.net/oauthCallback`;

// setup for authGoogleAPI
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const functionsOauthClient = new OAuth2Client(CONFIG_CLIENT_ID, CONFIG_CLIENT_SECRET,
  FUNCTIONS_REDIRECT);

// OAuth token cached locally.
let oauthTokens: any = null;

// visit the URL for this Function to request tokens
exports.authGoogleApi = functions.region('asia-east2').https.onRequest((req, res) => {
  res.set('Cache-Control', 'private, max-age=0, s-maxage=0');
  res.redirect(functionsOauthClient.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES,
    prompt: 'consent',
  }));
});

// setup for OauthCallback
const DB_TOKEN_PATH = '/api_tokens';

// after you grant access, you will be redirected to the URL for this Function
// this Function stores the tokens to your Firebase database
exports.oauthCallback = functions.region('asia-east2').https.onRequest(async (req, res) => {
  res.set('Cache-Control', 'private, max-age=0, s-maxage=0');
  const code = req.query.code;
  try {
    const { tokens } = await functionsOauthClient.getToken(code);
    // Now tokens contains an access_token and an optional refresh_token. Save them.
    await admin.firestore().collection(DB_TOKEN_PATH).add(tokens);
    return res.status(200).send('App successfully configured with new Credentials. '
      + 'You can now close this page.');
  } catch (error) {
    return res.status(400).send(error);
  }
});

// trigger function to write to Sheet when new data comes in on CONFIG_DATA_PATH
exports.appendRecordToSpreadSheet = functions.region('asia-east2').firestore
    .document(`${CONFIG_DATA_PATH}/{ITEM}`)
    .onCreate(appendRecord);

async function appendRecord(snap: functions.FirebaseFirestore.DocumentSnapshot, _: functions.EventContext) {
    const newRecord = snap.data();
    if (!newRecord)
        return;

    const rowData = await google.sheets('v4').spreadsheets.values.get({
        auth: await getAuthorizedClient(),
        spreadsheetId: CONFIG_SHEET_ID,
        majorDimension: 'ROWS',
        range: "'User activity'!a1:zz1"
    })

    const data: any[] = [newRecord.time];
    let columnNum = 1;
    if (rowData.data.values && rowData.data.values[0]){
        columnNum = Object.getOwnPropertyNames(rowData.data.values[0]).length
        const listUser = rowData.data.values[0];
        for (let index = 1; index < listUser.length; index+=3) {
            const user = listUser[index];
            if (newRecord.unique_active_user[user]){
                data.push(...[
                    newRecord.unique_active_user[user].point_in,
                    newRecord.unique_active_user[user].point_out,
                    newRecord.unique_active_user[user].total_bills,
                ])
                delete newRecord.unique_active_user[user]
            } else {
                data.push(...[0,0,0])
            }
        }
    }

    const newData: any[][] = [[],[],[]]
    for (const userKey in newRecord.unique_active_user) {
        const user = newRecord.unique_active_user[userKey];
        newData[0].push(...[userKey, user.name, ''])
        newData[1].push(...['point in', 'point out', 'total bills'])
        newData[2].push(...[
            user.point_in,
            user.point_out,
            user.total_bills,
        ])
    }

    console.log(data)
    console.log(newData)
    await batchUpdatePromise({
        spreadsheetId: CONFIG_SHEET_ID,
        requestBody: {
            requests: [
                {
                    "insertRange": {
                        "range": {
                            "sheetId": USER_ACTIVITY_GID,
                            "startRowIndex": 2,
                            "endRowIndex": 3
                        },
                        "shiftDimension": "ROWS"
                    }
                },
                {
                    "pasteData": {
                        "data": data.join(', '),
                        "type": "PASTE_NORMAL",
                        "delimiter": ",",
                        "coordinate": {
                            "sheetId": USER_ACTIVITY_GID,
                            "rowIndex": 2,
                            columnIndex: 0
                        }
                    }
                }
            ]
        },
    });

    await appendPromise({
        spreadsheetId: CONFIG_SHEET_ID,
        range: `'User activity'!${columnToLetter(columnNum + 1)}1`,
        valueInputOption: 'USER_ENTERED',
        insertDataOption: 'OVERWRITE',
        requestBody: {
            majorDimension: 'ROWS',
            values: newData,
        },
    });

    const totalData = [
        newRecord.time,
        newRecord.total_point_in,
        newRecord.total_point_out,
        Object.getOwnPropertyNames(newRecord.unique_active_user).length,
        newRecord.total_revenue,
    ]
    await appendPromise({
        spreadsheetId: CONFIG_SHEET_ID,
        range: `'Total'!A2`,
        valueInputOption: 'USER_ENTERED',
        insertDataOption: 'INSERT_ROWS',
        requestBody: {
            majorDimension: 'ROWS',
            values: [totalData],
        },
    });
}

// accepts an append request, returns a Promise to append it, enriching it with auth
function appendPromise(requestWithoutAuth: sheets_v4.Params$Resource$Spreadsheets$Values$Append) {
    return new Promise(async (resolve, reject) => {
        const client = await getAuthorizedClient();
        const sheets = google.sheets('v4');
        const request = requestWithoutAuth;
        request.auth = client;
        sheets.spreadsheets.values.append(request, (err: any, response: any) => {
            if (err) {
                console.log(`The API returned an error: ${err}`);
                reject(err);
            } else{
                resolve(response.data);
            }
        });
    });
}

function batchUpdatePromise(requestWithoutAuth: sheets_v4.Params$Resource$Spreadsheets$Batchupdate) {
    return new Promise(async (resolve, reject) => {
        const client = await getAuthorizedClient();
        const sheets = google.sheets('v4');
        const request = requestWithoutAuth;
        request.auth = client;
        sheets.spreadsheets.batchUpdate(request, (err: any, response: any) => {
            if (err) {
                console.log(`The API returned an error: ${err}`);
                reject(err);
            } else {
                resolve(response.data);
            }
        });
    });
}

// checks if oauthTokens have been loaded into memory, and if not, retrieves them
async function getAuthorizedClient() {
    if (oauthTokens) {
        return functionsOauthClient;
    }
    const snapshot = await admin.firestore().collection(DB_TOKEN_PATH).get();
    oauthTokens = snapshot.docs[0].data();

    functionsOauthClient.setCredentials(oauthTokens);
    return functionsOauthClient;
}

function columnToLetter(col: number) {
    let temp, letter = '';
    let column = col
    while (column > 0) {
        temp = (column - 1) % 26;
        letter = String.fromCharCode(temp + 65) + letter;
        column = (column - temp - 1) / 26;
    }
    return letter;
}