אינטראקציה עם Google Sheets מג'אווה

1. סקירה כללית

Google Sheets מספק דרך נוחה לאחסון ולתפעל גיליונות אלקטרוניים ולשתף פעולה עם אחרים במסמך.

לפעמים, זה יכול להיות שימושי לגשת למסמכים אלה מיישום, למשל לבצע פעולה אוטומטית. לשם כך, גוגל מספקת את ה- API של Google Sheets שמפתחים יכולים לתקשר איתו.

במאמר זה, אנו נבדוק כיצד אנו יכולים להתחבר ל- API ולבצע פעולות ב- Google Sheets.

2. תלות Maven

כדי להתחבר ל- API ולנהל מסמכים, נצטרך להוסיף את התלות של google-api-client, google-oauth-client-jetty ו- google-api-services-גליונות:

 com.google.api-client google-api-client 1.23.0 com.google.oauth-client google-oauth-client-jetty 1.23.0 com.google.apis google-api-services-sheets v4-rev493-1.23. 0 

3. אישור

ממשק ה- API של Google Sheets דורש הרשאת OAuth 2.0 לפני שאנחנו יכולים לגשת אליו דרך יישום.

ראשית, עלינו להשיג קבוצה של אישורי OAuth, ואז להשתמש בזה ביישום שלנו כדי להגיש בקשה לאישור.

3.1. השגת אישורי OAuth 2.0

כדי להשיג את האישורים, נצטרך ליצור פרויקט במסוף המפתחים של Google ואז להפעיל את ה- Google Sheets API עבור הפרויקט. השלב הראשון במדריך ההפעלה המהירה של גוגל מכיל מידע מפורט כיצד לבצע זאת.

לאחר שהורדנו את קובץ JSON עם פרטי הכניסה, בואו נעתיק את התוכן ב- google-sheets-client-secret.json קובץ ב- src / main / resources ספריה של היישום שלנו.

תוכן הקובץ צריך להיות דומה לזה:

{"installed": {"client_id": "", "project_id": "decisive-octane-187810", "auth_uri": "// accounts.google.com/o/oauth2/auth", "token_uri": " //accounts.google.com/o/oauth2/token "," auth_provider_x509_cert_url ":" // www.googleapis.com/oauth2/v1/certs "," client_secret ":" "," redirect_uris ": [" urn: ietf: wg: oauth: 2.0: oob "," // localhost "]}}

3.2. השגת א תְעוּדָה לְהִתְנַגֵד

אישור מוצלח מחזיר א תְעוּדָה אובייקט שבו אנו יכולים להשתמש כדי לקיים אינטראקציה עם ממשק ה- API של Google Sheets.

בואו ניצור GoogleAuthorizeUtil כיתה עם סטטי לְאַשֵׁר() שיטה הקוראת את התוכן של קובץ JSON לעיל ובונה a GoogleClientSecrets לְהִתְנַגֵד.

לאחר מכן, ניצור GoogleAuthorizationCodeFlow ושלח את בקשת ההרשאה:

מחלקה ציבורית GoogleAuthorizeUtil {public static Credential authorize () זורק IOException, GeneralSecurityException {// בנה GoogleClientSecrets מ- JSON קובץ רשימת סקופים = Arrays.asList (SheetsScopes.SPREADSHEETS); // בנה אישורי החזרת אובייקט אישורים; }}

בדוגמה שלנו, אנו קובעים את יריעות התפשטות היקף מכיוון שאנו רוצים לגשת ל- Google Sheets ולהשתמש בזיכרון DataStoreFactory לאחסון האישורים שהתקבלו. אפשרות נוספת היא שימוש ב- FileDataStoreFactory לאחסון האישורים בקובץ.

לקבלת קוד המקור המלא של ה- GoogleAuthorizeUtil קלהss, בדוק את פרויקט GitHub.

4. בניית ה- גיליונות מופע שירות

לצורך אינטראקציה עם Google Sheets, נצטרך א גיליונות אובייקט שהוא הלקוח לקריאה וכתיבה דרך ה- API.

בואו ניצור SheetsServiceUtil כיתה המשתמשת ב תְעוּדָה התנגד לעיל כדי להשיג מופע של גיליונות:

מחלקה ציבורית SheetsServiceUtil {private static final מחרוזת APPLICATION_NAME = "דוגמה של Google Sheets"; גיליונות סטטיים ציבוריים getSheetsService () זורק את IOException, GeneralSecurityException {Credential credential = GoogleAuthorizeUtil.authorize (); להחזיר Sheets.Builder חדשים (GoogleNetHttpTransport.newTrustedTransport (), JacksonFactory.getDefaultInstance (), אישורים). setApplicationName (APPLICATION_NAME) .build (); }}

לאחר מכן, נסתכל על כמה מהפעולות הנפוצות ביותר שאנו יכולים לבצע באמצעות ה- API.

5. כתיבת ערכים על גיליון

אינטראקציה עם גיליון אלקטרוני קיים מחייבת לדעת את מזהה הגיליון האלקטרוני, אותו אנו יכולים למצוא מכתובת האתר שלו.

לדוגמאות שלנו, נשתמש בגיליון אלקטרוני ציבורי בשם "הוצאות", הנמצא בכתובת:

//docs.google.com/spreadsheets/d/1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI/edit#gid=0

בהתבסס על כתובת אתר זו, אנו יכולים לזהות את מזהה הגיליון האלקטרוני כ- "1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI".

גַם, לקרוא ולכתוב ערכים, אנו נשתמש spreadsheets.values אוספים.

הערכים מיוצגים כ- ValueRange אובייקטים, שהם רשימות של רשימות Java חפצים, המתאים לשורות או עמודות בגיליון.

בואו ניצור כיתת מבחן בה אנו מאותחלים את שלנו גיליונות אובייקט שירות וקבוע SPREADSHEET_ID:

מחלקה ציבורית GoogleSheetsLiveTest {גליונות סטטיים פרטיים גליונות שירות; מחרוזת סטטית פרטית SPREADSHEET_ID = // ... @BeforeClass הגדרת חלל סטטי ציבורית () זורקת GeneralSecurityException, IOException {heetsService = SheetsServiceUtil.getSheetsService (); }}

לאחר מכן נוכל לכתוב ערכים לפי:

  • כתיבה לטווח יחיד
  • כתיבה לטווחים מרובים
  • הוספת נתונים לאחר טבלה

5.1. כתיבה לטווח יחיד

כדי לכתוב ערכים לטווח יחיד בגליון, נשתמש ב- גיליונות אלקטרוניים (). ערכים (). עדכון () שיטה:

@ מבחן ציבורי בטל כאשר WriteSheet_thenReadSheetOk () זורק IOException {ValueRange body = ValueRange חדש (). SetValues ​​(Arrays.asList (Arrays.asList ("הוצאות ינואר"), Arrays.asList ("ספרים", "30"), Arrays.asList ("עטים", "10"), Arrays.asList ("הוצאות פברואר"), Arrays.asList ("בגדים", "20"), Arrays.asList ("נעליים", "5"))); תוצאת UpdateValuesResponse = sheetsService.spreadsheets () .ערכים () .update (SPREADSHEET_ID, "A1", גוף) .setValueInputOption ("RAW") .execute (); }

הנה, אנו יוצרים לראשונה ValueRange התנגד עם מספר שורות המכיל רשימת הוצאות למשך חודשיים.

ואז, אנו משתמשים ב- עדכון() שיטה לבניית בקשה שכותבת את הערכים לגיליון האלקטרוני עם המזהה הנתון, החל בתא "A1".

כדי לשלוח את הבקשה, אנו משתמשים ב- לבצע() שיטה.

אם אנו רוצים שקבוצות הערכים שלנו ייחשבו כעמודות במקום שורות, נוכל להשתמש ב- setMajorDimension ("COLUMNS") שיטה.

אפשרות הקלט "RAW" פירושה שהערכים כתובים בדיוק כפי שהם, ולא מחושבים.

בעת ביצוע בדיקת JUnit זו, היישום יפתח חלון דפדפן באמצעות דפדפן ברירת המחדל של המערכת המבקש מהמשתמש להיכנס ולתת לאפליקציה שלנו הרשאה לתקשר עם Google Sheets בשם המשתמש:

שים לב שניתן לעקוף שלב ידני זה אם יש לך חשבון שירות OAuth.

דרישה שהאפליקציה תוכל להציג או לערוך את הגיליון האלקטרוני היא שלמשתמש המחובר יש תצוגה או הרשאת עריכה אליו. אחרת, הבקשה תגרום לשגיאה 403. הגיליון האלקטרוני בו אנו משתמשים לדוגמא מוגדר כגישת עריכה ציבורית.

עכשיו, אם נבדוק את הגיליון האלקטרוני, נראה את הטווח "A1: B6”מתעדכן בערכות הערך שלנו.

נעבור לכתיבה לטווחים שונים זה מזה בבקשה אחת.

5.2. כתיבה לטווחים מרובים

אם אנו רוצים לעדכן מספר טווחים על גיליון, נוכל להשתמש ב- BatchUpdateValuesRequest לביצועים טובים יותר:

נתוני רשימה = ArrayList חדש (); data.add (ValueRange חדש () .setRange ("D1") .setValues ​​(Arrays.asList (Arrays.asList ("סך הכל בינואר", "= B2 + B3")))); data.add (ValueRange חדש () .setRange ("D4") .setValues ​​(Arrays.asList (Arrays.asList ("סך הכל בפברואר", "= B5 + B6")))); BatchUpdateValuesRequest batchBody = BatchUpdateValuesRequest (חדש.) SetValueInputOption ("USER_ENTERED") .setData (נתונים); BatchUpdateValuesResponse batchResult = sheetsService.spreadsheets (). ערכים () .batchUpdate (SPREADSHEET_ID, batchBody) .execute ();

בדוגמה זו, אנו בונים לראשונה רשימה של ValueRanges, כל אחד מהם מורכב משני תאים המייצגים את שם החודש ואת סך ההוצאות.

ואז אנו יוצרים BatchUpdateValuesRequest עם אפשרות הקלט "USER_ENTERED", בניגוד ל "גלם", כלומר ערכי התא יחושבו על פי הנוסחה של הוספת שני תאים אחרים.

לבסוף, אנו יוצרים ושולחים את ה- batchUpdate בַּקָשָׁה. כתוצאה מכך, הטווחים "D1: E1"ו"D4: E4" יעודכן.

5.3. הוספת נתונים לאחר טבלה

דרך נוספת לכתוב ערכים בגיליון היא על ידי הוספתם בסוף הטבלה.

לשם כך, אנו יכולים להשתמש ב- לְצַרֵף() שיטה:

ValueRange appendBody = ValueRange חדש () .setValues ​​(Arrays.asList (Arrays.asList ("סה"כ", "= E1 + E4"))); AppendValuesResponse appendResult =heetsService.spreadsheets (). ערכים () .append (SPREADSHEET_ID, "A1", appendBody) .setValueInputOption ("USER_ENTERED") .setInsertDataOption ("INSERT_ROWS") .setIncludeValues ValueRange total = appendResult.getUpdates (). GetUpdatedData (); assertThat (total.getValues ​​(). get (0). get (1)). isEqualTo ("65");

ראשית, אנו בונים את ValueRange אובייקט המכיל את ערכי התא שאנו רוצים להוסיף.

במקרה שלנו, זה מכיל תא עם ההוצאות הכוללות עבור שני החודשים שאנו מוצאים על ידי הוספת ה- "E1" ו "E2" ערכי תאים.

לאחר מכן אנו יוצרים בקשה שתצרף את הנתונים אחרי הטבלה המכילה את "A1תא.

ה INSERT_ROWS אפשרות פירושה שאנחנו רוצים שהנתונים יתווספו לשורה חדשה, ולא יחליפו נתונים קיימים אחרי הטבלה. פירוש הדבר שהדוגמה תכתוב את הטווח "A7: B7”בריצה הראשונה שלה.

בריצות הבאות, הטבלה שמתחילה ב "A1" התא יתמתח כעת כדי לכלול את "A7: B7" שורה, כך ששורה חדשה עוברת אל "A8: B8" שורה, וכן הלאה.

אנחנו גם צריכים להגדיר את includeValuesInResponse נכס לנכון אם אנו רוצים לאמת את התגובה לבקשה. כתוצאה מכך, אובייקט התגובה יכיל את הנתונים המעודכנים.

6. קריאת ערכים מגיליון

בואו נוודא שהערכים שלנו נכתבו נכון על ידי קריאתם מהגיליון.

אנו יכולים לעשות זאת באמצעות ה- גיליונות אלקטרוניים (). ערכים (). קבל () שיטה לקרוא טווח יחיד או את batchUpdate () שיטה לקריאת טווחים מרובים:

טווחי רשימה = Arrays.asList ("E1", "E4"); BatchGetValuesResponse readResult = sheetsService.spreadsheets (). ערכים () .batchGet (SPREADSHEET_ID). SetRanges (טווחים). Execute (); ValueRange januaryTotal = readResult.getValueRanges (). Get (0); assertThat (januaryTotal.getValues ​​(). get (0) .get (0)) .isEqualTo ("40"); ValueRange febTotal = readResult.getValueRanges (). Get (1); assertThat (febTotal.getValues ​​(). get (0) .get (0)) .isEqualTo ("25");

הנה, אנו קוראים את הטווחים "E1" ו "דואר 4" ואימות שהם מכילים את הסכום עבור כל חודש שכתבנו קודם.

7. יצירת גיליונות אלקטרוניים חדשים

מלבד קריאה ועדכון ערכים, אנו יכולים גם לתפעל גליונות או גיליונות אלקטרוניים שלמים באמצעות גיליונות אלקטרוניים () ו גיליונות אלקטרוניים (). גיליונות () אוספים.

בואו נראה דוגמה ליצירת גיליון אלקטרוני חדש:

@ מבחן הריק הציבורי של @ () זורק IOException {גיליון אלקטרוני spreadSheet = גיליון אלקטרוני חדש (). SetProperties (SpreadsheetProperties חדש (). SetTitle ("הגיליון האלקטרוני שלי")); תוצאה של גיליון אלקטרוני = גליונות שירות. חלונות אלקטרוניים (). יצירת (גיליון אלקטרוני). ביצוע (); assertThat (result.getSpreadsheetId ()). isNotNull (); }

הנה, אנו יוצרים לראשונה גיליון אלקטרוני חפץ עם הכותרת “שֶׁלִיגיליון אלקטרוני" ואז לבנות ולשלוח בקשה באמצעות לִיצוֹר() ו לבצע() שיטות.

הגיליון האלקטרוני החדש יהיה פרטי ויוצב ב- Drive של המשתמש המחובר.

8. פעולות עדכון אחרות

מרבית הפעולות האחרות לובשות צורה של א בַּקָשָׁה אובייקט, אותו אנו מוסיפים לרשימה ומשמש לבניית a BatchUpdateSpreadsheetRequest.

בואו נראה כיצד נוכל לשלוח שתי בקשות לשינוי כותרת של גיליון אלקטרוני ולהעתקת קבוצה של תאים מגיליון אחד לאחר:

@Test הציבור בטל כאשרUpdateSpreadSheetTitle_thenOk () זורק IOException {UpdateSpreadsheetPropertiesRequest updateSpreadSheetRequest = UpdateSpreadsheetPropertiesRequest חדש) (set. Fields ("*"). SetProperties (SpreadsheetProperties חדש). CopyPasteRequest copyRequest = CopyPasteRequest חדש () .setSource (GridRange חדש (). SetSheetId (0) .setStartColumnIndex (0) .setEndColumnIndex (2). SetStartRowIndex (0). SetEndRowIndex (1)). 1) .setStartColumnIndex (0) .setEndColumnIndex (2) .setStartRowIndex (0) .setEndRowIndex (1)). SetPasteType ("PASTE_VALUES"); בקשות רשימה = ArrayList חדש (); requests.add (בקשה חדשה () .setCopyPaste (copyRequest)); requests.add (בקשה חדשה () .setUpdateSpreadsheetProperties (updateSpreadSheetRequest)); גוף BatchUpdateSpreadsheetRequest = חדש BatchUpdateSpreadsheetRequest (). SetRequests (בקשות); sheetsService.spreadsheets (). batchUpdate (SPREADSHEET_ID, body) .execute (); }

הנה, אנו יוצרים UpdateSpreadSheetPropertiesRequest אובייקט המציין את הכותרת החדשה, א CopyPasteRequest אובייקט המכיל את מקור היעד ואת הפעולה ואז הוספת אובייקטים אלה ל- a רשימה שֶׁל בקשות.

לאחר מכן אנו מבצעים את שתי הבקשות כעדכון אצווה.

סוגים רבים אחרים של בקשות זמינים לשימוש באופן דומה. לדוגמא, אנו יכולים ליצור גיליון חדש בגיליון אלקטרוני עם AddSheetRequest או לשנות ערכים עם FindRlaceRequest.

אנו יכולים לבצע פעולות אחרות כגון שינוי גבולות, הוספת פילטרים או מיזוג תאים. הרשימה המלאה של בַּקָשָׁה סוגים זמינים כאן.

9. מסקנה

במאמר זה ראינו כיצד אנו יכולים להתחבר ל- Google Sheets API מיישום Java וכמה דוגמאות לתמרון מסמכים המאוחסנים ב- Google Sheets.

קוד המקור המלא של הדוגמאות ניתן למצוא באתר GitHub.


$config[zx-auto] not found$config[zx-overlay] not found