【Day20】Google Sheets - 寫入爬蟲資料，跟 Copy & Paste 的日子說掰掰

async function crawlerFB (driver) {
    const isLogin = await loginFacebook(driver)
    if (isLogin) {
        console.log(`FB開始爬蟲`)
        let result_array = []
        for (fanpage of fanpage_array) {
            let trace = null
            try {
                const isGoFansPage = await goFansPage(driver, fanpage.url)
                if (isGoFansPage) {
                    await driver.sleep((Math.floor(Math.random() * 4) + 3) * 1000)
                    trace = await getTrace(driver, By, until)
                }
                if (trace === null) {
                    console.log(`${fanpage.title}無法抓取追蹤人數`)
                } else {
                    console.log(`${fanpage.title}追蹤人數：${trace}`)
                }
            } catch (e) {
                console.error(e);
                continue;
            } finally {// 將粉專的資訊塞入物件
                result_array.push({
                    url: fanpage.url,
                    title: fanpage.title,
                    trace: trace
                })
            }
        }
        // 回傳FB粉專爬蟲資料
        return result_array
    }
}

async function crawler () {
    const driver = await initDrive();
    if (!driver) {
        return
    }
    //取得回傳的爬蟲資料
    const ig_result_array = await crawlerIG(driver)
    const fb_result_array = await crawlerFB(driver)
    driver.quit();
    //將回傳的爬蟲資料傳遞給Google Sheets處理
    await updateGoogleSheets(ig_result_array, fb_result_array)
}

async function updateGoogleSheets (ig_result_array, fb_result_array) {
    try {
        const auth = await getAuth()
        let sheets = await getFBIGSheet(auth)
        console.log('FB、IG Sheet資訊:')
        console.log(sheets)

        // 將爬蟲資料寫入各自的Sheet
        await writeSheet('FB粉專', fb_result_array, auth)
        await writeSheet('IG帳號', ig_result_array, auth)
        console.log(`成功更新Google Sheets：https://docs.google.com/spreadsheets/d/${process.env.SPREADSHEET_ID}`);
    } catch (err) {
        console.error('更新Google Sheets失敗');
        console.error(err);
    }
}

async function writeSheet (title, result_array, auth) {
    // title可以直接超連結到粉專
    let title_array = result_array.map(fanpage => [`=HYPERLINK("${fanpage.url}","${fanpage.title}")`]);
    // 開頭填入填上FB粉專/IG帳號
    title_array.unshift([title])//unshift是指插入陣列開頭
    // 在第一欄寫入title(粉專名稱)
    await writeTitle(title, title_array, auth)

    // 取得目前最後一欄
    let lastCol = await getLastCol(title, auth)

    
    let trace_array = result_array.map(fanpage => [fanpage.trace]);
    // 抓取當天日期
    const datetime = new Date()
    trace_array.unshift([dateFormat(datetime, "GMT:yyyy/mm/dd")])
    // 再寫入trace(追蹤人數)
    await writeTrace(title, trace_array, lastCol, auth)
}

async function writeTitle (title, title_array, auth) {
    const sheets = google.sheets({ version: 'v4', auth });
    const request = {
        spreadsheetId: process.env.SPREADSHEET_ID,
        valueInputOption: "USER_ENTERED",
        range: [
        `'${title}'!A:A` // 這是寫入第一欄的意思
        ],
        resource: {
        values: title_array
        }
    }
    try {
        await sheets.spreadsheets.values.update(request);
        console.log(`updated ${title} title`);
    } catch (err) {
        console.error(err);
    }
}

async function getLastCol (title, auth) {
    const sheets = google.sheets({ version: 'v4', auth });
    const request = {
        spreadsheetId: process.env.SPREADSHEET_ID,
        ranges: [
        `'${title}'!A1:ZZ1`
        ],
        majorDimension: "COLUMNS",
    }
    try {
        let values = (await sheets.spreadsheets.values.batchGet(request)).data.valueRanges[0].values;
        // console.log(title + " StartCol: " + toColumnName(values.length + 1))
        return toColumnName(values.length + 1)
    } catch (err) {
        console.error(err);
    }
}

function toColumnName (num) {//Google Sheets無法辨認數字欄位，需轉為英文才能使用
    for (var ret = '', a = 1, b = 26; (num -= a) >= 0; a = b, b *= 26) {
        ret = String.fromCharCode(parseInt((num % b) / a) + 65) + ret;
    }
    return ret;
}

async function writeTrace (title, trace_array, lastCol, auth) {
    const sheets = google.sheets({ version: 'v4', auth });
    const request = {
        spreadsheetId: process.env.SPREADSHEET_ID,
        valueInputOption: "USER_ENTERED",
        range: [
        `'${title}'!${lastCol}:${lastCol}`//將追蹤人數填入最後一欄
        ],
        resource: {
        values: trace_array
        }
    }
    try {
        await sheets.spreadsheets.values.update(request);
        console.log(`updated ${title} trace`);
    } catch (err) {
        console.error(err);
    }
}