{"id":297,"date":"2020-01-10T16:35:00","date_gmt":"2020-01-10T08:35:00","guid":{"rendered":"https:\/\/kuo.us.to\/wordpress\/?p=297"},"modified":"2023-02-01T14:56:42","modified_gmt":"2023-02-01T06:56:42","slug":"python-%e5%ad%b8%e7%bf%92%e7%ad%86%e8%a8%98-gspread%e8%ae%80%e5%8f%96google-sheets","status":"publish","type":"post","link":"https:\/\/kuo.us.to\/wordpress\/python%e5%ad%b8%e7%bf%92%e7%ad%86%e8%a8%98\/297\/","title":{"rendered":"Python \u5b78\u7fd2\u7b46\u8a18- gspread\u8b80\u53d6Google Sheets"},"content":{"rendered":"

Python \u8981\u8b80\u6b0aGoogle Sheets\u8981\u5148\u958b\u555fGoogle<\/span>\u96f2\u7aef\u786c\u789f\uff08<\/span>Google Drive<\/span>\uff09\u548c<\/span>Google Sheets<\/span>\u7684<\/span>API\u3002<\/span>
\n
\n<\/span>\u76f8\u95dc\u53c3\u8003\u6b65\u9a5f\uff1a<\/span>
\n
\n<\/span>Python \u5b78\u7fd2\u7b46\u8a18- \u958b\u555fGoogle\u96f2\u7aef\u786c\u789f\u548cGoogle Sheets\u7684API\u529f\u80fd<\/a><\/span><\/p>\n

\u5b8c\u6210\u4e0a\u9762\u7684\u6b65\u9a5f\u5f8c\uff0c\u63a5\u4e0b\u4f86\u7684\u6b65\u9a5f\u5982\u4e0b\uff1a<\/p>\n

1. \u627e\u51fa\u524d\u6587\u958b\u555fGoogle\u96f2\u7aef\u786c\u789f\u6642\u4e0b\u8f09\u7684\u91d1\u9470JSON\u6a94\u6848\u3002<\/p>\n

\u4ee5Windows\u5167\u5efa\u7684\u8a18\u4e8b\u672c\u958b\u555f\uff0c\u627e\u5230client_email\u9019\u884c\uff0c\u5c07\u5857\u6389\u7684\u90a3\u4e32\u8907\u88fd\u8d77\u4f86\u5099\u7528\u3002<\/p>\n

<\/a><\/div>\n

2. \u8a2d\u5b9aGoogle Sheets\u5171\u7528<\/p>\n

\u5148\u5230Google\u96f2\u7aef\u786c\u789f\u9078\u64c7\u6a94\u6848\uff0c\u6309\u53f3\u9375\u9ede\u9078\u5171\u7528<\/p>\n

<\/a><\/div>\n
<\/div>\n
<\/div>\n
\u628a\u4e0a\u9762client_email\u4e2d\u8907\u88fd\u7684\u5167\u5bb9\u8cbc\u5230\u8207\u4ed6\u4eba\u5171\u7528\u4e2d\uff0c\u9ede\u9078\u50b3\u9001<\/div>\n
<\/a><\/div>\n
<\/div>\n
\u4f60\u78ba\u5b9a\u55ce\uff1f\u9ede\u9078\u662f<\/div>\n
<\/a><\/div>\n
<\/div>\n
3. \u958b\u555fGoogle Sheets\u6a94\u6848\uff0c\u67e5\u770b\u5167\u5bb9<\/div>\n
<\/a><\/div>\n
<\/div>\n
<\/div>\n
\u628a\u7db2\u5740\u4e2dd\/\u81f3\/edit\u4e2d\u7684\u6587\u5b57\u8907\u88fd\u4e0b\u4f86\uff0c\u5230\u6642\u5019\u7a0b\u5f0f\u6703\u7528\u5230<\/div>\n
<\/a><\/div>\n
<\/div>\n
<\/div>\n
3. \u958b\u59cb\u5beb\u7a0b\u5f0f\u4e86<\/div>\n
<\/div>\n
auth_json_path\u9019\u88cf\u5c31\u662f\u653e\u4f60\u7684json\u6a94<\/div>\n
spreadsheet_key_path\u9019\u88cf\u5c31\u662f\u653e\u4e0a\u9762d\/\u81f3\/edit\u4e2d\u7684\u6587\u5b57<\/div>\n
<\/div>\n
\n
\n
\n
\n
import<\/span> pandas<\/span> as<\/span> pd<\/span>\nimport<\/span> gspread<\/span>\nfrom<\/span> oauth2client.service_account<\/span> import<\/span> ServiceAccountCredentials<\/span> \n\ndef<\/span> auth_gss_client<\/span>(<\/span>path<\/span>,<\/span> scopes<\/span>):<\/span>\n    credentials<\/span> =<\/span> ServiceAccountCredentials<\/span>.<\/span>from_json_keyfile_name<\/span>(<\/span>path<\/span>,<\/span> scopes<\/span>)<\/span>\n    return<\/span> gspread<\/span>.<\/span>authorize<\/span>(<\/span>credentials<\/span>)<\/span>\n\n\nauth_json_path<\/span> =<\/span> 'auth_mykey.json'<\/span> #\u7531\u525b\u525b\u5efa\u7acb\u51fa\u7684\u6191\u8b49\uff0c\u653e\u7f6e\u76f8\u540c\u76ee\u9304\u4ee5\u4f9b\u5f15\u5165<\/span>\ngss_scopes<\/span> =<\/span> [<\/span>'https:\/\/spreadsheets.google.com\/feeds'<\/span>]<\/span> #\u6211\u5011\u60f3\u8981\u53d6\u7528\u7684\u7bc4\u570d<\/span>\ngss_client<\/span> =<\/span> auth_gss_client<\/span>(<\/span>auth_json_path<\/span>,<\/span> gss_scopes<\/span>)<\/span> #\u547c\u53eb\u6211\u5011\u7684\u51fd\u5f0f<\/span>\n\n#\u5f9e\u525b\u525b\u5efa\u7acb\u7684sheet\uff0c\u628a\u7db2\u5740\u4e2d https:\/\/docs.google.com\/spreadsheets\/d\/\u3014key\u3015\/edit \u7684 \u3014key\u3015\u7684\u503c\u4ee3\u5165 <\/span>\nspreadsheet_key_path<\/span> =<\/span> '1J87UIVms_5xYhDX_r1234podusj_ZP9807G0bM'<\/span>\n\n#\u6211\u5011\u900f\u904eopen_by_key\u9019\u500bmethod\u4f86\u958b\u555fsheet<\/span>\nsheet<\/span> =<\/span> gss_client<\/span>.<\/span>open_by_key<\/span>(<\/span>spreadsheet_key_path<\/span>)<\/span>.<\/span>sheet1<\/span>\ndf<\/span> =<\/span> pd<\/span>.<\/span>DataFrame<\/span>(<\/span>sheet<\/span>.<\/span>get_all_records<\/span>())<\/span>\n\ndf<\/span>\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n
Out[1]:<\/div>\n
\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
<\/th>\n\u5b78\u6821\u4ee3\u78bc<\/th>\n\u5b78\u6821\u540d\u7a31<\/th>\n\u65e5\u9593\u2215\u9032\u4fee\u5225<\/th>\n\u7b49\u7d1a\u5225<\/th>\n\u4e00\u5e74\u7d1a\u7537\u751f<\/th>\n\u4e00\u5e74\u7d1a\u5973\u751f<\/th>\n\u4e8c\u5e74\u7d1a\u7537\u751f<\/th>\n\u4e8c\u5e74\u7d1a\u5973\u751f<\/th>\n\u4e09\u5e74\u7d1a\u7537\u751f<\/th>\n\u4e09\u5e74\u7d1a\u5973\u751f<\/th>\n…<\/th>\n\u4e94\u5e74\u7d1a\u7537\u751f<\/th>\n\u4e94\u5e74\u7d1a\u5973\u751f<\/th>\n\u516d\u5e74\u7d1a\u7537\u751f<\/th>\n\u516d\u5e74\u7d1a\u5973\u751f<\/th>\n\u4e03\u5e74\u7d1a\u7537\u751f<\/th>\n\u4e03\u5e74\u7d1a\u5973\u751f<\/th>\n\u5ef6\u4fee\u751f\u7537\u751f<\/th>\n\u5ef6\u4fee\u751f\u5973\u751f<\/th>\n\u7e23\u5e02\u540d\u7a31<\/th>\n\u9ad4\u7cfb\u5225<\/th>\n<\/tr>\n<\/thead>\n
0<\/th>\n1<\/td>\n\u570b\u7acb\u653f\u6cbb\u5927\u5b78<\/td>\nD \u65e5<\/td>\nD \u535a\u58eb<\/td>\n96<\/td>\n85<\/td>\n82<\/td>\n78<\/td>\n93<\/td>\n59<\/td>\n…<\/td>\n71<\/td>\n52<\/td>\n58<\/td>\n46<\/td>\n74<\/td>\n40<\/td>\n0<\/td>\n0<\/td>\n30 \u81fa\u5317\u5e02<\/td>\n1 \u4e00\u822c<\/td>\n<\/tr>\n
1<\/th>\n1<\/td>\n\u570b\u7acb\u653f\u6cbb\u5927\u5b78<\/td>\nD \u65e5<\/td>\nM \u78a9\u58eb<\/td>\n624<\/td>\n742<\/td>\n632<\/td>\n695<\/td>\n348<\/td>\n431<\/td>\n…<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n30 \u81fa\u5317\u5e02<\/td>\n1 \u4e00\u822c<\/td>\n<\/tr>\n
2<\/th>\n1<\/td>\n\u570b\u7acb\u653f\u6cbb\u5927\u5b78<\/td>\nD \u65e5<\/td>\nB \u5b78\u58eb<\/td>\n899<\/td>\n1287<\/td>\n890<\/td>\n1332<\/td>\n878<\/td>\n1325<\/td>\n…<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n326<\/td>\n463<\/td>\n30 \u81fa\u5317\u5e02<\/td>\n1 \u4e00\u822c<\/td>\n<\/tr>\n
3<\/th>\n1<\/td>\n\u570b\u7acb\u653f\u6cbb\u5927\u5b78<\/td>\nN \u8077<\/td>\nM \u78a9\u58eb<\/td>\n303<\/td>\n248<\/td>\n253<\/td>\n259<\/td>\n201<\/td>\n210<\/td>\n…<\/td>\n53<\/td>\n47<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n30 \u81fa\u5317\u5e02<\/td>\n1 \u4e00\u822c<\/td>\n<\/tr>\n
4<\/th>\n2<\/td>\n\u570b\u7acb\u6e05\u83ef\u5927\u5b78<\/td>\nD \u65e5<\/td>\nD \u535a\u58eb<\/td>\n225<\/td>\n99<\/td>\n201<\/td>\n71<\/td>\n189<\/td>\n73<\/td>\n…<\/td>\n158<\/td>\n50<\/td>\n112<\/td>\n43<\/td>\n168<\/td>\n58<\/td>\n0<\/td>\n0<\/td>\n18 \u65b0\u7af9\u5e02<\/td>\n1 \u4e00\u822c<\/td>\n<\/tr>\n
…<\/th>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n
807<\/th>\n1293<\/td>\n\u5d07\u4ec1\u91ab\u8b77\u7ba1\u7406\u5c08\u79d1\u5b78\u6821<\/td>\nD \u65e5<\/td>\n5 \u4e94\u5c08<\/td>\n89<\/td>\n550<\/td>\n89<\/td>\n610<\/td>\n81<\/td>\n620<\/td>\n…<\/td>\n70<\/td>\n560<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n15<\/td>\n53<\/td>\n10 \u5609\u7fa9\u7e23<\/td>\n2 \u6280\u8077<\/td>\n<\/tr>\n
808<\/th>\n3002<\/td>\n\u81fa\u5317\u5e02\u7acb\u5927\u5b78<\/td>\nD \u65e5<\/td>\nD \u535a\u58eb<\/td>\n14<\/td>\n20<\/td>\n16<\/td>\n14<\/td>\n17<\/td>\n18<\/td>\n…<\/td>\n13<\/td>\n22<\/td>\n9<\/td>\n12<\/td>\n22<\/td>\n38<\/td>\n0<\/td>\n0<\/td>\n30 \u81fa\u5317\u5e02<\/td>\n1 \u4e00\u822c<\/td>\n<\/tr>\n
809<\/th>\n3002<\/td>\n\u81fa\u5317\u5e02\u7acb\u5927\u5b78<\/td>\nD \u65e5<\/td>\nM \u78a9\u58eb<\/td>\n124<\/td>\n252<\/td>\n112<\/td>\n228<\/td>\n82<\/td>\n134<\/td>\n…<\/td>\n10<\/td>\n39<\/td>\n12<\/td>\n34<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n30 \u81fa\u5317\u5e02<\/td>\n1 \u4e00\u822c<\/td>\n<\/tr>\n
810<\/th>\n3002<\/td>\n\u81fa\u5317\u5e02\u7acb\u5927\u5b78<\/td>\nD \u65e5<\/td>\nB \u5b78\u58eb<\/td>\n566<\/td>\n725<\/td>\n524<\/td>\n682<\/td>\n583<\/td>\n692<\/td>\n…<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n165<\/td>\n139<\/td>\n30 \u81fa\u5317\u5e02<\/td>\n1 \u4e00\u822c<\/td>\n<\/tr>\n
811<\/th>\n3002<\/td>\n\u81fa\u5317\u5e02\u7acb\u5927\u5b78<\/td>\nN \u8077<\/td>\nM \u78a9\u58eb<\/td>\n98<\/td>\n271<\/td>\n87<\/td>\n237<\/td>\n44<\/td>\n119<\/td>\n…<\/td>\n19<\/td>\n23<\/td>\n14<\/td>\n28<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n30 \u81fa\u5317\u5e02<\/td>\n1 \u4e00\u822c<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
812 rows \u00d7 22 columns<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
<\/div>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
Python \u8981\u8b80\u6b0aGoogle Sheets\u8981\u5148\u958b\u555fGoogle\u96f2\u7aef\u786c\u789f\uff08Google Drive\uff09\u548cGoo […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advgb_blocks_editor_width":"","advgb_blocks_columns_visual_guide":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}}},"categories":[4],"tags":[255,280,256,281,282,8,9],"author_meta":{"display_name":"mackuo","author_link":"https:\/\/kuo.us.to\/wordpress\/author\/mackuo\/"},"featured_img":null,"coauthors":[],"tax_additional":{"categories":{"linked":["Python\u5b78\u7fd2\u7b46\u8a18<\/a>"],"unlinked":["Python\u5b78\u7fd2\u7b46\u8a18<\/span>"]},"tags":{"linked":["google API<\/a>","google drive<\/a>","google sheets<\/a>","google\u96f2\u7aef\u786c\u789f<\/a>","gspread<\/a>","Pandas<\/a>","Python<\/a>"],"unlinked":["google API<\/span>","google drive<\/span>","google sheets<\/span>","google\u96f2\u7aef\u786c\u789f<\/span>","gspread<\/span>","Pandas<\/span>","Python<\/span>"]}},"comment_count":"0","relative_dates":{"created":"Posted 6 \u5e74 ago","modified":"Updated 3 \u5e74 ago"},"absolute_dates":{"created":"Posted on 2020 \u5e74 1 \u6708 10 \u65e5","modified":"Updated on 2023 \u5e74 2 \u6708 1 \u65e5"},"absolute_dates_time":{"created":"Posted on 2020 \u5e74 1 \u6708 10 \u65e5 \u4e0b\u5348 4:35","modified":"Updated on 2023 \u5e74 2 \u6708 1 \u65e5 \u4e0b\u5348 2:56"},"featured_img_caption":"","series_order":"","_links":{"self":[{"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/posts\/297"}],"collection":[{"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/comments?post=297"}],"version-history":[{"count":1,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/posts\/297\/revisions"}],"predecessor-version":[{"id":666,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/posts\/297\/revisions\/666"}],"wp:attachment":[{"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/media?parent=297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/categories?post=297"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/tags?post=297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}