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

1. परिचय

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

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

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

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

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

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

dMbN6g9RawQj_VXCSYpdYncY-DbaRzr2GbnwoV7jFf1u3avxJtmGPmKpMYgiaMH-qu80a_NJ9p2IIXFppYk8x3wyymZXavjglNLJJhuXieCem56H30hwXtd8PvXGpXJO9gEUDu3cZw

ci9Oe6PgnbNuSYlMyvbXF1JdQyiHoEgnhl4PlV_MFagm2ppzhueRkqX4eLjJllZco_2zCp0V0bpTupUSKji9KkQyWqj11pqit1K1faS1V6aFxLGQdkuzGp4rsQTan7F01iePL5DtqQ

8-tA_Lheyo8SscAVKrGii2coplQp2_D1Iosb2ViABY0UUO1A8cimXUu6Wf1R9zJIRExL5OB2j946aIiFtyKTzxDcNnuznmR45vZ2HMoK3o67jxuoUJCAnqvEX6NgPGFjCVNgASc-lg

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ट्रिगर को "Cloud Storage" पर सेट करना न भूलें. साथ ही, बकेट के नाम को पिछले चरण में बनाए गए नाम के हिसाब से अडजस्ट करें.

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

अब फ़ंक्शन के मुख्य हिस्से को बदलकर यह करें :

Cloud Storage और Sheets API का इस्तेमाल करना
csv2sheet फ़ंक्शन को async के तौर पर मार्क करें
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 नाम का एक एनवायरमेंट वैरिएबल तय करें. यह वैरिएबल, उस शीट दस्तावेज़ से मेल खाना चाहिए जिसे आपने पहले बनाया था :

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

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

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

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

इनलाइन एडिटर का इस्तेमाल करके, Cloud फ़ंक्शन में कोई और कोड लिखने से पहले, हमें स्टोरेज और शीट के सही स्कोप के साथ 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 क्लाइंट का इस्तेमाल करके एक फ़ंक्शन दिया गया है. इससे दूसरी पोज़िशन पर एक खाली शीट बनाई जा सकती है. आम तौर पर, यह डिफ़ॉल्ट "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. नई बनाई गई शीट में डेटा डालना

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

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 वैल्यू) के लिए, यह कोड काम नहीं करेगा.

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

अपडेट किए गए फ़ंक्शन को सेव करें
बकेट में CSV फ़ाइल छोड़ें
आपको अपनी स्प्रेडशीट में उससे जुड़ा डेटा दिखेगा!

10. सभी को एक साथ जोड़ना और फ़्लो की जांच करना

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

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

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

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

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

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

हमारा मज़ाक था, हमें किसी इंफ़्रास्ट्रक्चर को बंद नहीं करना पड़ा. यह सब सर्वरलेस तरीके से किया गया!

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

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

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

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

Cloud Functions के तरीके से जुड़ी गाइड देखें. इसमें कुछ सबसे सही तरीके भी शामिल हैं
Cloud Functions के किसी ट्यूटोरियल को देखें
Google Sheets API के बारे में ज़्यादा जानें

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

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