lc-to-excel.js

const express = require('express');
const { google } = require('googleapis');
const { sheets } = require('googleapis/build/src/apis/sheets');
const app = express()
const port = 3000

const { convertToExcelInput } = require('./util/formatter');

const QUERY_ALL_QUESTIONS = "query problemsetQuestionList($categorySlug: String, $limit: Int, $skip: Int, $filters: QuestionListFilterInput) {\n  problemsetQuestionList: questionList(\n    categorySlug: $categorySlug\n    limit: $limit\n    skip: $skip\n    filters: $filters\n  ) {\n    total: totalNum\n    questions: data {\n      questionId\n      title\n      titleSlug\n      difficulty\n      topicTags {\n        slug\n      }\n    }\n  }\n}\n    "
const VARIABLES_ALL_QUESTIONS = {"categorySlug":"","filters":{},"limit":2436,"skip":0};

app.get('/questions/all/sync', function(req, res) {
    const url = ` https://leetcode.com/graphql`;

    const variables = VARIABLES_ALL_QUESTIONS;
    const query = QUERY_ALL_QUESTIONS;

    return fetch(url, {
        method: 'post',
        headers: {
            'Content-Type': 'application/json',
        },
        body: JSON.stringify({query, variables})
        })
        .then(response => response.json())
        .then(({ data }) => {
            const result = convertToExcelInput(data?.problemsetQuestionList?.questions);
            saveQuestionsToExcelSheets(result);
            return res.json(result);
        })
        .catch((e) => {
        console.log(e)
    })
});

const authentication = async () => {
    const auth = new google.auth.GoogleAuth({
        keyFile: "credentials.json",
        scopes: "https://www.googleapis.com/auth/spreadsheets"
    });

    const client = await auth.getClient();

    const sheets = google.sheets({
        version: 'v4',
        auth: client,
    });
    return { sheets }
}

const id = '1D0dNwpdpEAqzK7u8MKq_HCYHyNqDVxsHHle7tmkifdk';
app.get('/getSheet', async(req, res) => {
    try {
        const { sheets } = await authentication();

        const response = await sheets.spreadsheets.values.get({
            spreadsheetId: id,
            range: 'Sheet1!A2:E1000',
        })
        res.send(response?.data?.values);
    } catch(e) {
        console.log(e);
        res.status(500).send();
    }
})

const saveQuestionsToExcelSheets = async (values = []) => {
    try {
        const { sheets } = await authentication();

        //writing data to a spreadsheet
        const writeReq = await sheets.spreadsheets.values.append({
            spreadsheetId: id,
            range: "Sheet1!A2:E1000",
            valueInputOption: "USER_ENTERED",
            resource: {
                values: values,
            }
        })

        if(writeReq.status === 200) {
            console.log("Spreadsheet updated successfully!")
        }

        // return res.json({ msg: "Something went wrong while updating the spreadsheet"});
    } catch(e) {
        console.log('Error updating the spreadsheet', e);
        // res.status(500).send();
    }
}

app.listen(port, () => {
  console.log(`Example app listening on port ${port}`)
})