Python 開發筆記 - 不透過 Google API Key 下載公開 Google Sheets 資料，將每個 Sheet 匯出 csv 格式

有個工作任務要做 Google Sheets 資料比對，最簡單的方式就把他們匯出後用 git diff 來比對即可，想試著用 AI 產生一隻 python 小工具，只要輸入 Google Sheets URL 或是 Google Sheets URL 內關鍵的辨識 ID （在此稱作 spreadsheet id），就能夠下載該 Google Spreadsheet 內所有 sheet 資料

然後，要下載指定 sheet 必須得知每個 sheet gid ，這個問 ChatGPT-4o 或 Claude.ai 老半天還是沒法解，包過上傳 html static code，最後自己還是跳下來收尾人工刻一下，原理：

• 先設法下載到 HTML Code
• 透過 docs-sheet-tab-caption 抓出 Sheet Name
• 透過 var bootstrapData = {...}; 得知內有 Sheet Name 與 Gid 的資料
• 再用 [0,0,\"gid\",[ 格式，找到 gid

連續動作：

% python3 main.py 

usage: main.py [-h] (--google-spreadsheet-url GOOGLE_SPREADSHEET_URL | --google-spreadsheet-id GOOGLE_SPREADSHEET_ID) [--output OUTPUT]

main.py: error: one of the arguments --google-spreadsheet-url --google-spreadsheet-id is required

% python3 main.py --google-spreadsheet-id 'XXXXXXXXXXXXXXXXXXXXXXX'

[INFO] Downloaded sheet: sheet01 to sheets_csv/sheet01.csv

[INFO] Downloaded sheet: sheet02 to sheets_csv/sheet02.csv

[INFO] Downloaded sheet: sheet03 to sheets_csv/sheet03.csv

[INFO] Downloaded sheet: sheet04 to sheets_csv/sheet04.csv

[INFO] Downloaded sheet: sheet05 to sheets_csv/sheet05.csv

程式碼：

```

% cat main.py 

import argparse

import re

import requests

import json

import os

def extract_spreadsheet_id(url):

    match = re.search(r'/d/([a-zA-Z0-9-_]+)', url)

    return match.group(1) if match else None

def get_spreadsheet_info(spreadsheet_id):

    url = f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit"

    headers = {

        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'

    }

    

    try:

        response = requests.get(url, headers=headers)

        response.raise_for_status()

        html_content = response.text

        

        # Extract sheet names

        sheet_names = re.findall(r'docs-sheet-tab-caption[^>]+>([^<]+)</div>', html_content)

        #print(f"Found sheet names: {sheet_names}")

        # Extract mergedConfig

        config_match = re.search(r'var bootstrapData\s*=\s*({.*?});', html_content, re.DOTALL)

        if config_match:

            config_str = config_match.group(1)

            sheet_info = {}

            try:

                for index, sheet_name in enumerate(sheet_names):

                    #print(f"Processing sheet: {sheet_name}, index: {index}")

                    beginPattern = f'[{index},0,\\"'

                    endPattern = f'\\",['

                    beginIndex = config_str.find(beginPattern)

                    endIndex = config_str.find(endPattern, beginIndex)

                    gidValue = config_str[beginIndex + len(beginPattern):endIndex]

                    sheet_info[sheet_name] = gidValue

                return sheet_info

            except Exception as e:

                print(f"[INFO] Error extracting sheet information: {e}")

                return None

        else:

            print("[INFO] Could not find bootstrapData in the HTML content")

            return None

    except requests.RequestException as e:

        print(f"[INFO] Error fetching the spreadsheet: {e}")

        return None

def download_sheet_as_csv(spreadsheet_id, sheet_name, gid, output_folder):

    csv_url = f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/export?format=csv&gid={gid}"

    csv_response = requests.get(csv_url)

    

    if csv_response.status_code == 200:

        output_path = os.path.join(output_folder, f"{sheet_name}.csv")

        with open(output_path, 'wb') as f:

            f.write(csv_response.content)

        print(f"[INFO] Downloaded sheet: {sheet_name} to {output_path}")

    else:

        print(f"[INFO] Failed to download sheet: {sheet_name}. Status code: {csv_response.status_code}")

def main():

    parser = argparse.ArgumentParser(description="Extract Google Spreadsheet information")

    group = parser.add_mutually_exclusive_group(required=True)

    group.add_argument("--google-spreadsheet-url", help="Google Spreadsheet URL")

    group.add_argument("--google-spreadsheet-id", help="Google Spreadsheet ID")

    parser.add_argument('--output', type=str, default='sheets_csv', help='The directory to save the CSV files')

    args = parser.parse_args()

    if args.google_spreadsheet_url:

        spreadsheet_id = extract_spreadsheet_id(args.google_spreadsheet_url)

    else:

        spreadsheet_id = args.google_spreadsheet_id

    if not spreadsheet_id:

        print("[INFO] Invalid Google Spreadsheet URL or ID")

        return

    sheet_info = get_spreadsheet_info(spreadsheet_id)

    if sheet_info:

        for name, gid in sheet_info.items():

            download_sheet_as_csv(spreadsheet_id, name, gid, args.output)

    else:

        print("[INFO] Failed to extract sheet information")

if __name__ == "__main__":

    main()

```

Bash 筆記 - 使用 s3fs、mysqldump、find 透過 s3 定期備份 MySQL 資料庫資料

由於資料庫恰好有 timestamp 這個數值，除設計完 incremental backup 方式後（Bash 筆記 - 使用時間區間備份 MySQL 資料表），而備份好的資料又感到麻煩，所以就往 s3 丟吧！而 s3 的丟法就用 s3fs 來執行。

流程：

1. 執行 db incremental backup
2. 使用 s3fs 掛載 s3 空間
3. 確認 s3 是否已有資料，若沒有則複製過去

其中我把 db backup 的(tar.bz2)產物都有做一次 checksum (tar.bz2.md5)，因此判斷是否已在 s3 上時，可以判斷 checksum file 即可：

mkdir -p /data/fuse-tmp/my-s3-bucket
mkdir -p /data/s3/my-s3-bucket
touch /data/s3/my-s3-bucket.password
s3fs my-s3-bucket /data/s3/my-s3-bucket -d -o use_cache=/data/fuse-tmp -o passwd_file=/data/s3/my-s3-bucket.password

find /data/db/ -name "*.bz2" -exec bash -c "test -e {}.md5 && test ! -e /data/s3/my-s3-bucket/\`basename {}\`.md5 && echo 'upload {} , {}.md5 ... ' && time cp {} {}.md5 /data/s3/smy-s3-bucket/ && echo 'done' " \;

sync;sync;sync

fusermount -u /data/s3/my-s3-bucket

其中，/data/db 是擺放一堆 db.sql.tar.bz2 跟 db.sql.tar.bz2.md5 的位置，而我讓備份過程中最後產出 db.sql.tar.bz2.md5 檔，因此先判斷 /data/db/db.sql.tar.bz2.md5 是否產生也代表備份流程是否跑完，接著才檢查 /data/s3/my-s3-bucket/db.sql.tar.bz2.md5 資料來決定是否要上傳到 s3 。

最後，s3 還可以開啟版本控制，這樣可以避免不小心蓋掉檔案的問題 :) 達成完整可自動化的備份方案

GAE 筆記 - 將 Google App Engine 之儲存資料 DataStore 匯出處理

前陣子寫小的程式不斷地將資料儲存起來，最近想處理一下，打算用 offline 的方式處理。接著就是要把資料先匯出，再用程式分析每筆資料。

匯出方式很簡單，只需到 Google App Engine -> Application -> YourApp -> Data -> Datastore Admin -> 勾選想要的資料表(Entities) -> Backup Entities ，不一會兒就可看到 Backups 有資料了。

接著是下載方式，有一派是說用 gsutils ，但我沒試成功，可能跟 gsutils 使用時機有關，我則是透過網頁把各個檔案下載來使用：Google App Engine -> Application -> YourApp -> Data -> Blob Viewer -> 可看到幾個小檔資料，就簡易人工點擊下載。

最後，寫簡單的程式來分析吧：

import webapp2

from google.appengine.api.files import records
from google.appengine.datastore import entity_pb
from google.appengine.api import datastore

class MainPage(webapp2.RequestHandler):
        def get(self):
                self.response.headers['Content-Type'] = 'text/plain'
                for record in records.RecordsReader(open('file-part-1', 'r')):
                        entity_proto = entity_pb.EntityProto(contents=record)
                        entity = datastore.Entity.FromPb(entity_proto)
                        self.response.write(entity)

app = webapp2.WSGIApplication([
    ('/', MainPage),
], debug=True)

[Linux] 透過 sed 將 MySQL Table 資料轉成 csv 格式 @ Ubuntu 14.04

印象中之前找資料時，有發現 MySQL Server 可以輸出 CSV 格式到本地端。但是，對於無法存取到 DB Server 的檔案系統的環境時(AWS RDS)，只好手動處理一下 :P

用法：

$ mysql -h rds-server -u root -p -e "SELECT field1, field2 FROM db_name.table_name" | grep "^Keyword" | sed 's/\t/","/g' | sed 's/^/"/g' | sed 's/$/"/g' > db-table-export.csv

其中 grep 那段只是為了mysql 輸出時，略過第一行 XD

AWS 筆記 - 從 Godaddy 搬到 AWS Route 53

AWS Route 53 有很多收費機制，但為了 GeoDNS 的使用，開始來搬遷。原先購買的 Domain name 是在 Godaddy 上購買以維護的，整個匯出流程還滿容易的。

1. AWS Route 53: Create Hosted Zone
   
   
2. AWS Route 53: Import Zone File
   
   
3. Godaddy: Export DNS Setttings
   
   
4. AWS Route 53: Import Zone File 貼上需要的，如 A系列、CNAME系列、MX系列等
   
   
5. Godaddy： Set NameServer，改用 AWS Route 53 提供的清單
   
   

最後，可以用 dig 去追蹤看看，是不是真的改用 AWS Route 53 提供的 NS 囉

$ dig +trace example.com