الشروع في العمل مع صفحات Google في بيثون. من التسجيل إلى قراءة البيانات

لبعض الوقت ، تمكنت من تحميل البيانات إلى Excel ، لكن وزارة الدفاع تتغير ، ويريد المستخدمون ذلك في السحب.

بعد أن بدأت ترجمة عدد من المشاريع إلى Python ، قررت أن الوقت قد حان لتغيير (أو الإضافة إلى) Excel باستخدام شيء أكثر حداثة.

عندما واجهت للمرة الأولى الحاجة إلى العمل مع جداول بيانات Google من بيثون ، كنتُ على وعي بأن كل هذا يمكن القيام به ببضع نقرات. تبين أن الواقع أقل وردية ، ولكن ليس لدينا كرة أرضية أخرى.

ساعدتني المقالات كثيرًا:

• نقوم بإنشاء جدول بيانات Google جميل من برنامجنا (باستخدام Google Sheets API v4)
• البدء باستخدام Google Sheets API v4 /
• العمل مع Google Drive API مع Python

كالعادة - عندما تواجه شيئًا ما لأول مرة ، فإنك تواجه مجموعة من الأسئلة التي تسببت في وقت لاحق فقط في حيرة - كيف يمكن لأي شخص حتى التفكير في هذا الأمر. الابتدائية نفسه!

ربما مشيت بعيدًا - سأكون سعيدًا إذا صححتني.

تم تنفيذ جميع الإجراءات على جهاز كمبيوتر يعمل بنظام Windows + Python 3.6.6 ، كما تم استخدام Jupyter Notebook.

الصعوبات الرئيسية التي واجهتني في مرحلة الإعدادات الأولية. العثور على رمز عملي ليس بالأمر الصعب.

الكود المستخدم في المقال متاح في المستودع.

الاشتراك في خدمات Google وتثبيت المكتبات

للعمل مع الجداول ، تحتاج إلى التسجيل في Google ، وإعداد المشروع وتثبيت المكتبات اللازمة.

الوثائق الرسمية باللغة الإنجليزية هنا .

تحتاج أولاً إلى التسجيل على gmail.com (يمكنك القيام بذلك بنفسك). فأنت بحاجة إلى إنشاء مشروع (حيث توفر Google الوصول إلى خدماتها).

هذه عملية طويلة ومملة تتيح لك أن تفهم لماذا لا تُعتبر واجهات Google الأكثر ملاءمةً وبديهية (يعتقد البعض أن الشبكة الاجتماعية في Google+ لم تقلع لهذا السبب).

للقيام بذلك ، انتقل إلى console.developers.google.com/cloud-resource-manager وانقر على "إنشاء مشروع"



أدخل اسم المشروع وانقر فوق "إنشاء"



في قائمة المشاريع المحدثة ، انتقل إلى قائمة "أذونات"


في النافذة التي تفتح ، انقر فوق "إضافة" ، وأدخل عنوان بريدك الإلكتروني من نطاق gmail.com وحدد مجموعة "المشروع" - "المالك"



حفظ التغييرات.

قد يبدو من الغريب أنك أنشأت مشروعًا ولكنك مجبر على إصدار حقوق لنفسك. وهذا أمر غريب في الواقع ، لكنه كان يجب اتباعه في وقت كتابة هذا المقرر ، حتى يبدأ كل شيء في العمل كما ينبغي.

تفضل بزيارة console.developers.google.com/cloud-resource-manager مرة أخرى

حدد قائمة الإعدادات في مشروعك



في النافذة التي تفتح ، حدد "حسابات الخدمة" ثم "إنشاء حساب خدمة"



أدخل اسم الحساب وانقر على "إنشاء"



حدد دور المالك وانقر فوق "متابعة".



في النافذة التي تظهر ، انقر فوق "إنشاء مفتاح"



حدد نوع مفتاح json وانقر فوق "إنشاء"



سيتم إنشاء ملف مع مفاتيح وتنزيله على الفور. احفظه ، بفضله سنكون قادرين على الوصول إلى خدمات Google.

انقر على الزر بثلاث ضربات أفقية ، على يسار النقش "واجهات برمجة تطبيقات Google" ، وحدد "API والخدمات" ، وفيه العنصر الفرعي "لوحة التحكم".



في النافذة التي تفتح ، انقر فوق "تمكين API والخدمات"



اكتب "google drive" في شريط البحث وانقر على خدمة "Google Drive API"



انقر فوق تمكين



سيُعلمك الموقع بأن واجهة برمجة التطبيقات ممكّنة ويحذرك من وجوب إنشاء بيانات الاعتماد. تجاهل هذا التحذير (لقد أنشأنا بالفعل حساب خدمة).



ارجع إلى لوحة التحكم



في النافذة التي تفتح ، انقر فوق "تمكين API والخدمات"



أدخل "ورقة" في شريط البحث وانقر فوق خدمة "Google Sheets API"



تأكد من أن API متصل. يجب أن يتم تشغيله تلقائيًا عند توصيل Google Drive API. إذا كان متصلاً ، فسترى زر "إدارة واجهة برمجة التطبيقات" ، إن لم يكن ، زر "تمكين". قم بتشغيله إذا لزم الأمر.

آخر مرة ، تفضل بزيارة console.developers.google.com/cloud-resource-manager

حدد قائمة الإعدادات في مشروعك


في النافذة التي تفتح ، حدد "حسابات الخدمة" ، ثم انسخ وحفظ عنوان البريد الإلكتروني لحساب الخدمة. سيكون في متناول اليد بالنسبة لك لمنح حق الوصول إلى الجداول.



ننتقل الآن إلى تثبيت المكتبات. قم بتشغيل الأمر في وحدة التحكم

pip3 install --upgrade google-api-python-client 

وبعد ذلك

 pip3 install oauth2client 

من المحتمل أنه عند تشغيل الأمر الثاني ، ستتلقى رسالة مفادها أن مكتبة oauth2client مثبتة بالفعل.

انتقل إلى raw.githubusercontent.com/gsuitedevs/python-samples/master/sheets/quickstart/quickstart.py

اضغط على زر الماوس الأيمن واختر "حفظ باسم"



احفظ الملف كـ quickstart.py

وتشغيله مع الأمر

 python quickstart.py 

سيتم فتح صفحة جديدة في المتصفح (ربما ستقول أن الصفحة غير آمنة ، ولكن يجب المضي قدمًا بجرأة) ويجب عليك قبول الشروط.

على هذا اكتمال طريقنا.

ملء وتنسيق الجدول

إنشاء الجدول الأول

 #   import httplib2 import apiclient.discovery from oauth2client.service_account import ServiceAccountCredentials CREDENTIALS_FILE = 'seraphic-effect-248407-7ac2c44ec709.json' #     ,     #     credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']) httpAuth = credentials.authorize(httplib2.Http()) #    service = apiclient.discovery.build('sheets', 'v4', http = httpAuth) #      4  API spreadsheet = service.spreadsheets().create(body = { 'properties': {'title': '  ', 'locale': 'ru_RU'}, 'sheets': [{'properties': {'sheetType': 'GRID', 'sheetId': 0, 'title': '  ', 'gridProperties': {'rowCount': 100, 'columnCount': 15}}}] }).execute() spreadsheetId = spreadsheet['spreadsheetId'] #    print('https://docs.google.com/spreadsheets/d/' + spreadsheetId) 

إذا حدث كل شيء دون أخطاء ، فسيتم عرض رابط إلى الجدول.

يتم استخدام معرف الملف في هذا الرابط ، ونحن نحفظه في جدول البيانات المتغير وسوف نستخدمه في المستقبل.

اتبع ذلك. ستبلغك Google بأنه ليس لديك حق الوصول



لا تطلب إذن! ستتلقى إشعارًا بعدم إمكانية تسليم خطاب طلب إلى العنوان الذي عينته Google نفسها لحساب النظام. لكن لا يمكنك تغيير هذا العنوان. ربما هذا لا يعمل فقط في الوضع الحر.

ولكن يمكننا منح أنفسنا إمكانية الوصول من خلال Google Drive. تحتاج إلى استبدال my_test_address@gmail.com باسمك.

 driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth) #    Google Drive  3  API access = driveService.permissions().create( fileId = spreadsheetId, body = {'type': 'user', 'role': 'writer', 'emailAddress': 'my_test_address@gmail.com'}, #     fields = 'id' ).execute() 

الآن لديك حق الوصول ، لا تغلق الجدول ، سنقوم بإدارته وسننظر على الفور في التغييرات.

كل وثيقة لها رمزها الخاص - جدول البيانات - وهذا هو بالضبط ما يتم عرضه في شريط العناوين عندما نفتح الجدول في المتصفح (في عنوان URL للصفحة مع الجدول المفتوح ، بين "https://docs.google.com/spreadsheets/d/" و "/ edit # gid = 0").

لقد حفظناه في جدول البيانات المتغير وسنواصل العمل معه.

أولاً ، نظرية صغيرة.

في كل ملف (جدول بيانات) توجد علامات تبويب أوراق (ورقة).

كل ورقة لها رمز رقمي خاص بها (sheetId). تحتوي الورقة الأولى التي تم إنشاؤها في المستند على هذا المعرف يساوي 0. أما الأوراق المتبقية فيحتوي على معرف غير صفري مختلف تمامًا (أي ، لا يتم ترقيمها على التوالي).

تأكد من هذا

 #   results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ { "addSheet": { "properties": { "title": "  ", "gridProperties": { "rowCount": 20, "columnCount": 12 } } } } ] }).execute() #   ,  Id   spreadsheet = service.spreadsheets().get(spreadsheetId = spreadsheetId).execute() sheetList = spreadsheet.get('sheets') for sheet in sheetList: print(sheet['properties']['sheetId'], sheet['properties']['title']) sheetId = sheetList[0]['properties']['sheetId'] print('     Id = ', sheetId) 

سيظهر شيء مثل هذا على الشاشة:

0 ورقة رقم واحد
415832263 ورقة أخرى
سنستخدم ورقة ذات معرف = 0

في الواقع ، الورقة الأولى لها معرف صفر ، والثانية مرقمة بشكل مختلف.

سؤال واحد آخر: كيفية تحديد نطاقات الخلايا. على ما يبدو ، تم تطوير جداول بيانات Google بواسطة فرق مختلفة ، بتوجيه من مديرين مختلفين وبمساعدة مهندسين مختلفين. لأنه يتم تعيين إحداثيات الخلايا بطريقتين مختلفتين.

الخيار 1: في تنسيق النص "الورقة رقم واحد! B2: D5" ، أي اسم الورقة ، متبوعًا بعلامة تعجب ، بعد - الخلية اليسرى العلوية في التنسيق "حرف (عمود) + رقم (سلسلة)" + الخلية اليمنى السفلى بالتنسيق نفسه.

 {"range": "  !B2:D5"} 

الخيار 2: بتنسيق json ، يشير إلى معرف الورقة وإحداثيات الخلايا اليمنى العلوية اليسرى والسفلية في شكل رقمي (رقم الصف ورقم العمود)

 {"range": { "sheetId": sheetId, # ID  "startRowIndex": 1, #    startRowIndex "endRowIndex": 5,#  endRowIndex - 1 (endRowIndex  !) "startColumnIndex": 0, #    startColumnIndex "endColumnIndex": 1 #  endColumnIndex - 1 }} 

وظائف مختلفة استخدام صيغ مختلفة.

الآن نحن نعرف ما يكفي لملء الخلايا بالبيانات ورسم إطار وتسليط الضوء على الرؤوس.

 results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = { "valueInputOption": "USER_ENTERED", #  ,    (  ) "data": [ {"range": "  !B2:D5", "majorDimension": "ROWS", #   ,   "values": [ [" B2", " C2", " D2"], #    ['25', "=6*6", "=sin(3,14/2)"] #    ]} ] }).execute() 

ملء عدة خلايا مع البيانات. لأن تم تحديد المعلمة USER_ENTERED ، يدرك الجدول هذه البيانات كما يتصور إدخال المستخدم - يحول القيم العددية إلى أرقام ، والقيم التي تبدأ بعلامة المساواة في الصيغ.

انظر إلى الجدول الخاص بك ، تمتلئ بالبيانات



ضبط عرض العمود. يمكن أن تقبل وظيفة batchUpdate العديد من الأوامر في وقت واحد ، لذلك سنقوم بتعيين عرض ثلاث مجموعات من الأعمدة مع طلب واحد. هناك عمود واحد في المجموعتين الأولى والثالثة ، واثنين في الثاني.

 results = service.spreadsheets().batchUpdate(spreadsheetId = spreadsheetId, body = { "requests": [ #    A: 20  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", #    "startIndex": 0, #     "endIndex": 1 #    startIndex  endIndex - 1 (endIndex  !) }, "properties": { "pixelSize": 20 #    }, "fields": "pixelSize" # ,     pixelSize } }, #    B  C: 150  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", "startIndex": 1, "endIndex": 3 }, "properties": { "pixelSize": 150 }, "fields": "pixelSize" } }, #    D: 200  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", "startIndex": 3, "endIndex": 4 }, "properties": { "pixelSize": 200 }, "fields": "pixelSize" } } ] }).execute() 

انظر إلى الجدول ، تغيرت عرض الأعمدة.



ارسم إطارًا حول الطاولة

 #   results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ {'updateBorders': {'range': {'sheetId': sheetId, 'startRowIndex': 1, 'endRowIndex': 3, 'startColumnIndex': 1, 'endColumnIndex': 4}, 'bottom': { #      'style': 'SOLID', #   'width': 1, #  1  'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, #   'top': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'left': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'right': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'innerHorizontal': { #       'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'innerVertical': { #       'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}} }} ] }).execute() 

اجمع الخلايا الموجودة أعلى الجدول وأدخل العنوان فيها.

 #   A2:D1 results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ {'mergeCells': {'range': {'sheetId': sheetId, 'startRowIndex': 0, 'endRowIndex': 1, 'startColumnIndex': 1, 'endColumnIndex': 4}, 'mergeType': 'MERGE_ALL'}} ] }).execute() #    results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = { "valueInputOption": "USER_ENTERED", #  ,    (  ) "data": [ {"range": "  !B1", "majorDimension": "ROWS", #   ,   "values": [[" " ] ]} ] }).execute() 

تعيين التنسيق لخلايا رأس الجدول

 #    results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ { "repeatCell": { "cell": { "userEnteredFormat": { "horizontalAlignment": 'CENTER', "backgroundColor": { "red": 0.8, "green": 0.8, "blue": 0.8, "alpha": 1 }, "textFormat": { "bold": True, "fontSize": 14 } } }, "range": { "sheetId": sheetId, "startRowIndex": 1, "endRowIndex": 2, "startColumnIndex": 1, "endColumnIndex": 4 }, "fields": "userEnteredFormat" } } ] }).execute() 

هناك طريقة سهلة لمعرفة ما هو العرض أو اللون الذي تحتاجه لتعيين خلية. للقيام بذلك ، يكفي تنسيق إحدى الخلايا يدويًا وقراءة خصائصها.

 ranges = ["  !C2:C2"] # results = service.spreadsheets().get(spreadsheetId = spreadsheetId, ranges = ranges, includeGridData = True).execute() print(' ') print(results['properties']) print('\n  ') print(results['sheets'][0]['data'][0]['rowData'] ) print('\n ') print(results['sheets'][0]['data'][0]['rowMetadata']) print('\n ') print(results['sheets'][0]['data'][0]['columnMetadata']) 

نحصل على استجابة

البيانات الرئيسية
{'title': 'First test document'، 'locale': 'ru_RU'، 'autoRecalc': 'ON_CHANGE'، 'timeZone': 'Etc / GMT'، 'defaultFormat': {'backgroundColor': {'red' : 1 ، 'أخضر': 1 ، 'أزرق': 1} ، 'حشوة': {'top': 2 ، 'right': 3 ، 'bottom': 2 ، 'left': 3} ، 'verticalAlignment': 'BOTTOM' ، 'wrapStrategy': 'OVERFLOW_CELL' ، 'textFormat': {'foregroundColor': {}، 'fontFamily': 'arial، sans، sans-serif'، 'fontSize': 10، 'bold': False، 'مائل': False ، 'يتوسطه خط': False ، 'تسطير': False}}}

القيم والتلوين
[{'values': [{'userEnteredValue': {'stringValue': 'Cell C2'}،'فعاليةValue ': {' stringValue ':' Cell C2 '}،' formattedValue ':' Cell C2 '،' userEnteredFormat ' : {'backgroundColor': {'red': 1، 'green': 0.6}، 'horizontalAlignment': 'CENTER'، 'textFormat': {'fontSize': 14، 'bold': True، 'italic': True }} ، 'effectFormat': {'backgroundColor': {'red': 1 ، 'green': 0.6} ، 'padding': {'top': 2 ، 'right': 3 ، 'bottom': 2 ، ' left ': 3}،' horizontalAlignment ':' CENTER '،' verticalAlignment ':' BOTTOM '،' wrapStrategy ':' OVERFLOW_CELL '،' textFormat ': {' foregroundColor ': {}،' fontFamily ':' Arial '، 'fontSize': 14 ، 'bold': صواب ، 'مائل': صواب ، 'يتوسطه خط': False ، 'تسطير': False} ، 'hyperlinkDisplayType': 'PLAIN_TEXT'}}]}]]

ارتفاع الخلية
[{'pixelSize': 21}]

عرض الخلية
[{'pixelSize': 150}]

سيعرض هذا الرمز خصائص الخلية C2. يمكنك تحديد الخط وملء اللون يدويًا (في الجدول) ، ثم انظر كيف تنعكس في json.

قراءة البيانات من الجدول

من أجل الكشف الكامل عن ميزات قراءة البيانات ، قمت بملء الخلايا B4 و C7 و D5 يدويًا كما هو موضح في الشكل.



رمز لقراءة البيانات

 ranges = ["  !A2:F8"] # results = service.spreadsheets().values().batchGet(spreadsheetId = spreadsheetId, ranges = ranges, valueRenderOption = 'FORMATTED_VALUE', dateTimeRenderOption = 'FORMATTED_STRING').execute() sheet_values = results['valueRanges'][0]['values'] print(sheet_values) 

يؤدي

[['' ، 'الخلية B2' ، 'الخلية C2' ، 'الخلية D2'] ، ['' ، '25' ، '36' ، '09999996829']]

بعض معلمات الدالة: valueRenderOption - تنسيق لقراءة البيانات الرقمية.

• FORMATTED_VALUE - القراءة بناءً على تنسيق العرض. أي ما كان مرئيا في الجدول سوف تقرأ. على سبيل المثال ، في الخلية D3 ، يكون الرقم هو 0.9999999 ، ولكن التنسيق هو "منزلان عشريان" ، لذلك يتم عرض "1.00" ، وبهذا التنسيق سيتم قراءته.
• UNFORMATTED_VALUE - تتم قراءة محتويات الخلية ، ولا تتم مراعاة التنسيق (على سبيل المثال ، سيتم قراءة 0.9999999)
• الصيغة - يتم عرض الصيغة (في هذه الحالة "= sin (3،14 / 2)". إذا تم إدخال رقم في الخلية ، فسيتم قراءته في هذا الوضع.

هذا الرمز يقرأ البيانات ويعرضها سطرا بسطر. النطاق المقروء A2: F8.
كما رأينا على الشاشة:

• إذا لم يتم ملء أي خلية في السطر الذي تتم قراءته ، فلن يتم عرض البيانات الموجودة على السطر.
• لا يتم عرض البيانات بعد آخر خلية مملوءة.