Google Sheets API: शुरुआती लोगों के लिए चरण-दर-चरण मार्गदर्शिका

Google Sheets अक्सर वह जगह है जहाँ टीमें डेटा संग्रहीत और साझा करती हैं। लेकिन जब आप बार-बार उसी शीट को अपडेट कर रहे होते हैं, तो मैन्युअल कॉपी-पेस्ट समय बर्बाद करता है।

Google Sheets API आपको अपनी ऐप्स और स्क्रिप्ट्स से शीट्स को स्वतः अपडेट और प्रबंधित करने देता है। आप डेटा खींच सकते हैं, अपडेट भेज सकते हैं, और बिना हाथ से किए रिपोर्ट्स को ताज़ा रख सकते हैं।

इस मार्गदर्शिका में, हम आपको चरण-दर-चरण बताएँगे कि सेटअप कैसे करें, पढ़ें, लिखें, फॉर्मैट करें, और आम स्प्रेडशीट कार्यों को स्वचालित करें।

Google Sheets API से शुरुआत करना

Google Sheets API उपयोग करना शुरू करने के लिए, आपको एक Google Cloud प्रोजेक्ट और अपनी ऐप को प्रमाणित करने का तरीका चाहिए। आइए उच्च-स्तरीय तौर पर इसे सेट करने का तरीका देखें। 

Google Cloud प्रोजेक्ट बनाना

सभी Google APIs एक Google Cloud प्रोजेक्ट के भीतर चलते हैं। यह प्रोजेक्ट आपकी क्रेडेंशियल्स, API सेटिंग्स, और उपयोग सीमाएँ रखता है।

अपना पहला प्रोजेक्ट ऐसे बनाएँ: 

• जाएँ the Google Cloud Console पर।
• ऊपर Select a project पर क्लिक करें।
• एक नया पॉप-अप Select a project दिखाई देगा। यहाँ से New Project पर क्लिक करें।
• अगली विंडो में, प्रोजेक्ट का नाम दर्ज करें।
• क्लिक करें Create।

Google Console में नया प्रोजेक्ट बनाएँ। छवि: लेखक द्वारा।

प्रोजेक्ट तैयार होने के बाद, आवश्यक APIs सक्षम करें: 

• अपना प्रोजेक्ट खोलें।
• ऊपर बाएँ तीन लाइनों पर क्लिक करें। 
• जाएँ APIs & Services > Enabled APIs & services.
• क्लिक करें + Enable APIs & services.

API सक्षम करने के लिए Enable APIs & Services पर जाएँ। छवि: लेखक द्वारा।

• अब API Library विंडो दिखाई देगी। सर्च बार में Google Sheets API खोजें और उस पर क्लिक करें।
• क्लिक करें Enable।

Google Sheets API सक्षम करें। छवि: लेखक द्वारा।

यदि आपकी एप्लिकेशन को स्प्रेडशीट फाइलें बनानी, स्थानांतरित करनी, या प्रबंधित करनी हैं, तो Google Drive API भी सक्षम करें: 

• खोजें Google Drive API।
• क्लिक करें Enable।

प्रमाणीकरण और प्राधिकरण

Google Sheets API तीन मुख्य प्रमाणीकरण विधियों का समर्थन करता है। कौन-सी विधि उपयोग करनी है, यह आपकी ऐप के काम करने के तरीके पर निर्भर करता है।

OAuth 2.0

इसे तब उपयोग करें जब वास्तविक उपयोगकर्ता साइन-इन करते हैं और अपनी ही स्प्रेडशीट्स के साथ काम करते हैं। इससे उपयोगकर्ता एक्सेस की अनुमति दे सकते हैं, और आपकी ऐप केवल वही कर सकती है जिसकी वे अनुमति देते हैं।

मान लीजिए आप एक वेब ऐप बना रहे हैं जो उपयोगकर्ताओं को अपने Google खाते कनेक्ट करने देता है ताकि आपकी ऐप उनकी व्यक्तिगत शीट्स को पढ़ या अपडेट कर सके। इस स्थिति में, OAuth 2.0 अपनाएँ। 

यह ऐसे करें:

• Google Console में जाएँ Navigation Menu (ऊपर बाएँ तीन लाइनें) > API & Services > OAuth consent screen

OAuth consent screen पर जाएँ। छवि: लेखक द्वारा।

• अब विवरण भरें: 
• उस ऐप का नाम दर्ज करें जिसे सहमति की आवश्यकता है, App name फ़ील्ड में
• सपोर्ट ईमेल दर्ज करें ताकि उपयोगकर्ता वहाँ प्रश्न पूछ सकें और Next पर क्लिक करें
• अब External audience प्रकार चुनें और Next पर क्लिक करें
• अगला, संपर्क जानकारी (ईमेल पता) भरें ताकि Google आपके प्रोजेक्ट में बदलावों की सूचना दे सके। फिर, Next पर क्लिक करें
• नीतियों से सहमत हों, बॉक्स को चेक करें और Continue पर क्लिक करें

• क्लिक करें Save. एक संदेश दिखाई देगा: OAuth configuration created!

अब, अंतिम-उपयोगकर्ताओं को प्रमाणित करने और उनके Google Sheets डेटा तक पहुँचने के लिए, OAuth 2.0 क्लाइंट क्रेडेंशियल्स बनाएँ। एक क्लाइंट ID आपके एप्लिकेशन की Google के OAuth सर्वरों पर विशिष्ट पहचान करता है।

यह ऐसे करें: 

• क्लिक करें Navigation Menu > APIs & Services > Credentials।
• Credentials विंडो में, + Create credentials पर क्लिक करें और OAuth client ID चुनें।

OAuth client ID पर जाएँ। छवि: लेखक द्वारा।

• अब Web Application को Application type ड्रॉपडाउन से चुनें।
• कंसोल में पहचान के लिए OAuth 2.0 क्लाइंट का नाम दर्ज करें।
• Under Authorized redirect URIs, क्लिक करें Add URI
• वह redirect URL दर्ज करें जहाँ Google प्राधिकरण प्रतिक्रिया भेजेगा। यह आपकी एप्लिकेशन में कॉन्फ़िगर किए गए redirect URI से बिल्कुल मेल खाना चाहिए (उदा., आपका OAuth callback endpoint)
• क्लिक करें Create
• कॉपी करें और सुरक्षित रखें Client ID और Client Secret. 

API कुंजियाँ

API कुंजियाँ आपके प्रोजेक्ट की पहचान करती हैं, किसी उपयोगकर्ता की नहीं। ये केवल सार्वजनिक स्प्रेडशीट्स के साथ काम करती हैं और निजी फाइलें संपादित नहीं कर सकतीं।

इसीलिए आपको इसे तभी उपयोग करना चाहिए जब आप किसी सार्वजनिक Google Sheet से डेटा पढ़कर वेबसाइट पर दिखाना चाहते हों या एक साधारण स्क्रिप्ट का परीक्षण करना चाहते हों।

अपनी API कुंजी ऐसे जनरेट करें: 

• जाएँ Navigation Menu > APIs & Services > Credentials।

API Keys पर जाएँ। छवि: लेखक द्वारा।

• क्लिक करें + Create credentials, फिर चुनें API key.
• नीचे स्क्रॉल करें, Restrict key बॉक्स चुनें, और फिर ड्रॉपडाउन से वे APIs चुनें जिनके साथ यह API कुंजी उपयोग की जा सकती है। 
• क्लिक करें Create।
• जनरेट की गई कुंजी कॉपी करें और सुरक्षित रखें।

जनरेट की गई API कुंजी सुरक्षित रखें। छवि: लेखक द्वारा।

सर्विस अकाउंट्स

सर्विस अकाउंट्स आपकी ऐप को बिना उपयोगकर्ता साइन-इन के अपने-आप चलने देते हैं। आप विशिष्ट स्प्रेडशीट्स को सर्विस अकाउंट ईमेल के साथ साझा करते हैं और नियंत्रित करते हैं कि वह क्या कर सकता है।

यदि आप एक शेड्यूल्ड स्क्रिप्ट चलाना चाहते हैं जो हर रात रिपोर्ट अपडेट करती है या डेटाबेस से डेटा एक साझा Google Sheet में सिंक करती है, तो सर्विस अकाउंट्स सबसे अच्छा विकल्प हैं। 

आइए देखें इन्हें कैसे बनाएँ और कॉन्फ़िगर करें: 

सर्विस अकाउंट्स बनाना और कॉन्फ़िगर करना

यह ऐसे करें: 

• उस Google Sheets API पर क्लिक करें जिसे हमने पहले सक्षम किया था। 
• ऊपर बाएँ, Create credentials बटन पर क्लिक करें।

Google Sheets API में क्रेडेंशियल्स बनाएँ। छवि: लेखक द्वारा।

• Create Credentials विंडो में, Google Sheets API को Select an API फ़ील्ड में चुना होना चाहिए। अब वह डेटा प्रकार चुनें जिसे आप एक्सेस करना चाहते हैं। चूँकि आप सर्विस अकाउंट का उपयोग कर रहे हैं, Application data चुनें और Next पर क्लिक करें।

क्रेडेंशियल्स दर्ज करें। छवि: लेखक द्वारा।

• अब एक नई विंडो Create service account दिखाई देगी:
• अपना Service account name, Service account ID, और Service account description जोड़ें। 
• क्लिक करें Create and continue।

एक पॉप-अप दिखाई देगा: Service account created।

Google Sheets API में सर्विस अकाउंट बनाएँ। छवि: लेखक द्वारा।

सर्विस अकाउंट लॉगिन या कंसेंट स्क्रीन का उपयोग नहीं करता। इसके बजाय, यह JSON कुंजी फ़ाइल में संग्रहीत निजी कुंजी का उपयोग कर प्रमाणित होता है। यह कुंजी फ़ाइल एप्लिकेशन सर्वर को Google के सामने अपनी पहचान साबित करने और स्वतः एक्सेस टोकन का अनुरोध करने देती है।

अब, कुंजी फ़ाइल जनरेट करने के लिए: 

• सर्विस अकाउंट खोलें। (आप इसे IAM & Admin > Service Account के तहत भी पा सकते हैं) 
• जाएँ Keys टैब पर।
• क्लिक करें Add key > Create new key।
• चुनें JSON और क्लिक करें Create।

अब, फ़ाइल डाउनलोड होना शुरू हो जाएगी। 

Google Sheets API के लिए JSON कुंजी फ़ाइल जनरेट करें। छवि: लेखक द्वारा।

सर्विस अकाउंट्स के साथ Google Sheets साझा करना

सर्विस अकाउंट्स को स्वतः आपकी स्प्रेडशीट्स तक पहुँच नहीं होती। आपको प्रत्येक फाइल सर्विस अकाउंट ईमेल के साथ साझा करनी होगी।

ऐसा करने के लिए: 

• डाउनलोड की हुई फाइल खोलें और client_email खोजें। या Service Account मेनू के तहत बनाए गए सर्विस अकाउंट पर क्लिक करें, फिर Email देखें। दोनों ईमेल एक जैसे होते हैं। 
• अब, उस ईमेल को कॉपी करें। 

JSON कुंजी फ़ाइल से क्लाइंट ईमेल कॉपी करें। छवि: लेखक द्वारा।

• Google Sheet खोलें।
• ऊपर दाएँ कोने में Share विकल्प पर जाएँ। 
• शेयर फ़ील्ड में वह ईमेल पेस्ट करें जिसे हमने पहले कॉपी किया था।
• चुनें Viewer (डेटा पढ़ने और लिखने की अनुमति देता है) या Editor (केवल पढ़ने की अनुमति देता है) एक्सेस। 
• क्लिक करें Send ताकि एक्सेस साझा हो जाए। 

Google Sheets के माध्यम से सर्विस अकाउंट एक्सेस साझा करें। छवि: लेखक द्वारा।

क्लाइंट लाइब्रेरीज़ इंस्टॉल करना

Google आधिकारिक क्लाइंट लाइब्रेरीज़ प्रदान करता है जो Sheets API के साथ काम करना आसान बनाती हैं। ये आपके लिए प्रमाणीकरण, अनुरोध फॉर्मैटिंग, और पुनः प्रयास संभालती हैं। वास्तव में, कच्चे HTTP अनुरोध भेजने की तुलना में यह आसान और सुरक्षित है।

यहाँ आम लोकप्रिय भाषाओं के लिए इंस्टॉलेशन उदाहरण हैं:

Python

pip install google-api-python-client google-auth google-auth-oauthlibसंचालित द्वारा

Node.js

npm install googleapisसंचालित द्वारा

Java (Maven के माध्यम से क्लाइंट लाइब्रेरी जोड़ें)

<dependency>
  <groupId>com.google.apis</groupId>
  <artifactId>google-api-services-sheets</artifactId>
</dependency>संचालित द्वारा

ध्यान दें: क्लाइंट लाइब्रेरीज़ सेटअप समय बचाती हैं और जैसे-जैसे आपका प्रोजेक्ट बढ़ता है, त्रुटियाँ घटाती हैं।

मुख्य डेटा क्रियाएँ

Google Sheets API का मुख्य उपयोग स्प्रेडशीट डेटा को पढ़ना, लिखना, अपडेट करना और फॉर्मैट करना है। आइए इन्हें समझें: 

स्प्रेडशीट्स से डेटा पढ़ना

Google Sheet से डेटा पढ़ने के लिए आपको दो चीज़ें चाहिए: स्प्रेडशीट ID और वह सेल रेंज जिसे आप एक्सेस करना चाहते हैं।

स्प्रेडशीट ID, Google Sheets URL से आती है। यह लंबी स्ट्रिंग /d/ और /edit के बीच होती है। यह API को बताती है कि किस स्प्रेडशीट से पढ़ना है।

उदाहरण के लिए, यदि मेरी Sheets URL है: https://docs.google.com/spreadsheets/d/1s4-xSl1ztvdXmCUISe14XZWduQoJ4Ignz_HrgqsExPI/edit?gid=0#gid=0  

इस स्थिति में, स्प्रेडशीट ID है: 1s4-xSl1ztvdXmCUISe14XZWduQoJ4Ignz_HrgqsExPI

स्प्रेडशीट ID। छवि: लेखक द्वारा।

रेंज A1 नोटेशन में लिखी जाती है, जो कॉलम अक्षरों और पंक्ति संख्याओं से सेल्स का वर्णन करती है। उदाहरण:

• A1 एक सिंगल सेल पढ़ता है

• A1:C10 सेल्स के एक ब्लॉक को पढ़ता है

• Sheet1!A:A पूरे कॉलम को पढ़ता है

• Sheet1!1:1 पूरी पंक्ति को पढ़ता है

मान प्राप्त करने के लिए, API दो मुख्य विधियाँ प्रदान करता है:

• spreadsheets.values.get एक सिंगल रेंज से डेटा पढ़ता है। इसका उपयोग तब करें जब आपको ठीक-ठीक पता हो कि कौन-सी तालिका या ब्लॉक चाहिए। 

• spreadsheets.values.batchGet एक ही अनुरोध में कई रेंज से डेटा पढ़ता है। इसका उपयोग तब करें जब आपको एक साथ कई जगहों से डेटा चाहिए, जैसे एक शीट से कुल योग और दूसरी से विवरण।

डेटा पढ़ने से स्प्रेडशीट नहीं बदलती। यह केवल आपकी ऐप के उपयोग के लिए मान लौटाती है। इसलिए, आप इसे डैशबोर्ड में डेटा खींचने, विश्लेषण के लिए डेटा निर्यात करने, और स्वचालित रिपोर्ट बनाने के लिए उपयोग कर सकते हैं।

स्प्रेडशीट्स में डेटा लिखना

डेटा लिखना, पढ़ने के समान ही काम करता है। आपको अभी भी स्प्रेडशीट ID और रेंज चाहिए, पर इस बार आप वे मान भी भेजते हैं जिन्हें लिखना है।

Google Sheets API इसके लिए दो मुख्य विधियाँ देता है:

• spreadsheets.values.update: इसका उपयोग तब करें जब आप किसी विशिष्ट रेंज में डेटा लिखना चाहते हैं और वहाँ मौजूद सामग्री को ओवरराइट करना चाहते हैं। उदाहरण के लिए, यदि आप तालिका की हेडर पंक्ति को नए कॉलम नामों से बदलना चाहते हैं, तो update सही विकल्प है। आपके द्वारा भेजे गए मान उस रेंज के मौजूदा सेल्स को बदल देंगे।

• spreadsheets.values.append: इसका उपयोग तब करें जब आप मौजूदा डेटा को प्रभावित किए बिना नई पंक्तियाँ जोड़ना चाहते हैं। यह तब उपयोगी है जब आपका डेटा लगातार बढ़ता रहता है, जैसे दैनिक मेट्रिक्स लॉग करना, नए रिकॉर्ड जोड़ना, या फ़ॉर्म प्रतिक्रियाएँ संग्रहीत करना। API स्वतः अंतिम गैर-खाली पंक्ति के बाद नए मान जोड़ देता है।

Value input विकल्प

डेटा लिखते समय, आपको यह भी चुनना होता है कि Google Sheets आपके द्वारा भेजे गए मानों के साथ कैसा व्यवहार करे। यह ValueInputOption द्वारा नियंत्रित होता है।

• RAW मान को वैसा ही स्टोर करता है जैसा आप भेजते हैं

• USER_ENTERED मान के साथ वैसा व्यवहार करता है जैसे उसे शीट में टाइप किया गया हो

उदाहरण के लिए, यदि आप =SUM(1,2) भेजते हैं:

• RAW के साथ, यह सादा टेक्स्ट के रूप में स्टोर होता है

• USER_ENTERED के साथ, यह एक फ़ॉर्मूला के रूप में मूल्यांकित होता है

ओवरराइट बनाम अपेंड डेटा

update और append के बीच चुनाव आपकी शीट की संरचना पर निर्भर करता है।

update का उपयोग करें जब:

• डेटा का स्थान निश्चित हो
• आप हेडर्स या ज्ञात सेल्स को बदल रहे हों

append का उपयोग करें जब:

• डेटा समय के साथ बढ़ता हो
• आप मौजूदा पंक्तियों को ओवरराइट करने से बचना चाहते हों

बैच ऑपरेशंस

बैच ऑपरेशंस आपको कई बदलाव एक ही API अनुरोध में समूहित करने देते हैं। इससे आपकी ऐप द्वारा किए गए कॉल्स की संख्या कम होती है और आप उपयोग सीमाओं के भीतर रहते हैं।

Google Sheets API में दो सामान्य बैचिंग पैटर्न हैं।

मानों के साथ काम करने के लिए, आप उपयोग कर सकते हैं:

• spreadsheets.values.batchGet कई रेंज एक साथ पढ़ने के लिए

• spreadsheets.values.batchUpdate एक अनुरोध में कई रेंज में लिखने के लिए

संरचनात्मक और फॉर्मैटिंग बदलावों के लिए, spreadsheets.batchUpdate का उपयोग करें। यह सबसे उपयोगी बैचिंग विधि है और एक ही अनुरोध में कई कार्रवाइयों का समर्थन करती है।

spreadsheets.batchUpdate के साथ, आप बदलावों को संयोजित कर सकते हैं जैसे:

• शीट्स बनाना या हटाना
• स्प्रेडशीट या टैब का नाम बदलना
• हेडर पंक्तियों को फ्रीज़ करना
• कॉलम चौड़ाई बदलना
• फॉर्मैटिंग लागू करना
• एक साथ कई रेंज अपडेट करना

उदाहरण के लिए, स्प्रेडशीट का नाम बदलने और नया टैब बनाने के लिए अलग-अलग अनुरोध भेजने के बजाय, आप दोनों को एक batchUpdate कॉल में कर सकते हैं। यह तेज़ है और जैसे-जैसे आपके वर्कफ़्लो बढ़ते हैं, प्रबंधन करना आसान होता है।

आप बैच ऑपरेशंस का उपयोग रिपोर्ट सेटअप करने, सुसंगत फॉर्मैटिंग लागू करने, या स्प्रेडशीट में बड़े पैमाने पर अपडेट करने के लिए कर सकते हैं।

डेटा अपडेट करना और हटाना

डेटा अपडेट करने का मतलब विशिष्ट सेल्स या रेंज में मानों को बदलना है। यह आमतौर पर तब किया जाता है जब आप संख्याएँ ताज़ा करते हैं, KPIs अपडेट करते हैं, या शीट में किसी नियत आउटपुट क्षेत्र को ओवरराइट करते हैं।

मौजूदा मानों को अपडेट करने के लिए, spreadsheets.values.update का उपयोग करें। यहाँ, आप लक्ष्य रेंज निर्दिष्ट करते हैं और नए मान भेजते हैं, और उस रेंज में मौजूदा डेटा बदल जाता है।

क्लियर करना बनाम डिलीट करना

डेटा क्लियर करना और डेटा डिलीट करना एक जैसा नहीं है:

क्लियर करना सेल्स के अंदर के मानों को हटाता है लेकिन शीट, लेआउट, और फॉर्मैटिंग बरकरार रखता है। spreadsheets.values.clear का उपयोग तब करें जब आप संरचना को प्रभावित किए बिना किसी रेंज को खाली करना चाहते हों।

उदाहरण: उसी सेल्स में नया डेटा लिखने से पहले किसी पुराने रिपोर्ट सेक्शन को क्लियर करना।

डिलीट करना पूरे शीट टैब को हटाता है। यह spreadsheets.batchUpdate में deleteSheet अनुरोध के साथ किया जाता है। शीट को डिलीट करना उसके डेटा और संरचना को स्थायी रूप से हटा देता है।

क्योंकि शीट डिलीट करना अपरिवर्तनीय है, इसे कम ही उपयोग करें और तभी जब आप आश्वस्त हों कि डेटा अब आवश्यक नहीं है।

उन्नत फीचर्स और कार्यक्षमता

डेटा पढ़ने-लिखने से आगे, Google Sheets API आपको नियंत्रित करने देता है कि स्प्रेडशीट्स कैसी दिखें और कैसे व्यवहार करें। इससे आप बड़े पैमाने पर फॉर्मैटिंग और विज़ुअल नियमों को स्वचालित कर सकते हैं।

फॉर्मैटिंग और कंडीशनल फॉर्मैटिंग

Google Sheets API आपको Sheets UI की बजाय प्रोग्रामेटिक तरीके से सेल फॉर्मैटिंग लागू करने देता है। इसमें टेक्स्ट स्टाइल्स, रंग, नंबर फॉर्मैट्स, और लेआउट परिवर्तन शामिल हैं।

सभी फॉर्मैटिंग बदलाव spreadsheets.batchUpdate का उपयोग करके JSON अनुरोधों के रूप में भेजे जाते हैं। प्रत्येक अनुरोध बताता है कि क्या बदलना है और कहाँ लागू करना है।

सामान्य फॉर्मैटिंग कार्यों में शामिल हैं:

• हेडर पंक्तियों को बोल्ड करना
• मुद्रा या प्रतिशत के लिए नंबर फॉर्मैट लागू करना
• कॉलम चौड़ाई समायोजित करना
• विशिष्ट रेंज के लिए बैकग्राउंड रंग सेट करना

उदाहरण के लिए, एक फॉर्मैटिंग अनुरोध शीट की पहली पंक्ति को लक्षित कर उसे हल्की पृष्ठभूमि के साथ बोल्ड कर सकता है। अनुरोध जीरो-आधारित इंडेक्स का उपयोग करता है, जहाँ पंक्ति 0 पहली पंक्ति का प्रतिनिधित्व करती है।

कंडीशनल फॉर्मैटिंग

कंडीशनल फॉर्मैटिंग आपको मानों के आधार पर सेल्स को स्वतः स्टाइल करने देती है। यदि आप चाहते हैं कि Sheets बिना मैन्युअल समीक्षा के महत्वपूर्ण डेटा को हाइलाइट करे, तो कंडीशनल फॉर्मैटिंग मदद कर सकती है। 

API का उपयोग करते हुए, कंडीशनल फॉर्मैटिंग नियम JSON में परिभाषित किए जाते हैं और spreadsheets.batchUpdate के माध्यम से लागू होते हैं।

आप इसका उपयोग कर सकते हैं:

• नकारात्मक मानों को लाल रंग में हाइलाइट करना
• किसी थ्रेशहोल्ड से ऊपर या नीचे के मानों को फ़्लैग करना
• ऐसी पंक्तियों को स्वतः स्टाइल करना जो कुछ शर्तें पूरी करती हों

उदाहरण के लिए, आप एक नियम परिभाषित कर सकते हैं जो किसी विशिष्ट कॉलम में 0 से कम के किसी भी मान को हाइलाइट करे। एक बार लागू होने पर, डेटा बदलते ही फॉर्मैटिंग स्वतः अपडेट हो जाती है।

पिवट टेबल्स और डेटा एग्रीगेशन

Google Sheets API, कोड के माध्यम से पिवट टेबल्स बना और अपडेट कर सकता है। पिवट टेबल्स बड़े डाटासेट्स का सारांश निकालती हैं बिना मूल डेटा को बदले, जो रिपोर्टिंग और विश्लेषण में सहायक है।

आपको केवल JSON अनुरोधों के माध्यम से पिवट टेबल्स परिभाषित करनी हैं और spreadsheets.batchUpdate के साथ लागू करना है। ये अनुरोध बताते हैं कि डेटा को कैसे ग्रुप, सारांशित और प्रदर्शित किया जाए।

API के माध्यम से, आप:

• कौन-से कॉलम्स रो लेबल बनेंगे, चुन सकते हैं
• sum, count, या average जैसे एग्रीगेशन तरीकों का चयन कर सकते हैं
• पिवट टेबल कहाँ दिखाई देगी, नियंत्रित कर सकते हैं
• जैसे-जैसे नया डेटा जुड़ता है, पिवट टेबल्स को रीफ़्रेश या संशोधित कर सकते हैं

उदाहरण के लिए, आप पंक्तियों को कीवर्ड के अनुसार ग्रुप कर सकते हैं और स्वतः कुल क्लिक्स और इम्प्रेशंस की गणना कर सकते हैं। आधारभूत डेटा बदलते ही पिवट टेबल स्वतः अपडेट होती है।

Connected Sheets और BigQuery इंटीग्रेशन

Connected Sheets Google Sheets को सीधे BigQuery के साथ काम करने देता है। BigQuery बड़े डाटासेट्स सँभालता है, जबकि Sheets विश्लेषण और रिपोर्टिंग के लिए उपयोग होता है। 

लाखों पंक्तियाँ शीट में कॉपी करने के बजाय, Connected Sheets BigQuery पर क्वेरी चलाता है और परिणाम दिखाता है। इससे बहुत बड़े डाटासेट्स पर भी स्प्रेडशीट्स तेज़ और प्रतिक्रिया-सक्षम रहती हैं।

कनेक्शन Google Sheets के अंदर से बनाया जाता है। कनेक्शन सेट करने के लिए Google Sheets API का उपयोग नहीं होता।

तो, यह ऐसे काम करता है:

• डेटा BigQuery टेबल में रहता है
• आप एक Google Sheet को उस टेबल से कनेक्ट करते हैं
• Sheets क्वेरी चलाता है और परिणाम दिखाता है
• बिना फाइल एक्सपोर्ट किए डेटा को रीफ़्रेश किया जा सकता है

कनेक्ट होने के बाद, आप सीधे Sheets में पिवट टेबल्स बना सकते हैं, फ़ॉर्मूले लागू कर सकते हैं, और रिपोर्ट्स बना सकते हैं।

प्रमाणीकरण और अनुमतियाँ

Connected Sheets तक पहुँच Google Cloud Identity and Access Management के माध्यम से नियंत्रित होती है।

जो खाता Sheets को BigQuery से कनेक्ट कर रहा है, उसके पास होना चाहिए:

• BigQuery Data Viewer अनुमति
• BigQuery Job User अनुमति
• Google Sheet तक पहुँच

प्रमाणीकरण स्वतः Google Sheets इंटरफ़ेस के माध्यम से संभाला जाता है।

हालाँकि, कुछ सीमाओं से अवगत रहें। जबकि Sheets API Connected Sheets से डेटा पढ़ सकता है और फॉर्मैटिंग लागू कर सकता है या पिवट टेबल्स बना सकता है, यह आधारभूत BigQuery क्वेरी को संशोधित नहीं कर सकता या BigQuery-समर्थित रेंजेज़ में वापस डेटा नहीं लिख सकता।

इसीलिए Connected Sheets, विश्लेषण और रिपोर्टिंग के लिए सबसे उपयुक्त है, कच्चा डेटा डालने या संपादित करने के लिए नहीं।

नो-कोड इंटीग्रेशन टूल्स का उपयोग

हर वर्कफ़्लो को कस्टम कोड की आवश्यकता नहीं होती। कई नो-कोड और लो-कोड टूल सीधे Google Sheets API से कनेक्ट होते हैं और आपके लिए तकनीकी विवरण संभालते हैं। तीन सबसे लोकप्रिय विकल्प हैं:

• Apipheny
• Supermetrics
• Funnel

ये टूल डेटा स्रोत चुनने, शेड्यूल सेट करने, और परिणाम Google Sheets को भेजने के लिए विजुअल इंटरफ़ेस प्रदान करते हैं। ये बैकग्राउंड में प्रमाणीकरण, API अनुरोध, और त्रुटि हैंडलिंग सँभालते हैं।

इससे नो-कोड टूल्स, मार्केटर्स और विश्लेषकों के लिए एक अच्छा विकल्प बनते हैं जिन्हें बिना कोड लिखे विश्वसनीय डेटा पाइपलाइन्स की आवश्यकता होती है। ये सामान्य रिपोर्टिंग और डैशबोर्ड उपयोग मामलों के लिए सेटअप समय भी कम करते हैं।

प्राइसिंग और API सीमाएँ

अधिकांश प्रोजेक्ट्स के लिए, आप Google Sheets API मुफ्त में उपयोग कर सकते हैं। मुख्य बात यह समझना है कि उपयोग सीमाएँ कैसे काम करती हैं।

API प्राइसिंग का अवलोकन

Google आपसे प्रति अनुरोध या प्रति स्प्रेडशीट कार्रवाई शुल्क नहीं लेता। इसके बजाय, उपयोग को कोटा द्वारा नियंत्रित किया जाता है। 

जब तक आपका प्रोजेक्ट उन सीमाओं के भीतर रहता है, आप API बिना किसी लागत के उपयोग कर सकते हैं। यदि आप उन्हें पार करते हैं, तो अनुरोध अस्थायी रूप से अवरुद्ध हो जाते हैं जब तक कि उपयोग कम न हो जाए या सीमाएँ बढ़ाई न जाएँ।

उपयोग सीमाएँ और कोटा

Google Sheets API सीमाएँ लगाता है ताकि प्रदर्शन स्थिरता बनी रहे और दुरुपयोग रोका जा सके। ये सीमाएँ आमतौर पर प्रति प्रोजेक्ट लागू होती हैं और कुछ मामलों में प्रति उपयोगकर्ता।

अधिकांश प्रोजेक्ट्स दो मुख्य प्रकार की सीमाओं से टकराते हैं:

1. प्रति मिनट अनुरोध: यह सीमा नियंत्रित करती है कि आपका प्रोजेक्ट कम समय में कितने API कॉल्स भेज सकता है। एक साथ बहुत अधिक छोटे अनुरोध अस्थायी त्रुटियों को ट्रिगर कर सकते हैं।

डिफ़ॉल्ट रूप से, Google Sheets API यह अनुमति देता है:

सीमाएँ ज्यादातर इस पर लागू होती हैं कि आप प्रति मिनट कितने अनुरोध भेजते हैं, न कि दिन भर में कुल कितने।

सीमाओं के भीतर रहने और समस्याओं से बचने के लिए:

• कई अपडेट्स को एक ही अनुरोध में बैच करें
• छोटे बदलावों के लिए बार-बार पढ़ने से बचें
• डेटा को फिर से लाने की बजाय कैश करें
• Google Cloud Console में उपयोग मॉनिटर करें और समय रहते समायोजन करें

प्रदर्शन अनुकूलन और कोटा प्रबंधन

जब आप Google Sheets को स्वचालित करते हैं, तो प्रदर्शन की समस्याएँ आमतौर पर बहुत तेज़ी से बहुत सारे अनुरोध भेजने से आती हैं। इसलिए, यह समझना महत्वपूर्ण है कि कोटा कैसे काम करता है। 

API कोटा और रेट लिमिट्स को समझना

Google एक निश्चित समय में आपका प्रोजेक्ट कितने अनुरोध भेज सकता है, इसे सीमित करने के लिए कोटा लागू करता है। कुछ सीमाएँ प्रति प्रोजेक्ट लागू होती हैं, और कुछ प्रति उपयोगकर्ता।

ये सीमाएँ सीधे स्वचालित वर्कफ़्लो को प्रभावित करती हैं। यदि आपकी स्क्रिप्ट बहुत अधिक अनुरोध बहुत तेज़ी से भेजती है, तो API त्रुटियाँ लौटाना शुरू कर देता है और वर्कफ़्लो रुक जाता है। यह अक्सर तब होता है जब स्क्रिप्ट्स:

• पंक्तियों को एक-एक कर अपडेट करती हैं
• बार-बार बड़े रेंज पढ़ती हैं
• बैचिंग के बिना बहुत अधिक बार चलती हैं

उपयोग की निगरानी करने से आप समस्याएँ जल्दी पकड़ लेते हैं। Google Cloud Console में, आप देख सकते हैं कि आपका प्रोजेक्ट सीमाओं के कितने करीब है और गतिविधि में अचानक स्पाइक्स पकड़ सकते हैं। ऐसे करें:

• अपना प्रोजेक्ट खोलें।
• APIs & Services > Enabled API Services पर जाएँ 
• Google Sheets API पर क्लिक करें।
• Quotas & System Limits खोलें।

यदि आपको अचानक स्पाइक्स दिखें, तो आमतौर पर यह अक्षम अनुरोध पैटर्न का संकेत है।

Quotas और system limits। छवि: लेखक द्वारा।

एक्सपोनेंशियल बैकऑफ लागू करना

अच्छी तरह लिखी गई स्क्रिप्ट्स भी अस्थायी API त्रुटियों का सामना कर सकती हैं। सबसे सामान्य त्रुटियों को पहचानना आसान है:

• 429 त्रुटियाँ मतलब थोड़े समय में बहुत अधिक अनुरोध भेजे गए
• 5xx त्रुटियाँ मतलब API में अस्थायी सर्वर समस्या थी

ये त्रुटियाँ अक्सर अपने-आप ठीक हो जाती हैं। तुरंत पुनः प्रयास करना समस्या को और बढ़ा सकता है।

एक्सपोनेंशियल बैकऑफ, अनुरोधों को सुरक्षित रूप से पुनः प्रयास करने का मानक तरीका है। तुरंत पुनः प्रयास करने के बजाय, आपकी स्क्रिप्ट दोबारा प्रयास करने से पहले इंतज़ार करती है। हर विफलता के बाद प्रतीक्षा समय बढ़ता है।

एक सरल बैकऑफ पैटर्न ऐसा दिखता है:

• एक अनुरोध भेजें
• यदि 429 या 5xx त्रुटि मिले, तो एक सेकंड प्रतीक्षा करें
• फिर से प्रयास करें
• यदि फिर विफल हो, तो दो सेकंड प्रतीक्षा करें, फिर चार, फिर आठ
• अधिकतम प्रतीक्षा समय या पुनः प्रयास संख्या के बाद रुकें

यह तरीका API पर दबाव कम करता है और सफल पुनः प्रयास की संभावना बढ़ाता है।

व्यवहार में एक्सपोनेंशियल बैकऑफ

यहाँ एक सरल Python उदाहरण है जो एक्सपोनेंशियल बैकऑफ का उपयोग कर अनुरोध को पुनः प्रयास करता है:

import time
from googleapiclient.errors import HttpError

def run_with_backoff(callable_request, max_retries=5):
    delay = 1

    for attempt in range(max_retries):
        try:
            return callable_request()
        except HttpError as e:
            status = getattr(e.resp, "status", None)

            if status in [429, 500, 502, 503, 504]:
                time.sleep(delay)
                delay = min(delay * 2, 32)
                continue

            raise

    raise RuntimeError("Request failed after retries.")संचालित द्वारा

आप फिर किसी भी API कॉल को इस हेल्पर के साथ लपेट सकते हैं:

result = run_with_backoff(
    lambda: service.spreadsheets().values().get(
        spreadsheetId=spreadsheet_id,
        range="Report!A1:E100"
    ).execute()
)संचालित द्वारा

यह आपकी स्क्रिप्ट को तुरंत विफल होने से बचाता है जब समस्या अस्थायी हो, और आपके वर्कफ़्लो को अधिक विश्वसनीय रूप से चलाने में मदद करता है।

अनुकूलन रणनीतियाँ

जब आप कम अनुरोध भेजते हैं और पेलोड छोटे रखते हैं तो प्रदर्शन बेहतर होता है। अधिकांश धीमेपन तब होते हैं जब स्क्रिप्ट्स एक ही कॉल में बहुत अधिक काम करती हैं।

धीमेपन से बचने के लिए सबसे प्रभावी तकनीकें ये हैं:

बैच अनुरोध

spreadsheets.values.batchGet, spreadsheets.values.batchUpdate, और spreadsheets.batchreadsheets.batchUpdate जैसे बैच तरीकों से आप कई कार्रवाइयों को एक ही अनुरोध में जोड़ सकते हैं। इससे सबसे बड़ा प्रदर्शन लाभ मिलता है।

कैशिंग

एक ही डेटा को बार-बार पढ़ना समय और कोटा दोनों बर्बाद करता है। स्प्रेडशीट मेटाडेटा या शीट IDs एक बार प्राप्त करें, फिर स्क्रिप्ट रन के दौरान पुन: उपयोग करें।

पेजिनेशन

कुछ API प्रतिक्रियाएँ पेजों में डेटा लौटाती हैं। सब कुछ एक साथ लोड करने के बजाय एक समय में एक पेज प्रोसेस करें। बड़े डाटासेट्स के साथ काम करते समय यह टाइमआउट और मेमोरी समस्याओं से बचाता है।

Gzip कंप्रेशन

अधिकांश Google क्लाइंट लाइब्रेरीज़ और HTTP क्लाइंट्स स्वतः gzip कंप्रेशन का समर्थन करते हैं। जब प्रतिक्रियाएँ बड़ी होती हैं, जैसे वाइड रेंज पढ़ते समय या स्प्रेडशीट मेटाडेटा लाते समय, कंप्रेशन आकार घटाता है।

आंशिक प्रतिक्रियाएँ

जहाँ संभव हो, पूर्ण संसाधन के बजाय केवल आवश्यक फ़ील्ड्स का अनुरोध करें। छोटी प्रतिक्रियाएँ तेज़ होती हैं और कम बैंडविड्थ लेती हैं।

पेलोड आकार कम करें

एक-एक सेल लिखने से बचें। इसके बजाय पूरी रेंज लिखें। यदि कुछ नहीं बदला है तो फॉर्मैटिंग अपडेट्स को छोड़ दें, और अनावश्यक डेटा फिर से न भेजें।

यहाँ एक सरल नियम अच्छा काम करता है: यदि कोई स्क्रिप्ट पंक्तियों पर लूप करती है और उस लूप के अंदर API कॉल करती है, तो वह स्केल नहीं होगी। काम को बैच करें, अनुरोधों की संख्या घटाएँ, और पेलोड छोटे रखें।

त्रुटि प्रबंधन और समस्या निवारण

किसी भी API के साथ काम करते समय त्रुटियाँ आती हैं। तो, आइए देखें कि समस्याओं को जल्दी कैसे ठीक करें और स्वचालन को विश्वसनीय रखें। 

सामान्य प्रमाणीकरण समस्याएँ

Google Sheets API के साथ शुरुआत करते समय सबसे सामान्य त्रुटियाँ प्रमाणीकरण से जुड़ी होती हैं। अधिकांश सेटअप समस्याओं से आती हैं, आपके कोड की बग्स से नहीं।

सामान्य समस्याएँ शामिल हैं:

• Permission denied त्रुटियाँ: स्प्रेडशीट सर्विस अकाउंट ईमेल के साथ साझा नहीं की गई, या उपयोगकर्ता ने आवश्यक OAuth स्कोप्स की अनुमति नहीं दी
• Invalid credentials या file not found त्रुटियाँ: JSON कुंजी फ़ाइल का पथ गलत है या फ़ाइलनाम मेल नहीं खाता
• API not enabled त्रुटियाँ: Google Sheets API, उन्हीं Google Cloud प्रोजेक्ट में सक्षम नहीं है जहाँ क्रेडेंशियल्स हैं

OAuth समस्याएँ अक्सर तब होती हैं जब स्कोप्स उस क्रिया से मेल नहीं खाते जो आप करना चाहते हैं। उदाहरण के लिए, read-only स्कोप्स लिखने की अनुमति नहीं देंगे।

सर्विस अकाउंट समस्याएँ आमतौर पर फाइल शेयरिंग की कमी से आती हैं। सर्विस अकाउंट किसी स्प्रेडशीट को तब तक नहीं देख सकता जब तक कि उसे उसके ईमेल पते के साथ स्पष्ट रूप से साझा न किया गया हो।

डेटा फॉर्मैटिंग और अनुरोध त्रुटियाँ

अनुरोध त्रुटियाँ आमतौर पर तब होती हैं जब भेजा गया डेटा API की अपेक्षा से मेल नहीं खाता। त्रुटि संदेश अक्सर वर्णनात्मक होते हैं, लेकिन शुरुआत में वे फिर भी भ्रमित कर सकते हैं।

सामान्य कारणों में शामिल हैं:

• अमान्य A1 नोटेशन: शीट नामों की वर्तनी गलत है या रेंज मौजूद नहीं

• गलत डेटा संरचना: मानों को द्वि-आयामी ऐरे के रूप में भेजना चाहिए, भले ही एक ही पंक्ति हो

• टाइप मिसमैच: संख्याएँ, दिनांक, और फ़ॉर्मूले valueInputOption पर निर्भर करते हुए अलग तरह से व्यवहार करते हैं

समस्या निवारण करते समय, हमेशा पूरी त्रुटि प्रतिक्रिया पढ़ें क्योंकि Google APIs आमतौर पर ठीक उसी फ़ील्ड की ओर इशारा करते हैं जो सत्यापन में विफल हुआ।

सेवा उपलब्धता और प्रदर्शन चुनौतियाँ

किसी भी क्लाउड सेवा की तरह, Google Sheets API कभी-कभी उच्च लोड या अस्थायी आउटेज के दौरान 503 Service Unavailable जैसी सर्वर त्रुटियाँ लौटा सकता है।

ये त्रुटियाँ आमतौर पर अस्थायी होती हैं। लेकिन तुरंत पुनः प्रयास न करें क्योंकि इससे स्थिति और खराब हो सकती है।

विश्वसनीयता समस्याओं को अधिक प्रभावी ढंग से संभालने के लिए:

• केवल 429 और 5xx जैसी अस्थायी त्रुटियों के लिए ही अनुरोधों को पुनः प्रयास करें
• पुनः प्रयासों के बीच एक्सपोनेंशियल बैकऑफ का उपयोग करें
• ट्रैफ़िक कम करने के लिए अनुरोधों को बैच करें
• सीमाएँ हिट होने से बचने के लिए कोटा उपयोग मॉनिटर करें

एक सुदृढ़ इंटीग्रेशन मानकर चलता है कि कुछ अनुरोध विफल होंगे। उचित पुनः प्रयास, बैचिंग, और मॉनिटरिंग आपकी स्क्रिप्ट्स को स्वतः उबरने और बिना मैन्युअल हस्तक्षेप के चलते रहने देती हैं।

सर्वोत्तम उपाय और सुरक्षा

Google Sheets API के साथ काम करना आसान है, लेकिन दीर्घकालिक विश्वसनीयता इस पर निर्भर करती है कि हम अपना सेटअप कैसे डिज़ाइन और सुरक्षित करते हैं।

डेवलपमेंट टूल्स और संसाधन

Google विकास को सुरक्षित और आसान बनाने के लिए आधिकारिक टूल्स और संसाधन प्रदान करता है:

• Google Workspace APIs Explorer आपको ब्राउज़र में ही Sheets API अनुरोधों का परीक्षण करने देता है। आप एंडपॉइंट्स के साथ प्रयोग कर सकते हैं, आवश्यक पैरामीटर्स देख सकते हैं, और बिना कोड लिखे प्रतिक्रियाएँ जाँच सकते हैं। 
• Sheets API क्विकस्टार्ट गाइड्स अलग-अलग भाषाओं के लिए बुनियादी सेटअप बताते हैं। ये प्रमाणीकरण, एक सरल रीड या राइट कॉल, और सामान्य कॉन्फ़िगरेशन चरण दिखाते हैं। 
• आधिकारिक डॉक्यूमेंटेशन वर्तमान व्यवहार और ज्ञात सीमाओं को दर्शाता है।
• एक टेस्ट स्प्रेडशीट आपको बिना प्रोडक्शन डेटा जोखिम में डाले या लाइव रिपोर्ट्स को ओवरराइट किए प्रयोग करने देती है।

क्लाइंट लाइब्रेरीज़ और SDK का उपयोग

Google Sheets API के लिए Google आधिकारिक क्लाइंट लाइब्रेरीज़ बनाए रखता है। ये लाइब्रेरीज़ प्रमाणीकरण, पुनः प्रयास, और अनुरोध फॉर्मैटिंग सँभालती हैं। हम समर्थित लाइब्रेरीज़ के साथ त्रुटियाँ घटा सकते हैं और समय बचा सकते हैं। ये कस्टम HTTP कोड की तुलना में API अपडेट्स के अनुरूप बेहतर ढलती हैं।

समर्थित भाषाएँ हैं Python, Node.js के साथ JavaScript, Java, और Go.

प्रत्येक लाइब्रेरी एक ही मुख्य API संरचना का पालन करती है, लेकिन कुछ भाषा-विशिष्ट हेल्पर्स प्रदान करती हैं। ऑटोमेशन के लिए Python और Node.js अक्सर सरल सेटअप और मज़बूत समुदाय समर्थन के कारण पसंद किए जाते हैं।

क्लाइंट लाइब्रेरीज़ अन्य Google APIs के साथ भी सहजता से इंटीग्रेट होती हैं, जो तब मदद करती हैं जब कोई वर्कफ़्लो Drive, BigQuery, या Gmail शामिल करता है।

क्रेडेंशियल प्रबंधन और सुरक्षा

क्रेडेंशियल्स आपके डेटा तक पहुँच को नियंत्रित करते हैं, इसलिए इन्हें सही ढंग से संभालें और इन सर्वोत्तम उपायों का पालन करें:

• कुंजी फ़ाइलों को कभी भी वर्ज़न कंट्रोल में कमिट न करें

• कुंजियों को सार्वजनिक रिपॉज़िटरीज़ के बाहर स्टोर करें

• प्रोडक्शन में environment variables या सीक्रेट मैनेजर्स का उपयोग करें

• केवल आवश्यक अनुमतियाँ दें

• विस्तृत भूमिकाओं की बजाय संकीर्ण भूमिकाएँ पसंद करें

• सर्विस अकाउंट एक्सेस को विशिष्ट कार्यों तक सीमित रखें

• असामान्य स्पाइक्स या अज्ञात एक्सेस पैटर्न पर नज़र रखें

• कुंजियों को नियमित रूप से रोटेट करें और उजागर कुंजियों को तुरंत रद्द कर बदलें

अंतिम विचार 

सबसे अच्छा अगला कदम है वह एक छोटा-सा काम स्वचालित करना जो आप पहले से हाथ से करते हैं। कोई ऐसी स्प्रेडशीट चुनें जिस पर आपका नियंत्रण हो, एक रेंज पढ़ें, फिर उसमें डेटा लिखें या जोड़ें। एक बार यह काम करने लगे, इसे शेड्यूल पर डाल दें और इसे अपने-आप चलने दें।

यहीं से Google Sheets API का असली लाभ मिलने लगता है। 

आपको तुरंत एक जटिल सिस्टम की आवश्यकता नहीं है। एक विश्वसनीय वर्कफ़्लो समय बचाने के लिए पर्याप्त है, और अगला बनाना बहुत आसान कर देता है। यदि आप स्वचालन के साथ-साथ उन कौशलों को निखारना चाहते हैं, तो हमारे Google Sheets Fundamentals ट्रैक या Introduction to Google Sheets, और Intermediate Google Sheets कोर्स देखें ताकि आप हर स्वचालित वर्कफ़्लो से अधिक मूल्य प्राप्त कर सकें।