Google Sheets में CSV डेटा इंपोर्ट अपने-आप होने के लिए Cloud फ़ंक्शन

1. परिचय

इस कोडलैब का मकसद, आपको यह समझना है कि Cloud Storage पर अपलोड की गई CSV फ़ाइल पर प्रतिक्रिया देने के लिए, Cloud फ़ंक्शन कैसे लिखना है, ताकि उसका कॉन्टेंट पढ़ा जा सके और Sheets API का इस्तेमाल करके किसी Google शीट को अपडेट किया जा सके.

e9c78061022a6760.png

इसे मैन्युअल "CSV के रूप में इंपोर्ट करें" के ऑटोमेशन के तौर पर देखा जा सकता है चरण पूरा करें. इससे यह पक्का हो जाएगा कि डेटा उपलब्ध होते ही, स्प्रेडशीट में उसका विश्लेषण किया जा सकता है. ऐसा हो सकता है कि किसी अन्य टीम ने उसे बनाया हो.

लागू होने पर ऐसा दिखेगा :

52df703605ae4bd3.png

2. सेटअप और ज़रूरी शर्तें

अपने हिसाब से एनवायरमेंट सेटअप करना

  1. Cloud Console में साइन इन करें और नया प्रोजेक्ट बनाएं या किसी मौजूदा प्रोजेक्ट का फिर से इस्तेमाल करें. (अगर आपके पास पहले से Gmail या G Suite खाता नहीं है, तो आपको एक खाता बनाना होगा.)

dMbN6g9RawQj_VXCSYpdYncY-DbaRzr2GbnwoV7jFf1u3avxJtmGPmKpMYgiaMH-qu80a_NJ9p2IIXFppYk8x3wyymZXavjglNLJJhuXieCem56H30hwXtd8PvXGpXJO9gEUDu3cZw

ci9Oe6PgnbNuSYlMyvbXF1JdQyiHoEgnhl4PlV_MFagm2ppzhueRkqX4eLjJllZco_2zCp0V0bpTupUSKji9KkQyWqj11pqit1K1faS1V6aFxLGQdkuzGp4rsQTan7F01iePL5DtqQ

8-tA_Lheyo8SscAVKrGii2coplQp2_D1Iosb2ViABY0UUO1A8cimXUu6Wf1R9zJIRExL5OB2j946aIiFtyKTzxDcNnuznmR45vZ2HMoK3o67jxuoUJCAnqvEX6NgPGFjCVNgASc-lg

प्रोजेक्ट आईडी याद रखें. यह Google Cloud के सभी प्रोजेक्ट के लिए एक खास नाम होता है (ऊपर दिया गया नाम पहले ही ले लिया गया है और यह आपके लिए काम नहीं करेगा!). बाद में, इस कोडलैब को इस कोडलैब में PROJECT_ID के तौर पर दिखाया जाएगा.

  1. इसके बाद, आपको Google Cloud के संसाधनों का इस्तेमाल करने के लिए, Cloud Console में बिलिंग की सुविधा चालू करनी होगी.

इस कोडलैब का इस्तेमाल करने पर, आपको ज़्यादा पैसे नहीं चुकाने होंगे. "साफ़ करना" सेक्शन में दिए गए निर्देशों का पालन करना न भूलें सेक्शन में, संसाधनों को बंद करने का तरीका बताया गया है. इससे इस ट्यूटोरियल के अलावा बिलिंग की सुविधा नहीं मिलेगी. Google Cloud के नए उपयोगकर्ता, 300USD डॉलर के मुफ़्त में आज़माने वाले प्रोग्राम में हिस्सा ले सकते हैं.

3. Google शीट बनाना, उसे कॉन्फ़िगर करना, और एपीआई चालू करना

सबसे पहले, चलिए एक नया शीट दस्तावेज़ बनाते हैं (यह शीट किसी भी उपयोगकर्ता की हो सकती है). एक बार बनाए जाने के बाद, इसके पहचानकर्ता को याद रखें; इसे उस फ़ंक्शन के लिए एनवायरमेंट वैरिएबल के तौर पर इस्तेमाल किया जाएगा जिसे हम लिखेंगे :

dd77d5fc1364ad3e.png

GCP कंसोल से, "एपीआई और सेवाएं" पर जाकर, अपने नए प्रोजेक्ट के लिए Google Sheets API चालू करें. और फिर "एपीआई लाइब्रेरी" सेक्शन :

c64e2e98b8b55f16.png

"IAM और एडमिन" सेक्शन में, "सेवा खाते" पर जाएं और App Engine के डिफ़ॉल्ट सेवा खाते के लिए ईमेल नोट करें. यह your-project-id@appspot.gserviceaccount.com फ़ॉर्मैट में होना चाहिए. बेशक, आप इस कार्रवाई के लिए अपना सेवा खाता भी बना सकते हैं.

6e279d7e07d4febf.png

आखिर में, "शेयर करें" का इस्तेमाल करके, इस सेवा खाते में बदलाव करने का अधिकार अपनी स्प्रेडशीट में दें बटन :

c334062465ddf928.png

इस सेटअप की मदद से, अब हम अपने Cloud Function को तैयार कर सकते हैं और उसे इस सेवा खाते का इस्तेमाल करने के लिए कॉन्फ़िगर कर सकते हैं. वह हमारे द्वारा अभी बनाए गए इस स्प्रेडशीट दस्तावेज़ में लिख सकेगा.

4. स्टोरेज बकेट बनाना

चलिए, एक बकेट बनाते हैं, जिसे नई CSV फ़ाइलों के लिए, हमारा क्लाउड फ़ंक्शन मॉनिटर करेगा.

कंसोल में, बाईं ओर मौजूद मेन्यू का इस्तेमाल करके "स्टोरेज" पर जाएं... :

2ddcb54423979d25.png

... और सभी अन्य सेटिंग को उनके डिफ़ॉल्ट मानों पर सेट करके csv2sheet-POSTFIX नाम की एक नई बकेट बनाएं (POSTFIX को किसी यूनीक से बदलें) :

dd637080ade62e81.png

5. Cloud फ़ंक्शन बनाएं

अब हम csv2sheet नाम का एक Cloud फ़ंक्शन बना सकते हैं. यह फ़ंक्शन किसी Cloud Storage बकेट में फ़ाइल अपलोड करने पर ट्रिगर होता है. यह कोड, Cloud Console में इनलाइन एडिटर का इस्तेमाल करके एक साथ काम नहीं करने वाले फ़ंक्शन के साथ Node.js 8 में लिखा जाएगा :

6ee1a5ce63174ae8.png

पक्का करें कि आपने ट्रिगर को "Cloud Storage" पर सेट किया हो और बकेट का नाम पिछले चरण में बनाए गए नाम पर समायोजित करने के लिए.

साथ ही, उस फ़ंक्शन के लिए एंट्रीपॉइंट अपडेट करें जिसे हम csv2sheet पर लिखने वाले हैं :

446e7c7c992c2d8a.png

अब फ़ंक्शन के मुख्य भाग को इसमें बदलें :

  1. Cloud Storage और Sheets एपीआई का इस्तेमाल करते हैं
  2. csv2sheet फ़ंक्शन को async के तौर पर मार्क करें
  3. Cloud Storage के इवेंट मेटाडेटा से fileName को होल्ड करें और उस नई शीट का नाम तय करें जो हम बनाएंगे :
const {google} = require("googleapis");
const {Storage} = require("@google-cloud/storage")

exports.csv2sheet = async (data, context) => {
  var fileName = data.name;
  // basic check that this is a *.csv file, etc...
  if (!fileName.endsWith(".csv")) {
    console.log("Not a .csv file, ignoring.");
    return;
  }
  // define name of new sheet  
  const sheetName = fileName.slice(0, -4);

  // TODO!
};

await का इस्तेमाल करने के लिए, यहां async का इस्तेमाल करना ज़रूरी है, जैसा कि हम जल्द ही देखेंगे.

इस प्रकार्य को बनाते समय कुछ महत्वपूर्ण विकल्पों में ये शामिल हैं (स्क्रीन के नीचे "अधिक" लिंक क्लिक करें) :

  • ड्रॉपडाउन मेन्यू का इस्तेमाल करके, ऊपर बताए गए सेवा खाते को चुनें
  • SPREADSHEET_ID नाम का एक एनवायरमेंट वैरिएबल तय करें. यह वैरिएबल, पहले बनाए गए शीट दस्तावेज़ से मेल खाना चाहिए :

fd22d1873bcb8c66.png

सेटअप के आखिरी चरण में, यहां Cloud Storage और Google Sheets API के साथ दिया गया package.json कॉन्टेंट दिया गया है, जो दो डिपेंडेंसी के तौर पर इस्तेमाल किए जाएंगे. कंसोल के इनलाइन एडिटर PACKAGE.JSON टैब का इस्तेमाल करें :

{
    "name": "csv2sheet",
    "version": "0.0.42",
    "dependencies": {
        "googleapis": "^51.0.0",
        "@google-cloud/storage": "^5.0.1"
    }
}

सब कुछ बताए गए तरीके से कॉन्फ़िगर करने के बाद, "बनाएं" पर क्लिक करें ! कुछ ही मिनट के बाद, आपका फ़ंक्शन बन जाएगा और डिप्लॉय हो जाएगा.

6. पुष्टि करने और Sheets API को सेटअप करने का तरीका

इनलाइन एडिटर का इस्तेमाल करके, अपने क्लाउड फ़ंक्शन में कोई अन्य कोड लिखने से पहले, हमें Google Client API को सही स्टोरेज और शीट के स्कोप के साथ बनाने से रोकना होगा. ध्यान रखें कि यह async फ़ंक्शन का हिस्सा है.

कंसोल के फ़ंक्शन एडिटर में, "बदलाव करें" पर क्लिक करें और अपने csv2sheet फ़ंक्शन के मुख्य भाग में यह कोड जोड़ें :

// block on auth + getting the sheets API object
const auth = await google.auth.getClient({
  scopes: [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/devstorage.read_only"
  ]
});

वहां से हम Sheets API क्लाइंट बना सकते हैं :

const sheetsAPI = google.sheets({version: 'v4', auth});

7. खाली शीट बनाने के लिए Sheets API का इस्तेमाल करना

Sheets API क्लाइंट का इस्तेमाल करके, अपने दस्तावेज़ में एक नई शीट बनाई जा सकती है. हालांकि, आगे बढ़ने से पहले, उसकी शब्दावली पर ध्यान देने वाली ज़रूरी बातें यहां दी गई हैं:

  • स्प्रेडशीट एक असल दस्तावेज़ होता है और इसके आइडेंटिफ़ायर से इसका रेफ़रंस मिलता है. इस बारे में ऊपर बताया गया है और यह दस्तावेज़ के यूआरएल में दिखता है
  • शीट, दस्तावेज़ के टैब में से एक है. इसे टैब के नाम या शीट बनाते समय जनरेट किए गए आइडेंटिफ़ायर से रेफ़रंस के तौर पर इस्तेमाल किया जा सकता है

इसे ध्यान में रखते हुए, यहां एक फ़ंक्शन दिया गया है जो Sheets API क्लाइंट का इस्तेमाल करके, पोज़िशन 2 पर एक खाली शीट (आम तौर पर, डिफ़ॉल्ट "Sheet1 के बाद"), 26 कॉलम, 2,000 पंक्तियों और पहली पंक्ति को फ़्रीज़ करता है (इनलाइन एडिटर का इस्तेमाल करके इसे अपने फ़ंक्शन में जोड़ें) बनाने के लिए ऐसा करता है :

function addEmptySheet(sheetsAPI, sheetName) {
  return new Promise((resolve, reject) => {
    const emptySheetParams = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            addSheet: {
              properties: {
                title: sheetName,
                index: 1,
                gridProperties: {
                  rowCount: 2000,
                  columnCount: 26,
                  frozenRowCount: 1
                }
              }
            }
          }
        ]
      }
    };
    sheetsAPI.spreadsheets.batchUpdate( emptySheetParams, function(err, response) {
        if (err) {
          reject("The Sheets API returned an error: " + err);
        } else {
          const sheetId = response.data.replies[0].addSheet.properties.sheetId;
          console.log("Created empty sheet: " + sheetId);
          resolve(sheetId);
        }
      }
    );
  });
}

ध्यान दें कि हम स्प्रेडशीट के रेफ़रंस को हार्ड कोड करने के बजाय, पहले बनाए गए SPREADSHEET_ID एनवायरमेंट वैरिएबल का इस्तेमाल कैसे करते हैं.

इस शीट पर किए गए अन्य अनुरोधों के लिए, हमें sheetId याद रखना होगा. साथ ही, शीट का नाम यूनीक होना चाहिए. अगर sheetName नाम की शीट पहले से मौजूद है, तो शीट का नाम नहीं बनेगा.

Sheets API में मौजूद batchUpdate फ़ंक्शन, दस्तावेज़ों के साथ इंटरैक्ट करने का एक सामान्य तरीका है. इसके बारे में यहां बताया गया है.

8. स्टोरेज वाली CSV फ़ाइल में मौजूद डेटा को पढ़ना

अब हमारे पास अपना डेटा डालने के लिए जगह है, इसलिए अब इनलाइन एडिटर में क्लाउड फ़ंक्शन डेवलप किया जाता है. साथ ही, हाल ही में अपलोड की गई फ़ाइल से असली डेटा लेकर उसे स्ट्रिंग में सेव करने के लिए, Cloud Storage API का इस्तेमाल किया जाता है:

function readCSVContent(sheetsAPI, file, sheetName) {
  return new Promise((resolve, reject) => {
    const storage = new Storage();
    let fileContents = new Buffer('');
    storage.bucket(file.bucket).file(file.name).createReadStream()
    .on('error', function(err) {
      reject('The Storage API returned an error: ' + err);
    })
    .on('data', function(chunk) {
      fileContents = Buffer.concat([fileContents, chunk]);
    })  
    .on('end', function() {
      let content = fileContents.toString('utf8');
      console.log("CSV content read as string : " + content );
      resolve(content);
    });
  });
}

9. नई बनाई गई शीट को भरें

अब उसी शीट क्लाइंट एपीआई का इस्तेमाल करके बनाई गई शीट को भरने का समय आ गया है जिसे हमने अभी-अभी इकट्ठा किया है. इस अवसर की मदद से, हम शीट के कॉलम में कुछ स्टाइल भी जोड़ेंगे (सबसे ऊपर की लाइन का फ़ॉन्ट साइज़ बदलना और उसे बोल्ड करना) :

function populateAndStyle(sheetsAPI, theData, sheetId) {
  return new Promise((resolve, reject) => {
    // Using 'batchUpdate' allows for multiple 'requests' to be sent in a single batch.
    // Populate the sheet referenced by its ID with the data received (a CSV string)
    // Style: set first row font size to 11 and to Bold. Exercise left for the reader: resize columns
    const dataAndStyle = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            pasteData: {
              coordinate: {
                sheetId: sheetId,
                rowIndex: 0,
                columnIndex: 0
              },
              data: theData,
              delimiter: ","
            }
          },
          {
            repeatCell: {
              range: {
                sheetId: sheetId,
                startRowIndex: 0,
                endRowIndex: 1
              },
              cell: {
                userEnteredFormat: {
                  textFormat: {
                    fontSize: 11,
                    bold: true
                  }
                }
              },
              fields: "userEnteredFormat(textFormat)"
            }
          }       
        ]
      }
    };
        
    sheetsAPI.spreadsheets.batchUpdate(dataAndStyle, function(err, response) {
      if (err) {
        reject("The Sheets API returned an error: " + err);
      } else {
        console.log(sheetId + " sheet populated with " + theData.length + " rows and column style set.");
        resolve();
      }
    });    
  });
}

यह कोड हमारे क्लाउड फ़ंक्शन में जोड़ा जाना चाहिए, जो अब 99% पूरा हो गया है!

ध्यान दें कि डेटा और स्टाइलिंग, दोनों को एक से ज़्यादा requests के तौर पर, एक Sheets API batchUpdate कॉल में कैसे जोड़ा जाता है. इससे बेहतर और ऐटॉमिक अपडेट मिलता है.

ध्यान दें कि हम बदलाव की एक रेंज भी तय करते हैं, जो हमारी बनाई शीट के साइज़ से मेल खाती है. इसका मतलब है कि 26 कॉलम (शीट बनाने के दौरान इस्तेमाल की गई columnCount वैल्यू) से ज़्यादा के कॉन्टेंट को इस कोड के साथ फ़ेल कर दिया जाएगा.

अगर सब ठीक रहता है, तो अब ये काम किए जा सकते हैं:

  1. अपडेट किए गए फ़ंक्शन को सेव करें
  2. बकेट में CSV फ़ाइल डालें
  3. अपनी स्प्रेडशीट में संबंधित डेटा का पॉप-अप देखें!

10. इन्हें एक जगह इकट्ठा किया जा रहा है और प्रोसेस को टेस्ट किया जा रहा है

हमने अभी जिन फ़ंक्शन के बारे में चर्चा की है उन्हें ओरिजनल csv2sheet फ़ंक्शन में, ब्लॉक करने वाले कॉल के तौर पर क्रम से लगाया जा सकता है:

  const sheetId = await addEmptySheet(sheetsAPI, sheetName);
  const theData = await readCSVContent(sheetsAPI, data, sheetName);
  await populateAndStyle(sheetsAPI, theData, sheetId);

अगर आपको फ़ंक्शन का पूरा सोर्स कोड चाहिए, तो यह यहां उपलब्ध है. इसे एक ही सेट में आसानी से ऐक्सेस किया जा सकता है.

जब सब कुछ सही जगह पर हो जाए, तो सही बकेट में CSV फ़ाइल अपलोड करें. इसके बाद, देखें कि आपकी स्प्रेडशीट एक नई शीट में, फ़ाइल के कॉन्टेंट के साथ अपडेट होती है या नहीं. अगर आपके पास पहले से कोई CSV फ़ाइल नहीं है, तो यहां एक सैंपल CSV फ़ाइल दी गई है.

1efae021942e64fa.png

यह देखने के लिए कि क्या होता है, बकेट में कई फ़ाइलें अपलोड करके देखें!

11. हो गया! इन्फ़्रास्ट्रक्चर को खराब करने का समय

हम मज़ाक़ कर रहे हैं. यह काम बिना सर्वर के पूरा किया जा सकता है. ऐसा करने के लिए कोई सिस्टम नहीं है !

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

12. आगे क्या करना है?

इसके बाद, यह कोडलैब आपको Cloud फ़ंक्शन में Cloud Storage बकेट में अपलोड सुनने के चरणों के बारे में बताता है, ताकि Google शीट को सही एपीआई का इस्तेमाल करके अपडेट किया जा सके.

यहां फ़ॉलो-अप करने के कुछ चरण दिए गए हैं :

अगर आपको इस कोडलैब के साथ कोई समस्या आ रही है, तो सबसे नीचे बाएं कोने में दिए गए लिंक पर जाकर, किसी भी समस्या की शिकायत करें.

आपके सुझाव, शिकायत या राय से हमें मदद मिलती है!