# Python 教學 | pygsheets 自動寫入資料到 Google Sheet <p> 上次教過大家用 <a href="https://showsun63.blogspot.com/2023/07/openpyxl.html">python 去讀寫 excel</a>,這次要分享用 python 串接 Google Sheet,對 Google Sheet 做讀寫。 </p> 文章會分為三大部分: 1. 啟用 Google Sheet API 2. python 連接 Google Sheet 3. pygsheets 資料讀寫 <h2>啟用 Google Sheet API</h2> <h3>step 1. 建立 GCP 專案</h3> <ol> <li>前往 <a href="https://console.cloud.google.com/welcome" target="blank">GCP</a> 網頁</li> <li>點選 [選取專案] > [新增專案]</li> <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQOcgthweOx-1bZ4_ZLyLgcJ0EXtmWhgVcS6_BxO4srkJAFYVGqwgqWqXXtQJPN2f0aP5GnfLhULEd5wT29WGnJgAKULG9G5jU2BIiTGBqZescCtkr3nhpccSdfrzN9EOaBi-y_r5Fxa_uKm-topLA7KrDUWzPI5wHIU2Gne_yvBUy-kE/s1600/gcp_project.jpg" width="80%"/> <li>填寫專案資訊,點選 [建立]</li> <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgoND7OpKyW8K2gNMY1Fe7DVY7o1EH3C2Lg6YGQ-2xJvgeKY5wh5ZcXSbeaPgNktkBqVm4ifb3YLp2Ay5itnk9loLO_yDb2MjFVPA5YHlY0KFUMc9AxHkOC4wmgjJ0z3HAgpPYVw6jZhw_kj6SR0o-pZS0nDzEReyDXeMLWNFDrYI0wrYU/s1600/gcp_project2.jpg" width="80%"/> </ol> <h3>step 2. 啟用 API</h3> <ol> <li>前往 <a href="https://console.cloud.google.com/marketplace/product/google/sheets.googleapis.com">Google Sheet API</a>,先點選 [選取專案] 選剛剛建立的專案,再點選 [啟用]</li> <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCVhnfnYJHaerr_vj71LGVvuIKsouufCVohLx9S_Ns6mU6qYJt4cSdyfPtknYfQHLX71726cyjWFCdDwD4GhTgyfaUBKxMdSpOhgSYUUloUdSILRWD_trz98MW583X-tRI1KEFnKnjcT61RdLnz25w03ozwACxL90HwuhkyMKXc2NATiY/s1600/gsheet_api.jpg" width=80%"/> <li>點選 [憑證] > [建立憑證] > [服務帳戶],填寫相關資料後點選 [完成]</li> 我們會獲得一個服務帳戶,之後要和這個帳戶共享 google sheet。<br /> <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRu0r1AmARAwS6EsPIXYLPvVMUSxNixHRWSdrNmjHnLPSA64Wv8MTjO6hlgn5pM_PHuaacqCU3XrdMVaDGv8xe_yC0prEmNEbYSev5qYpwfShY0o0w6rSrZbLTTeGPgeH34hJb0fnDpwF_pLWLaut1QP-LUttZLva_NSSmEV5LEns4mWM/s1600/account.jpg" width="80%"/> <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtRxF_j4MrqIuMbeqzuIoA5D2zLhyphenhyphen_7CCcEUD4byXI_eC50vqmapvP4V1LIV_brU0VdNhUVhrgmZdDT36nojczchHtMxpXqXRV7suXRec83hy7jcTgAR6xubYZ8HYwexWbWgLJ_tooXorJ-WR_Ae5A4kBM-E_GDUJX8gIj6rs12JOmGBM/s1600/account2.jpg" width="80%"/> <li>點選剛剛建立的帳戶 > [金鑰] > [新增金鑰] > [建立新的金鑰] > [json] > [建立]</li> 這時候就會下載一個 json 檔案,這個檔案就是連接 Google Sheet 的密碼,記得妥善保管,不要外流。<br /> <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnTK-S2pK5ijfcCJCtJC5R3uwMtDxT-kldtuzdbeNzx4_u0quskGpKjbZpiNIY7KnatIxZyHbtSPpj3sJv95soNqTWXLboONn6AnUjkQjJTxnyVRdZOEO8rNOPz8cv8KJR3o29QNiV7gAJjPZi7uCpT09mSBxTxj9cJACCl0sPaVeOsHg/s1600/key.jpg" width="80%" /> <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJCDdJoLFyFF-AnV3xyl33PIW7UXL5ySIzzMMJZAsUPxRWV2gB9e2NqKv9YpH24_8IAver5M8j7LRIT1KtqLtmSflkbPyRcIRxMNDxJeujzi5IKGg0cYOcE3YMSmotiyX7V9T2I06tGfMtC7n8Q2vB_q7MeaZ2qg8BlIWsyQ5SjhY8Uqc/s1600/key2.jpg" width="80%" /> <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9TxSAeyOzTizf7T9OhB0Gu9iZnbKwpT4HhPQ2FLUzox07gfM4IFbjjpC-dlggfRjE2xD0LrvilLJ2lp1F3weBE5FiOehsxeNwddGmZeOUp-fN-UPwhSTTzzGpSfAkw_H-talHhaSIGyaIxr8ZmNJgRy2q0TFP4oh4VzDaaPRSzPr3Jhs/s1600/key3.jpg" width="80%"/> </ol> <h2 id="pytogsheet">python 連接 Google Sheet</h2> <h3>step 1. 建立 Google Sheet</h3> <ol> <li>到 <a href="https://docs.google.com/spreadsheets/u/0/">Google Sheet</a> 建立一個試算表</li> <li>點選 [共用],輸入剛剛服務帳戶的 email</li> <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgW-XZghecfxFtQXw_IAkN1lLg9b9-qyu0bCpx9ggoNfSOB-Rvpqe5t1kjXzZk7Pjy73KkKoiG4xLRRPduTtYRgay5ZqnNYfAPJO45orKfjKfAaY1f0nJ7KUuN8KcUGminjwVWUnrw2Ww_7ghf0JHPYqLoYt3hVkW56OaE_MTGTTs7B60/s1600/share.jpg" width="80%"/> </ol> <h3>step 2. 建立 python 檔</h3> <ol> <li>安裝套件,終端機輸入 <code>pip install pygsheets</code></li> <li>Python 程式碼</li> 連接 Google Sheet 會需要兩項資訊,一個是剛剛下載的金鑰,另一個是 Google Sheet 的網址(直接複製網址列的網址就是了) ```python import pygsheets key = "xxx.json" # json 金鑰的檔案路徑,記得換成自己的 gc = pygsheets.authorize(service_file = key) url = "https://docs.google.com/spreadsheets/xxx" # Google Sheet 的網址,記得換成自己的 sheet = gc.open_by_url(url) ``` </ol> <h2>pygsheets 資料讀寫</h2> <ol> <li>開啟工作表</li> <code>work_sheet = sheet.worksheet_by_title("工作表1")</code> <li>讀取 Google Sheet</li> 取得每一小格(cell)有兩種方法,第一種是利用字串 "A1",第二種是利用 tuple 給行列的值<br /> <code>print(work_sheet.cell("A1").value)</code><br /> <code>print(work_sheet.cell((1,2)).value)</code> <li>寫入 Google Sheet</li> cell 的取得方式和剛剛一樣,再利用 = 把值寫入即可<br /> <code>work_sheet.cell("A1").value = "apple"</code><br /> <code>work_sheet.cell("A1").value = "banana"</code> </ol> 這篇文章就先寫到這裡啦,之後再出一篇文章教大家更多的讀寫技巧,歡迎大家先到我的[部落格](https://showsun63.blogspot.com)逛逛~