1. परिचय
Apps Script की बुनियादी बातें, Google Sheets कोडलैब की प्लेलिस्ट के चौथे हिस्से में आपका स्वागत है.
इस कोडलैब को पूरा करके, आप Apps स्क्रिप्ट में अपने स्प्रेडशीट डेटा को फ़ॉर्मैट करने का तरीका जान सकते हैं. साथ ही, सार्वजनिक एपीआई से फ़ेच किए गए फ़ॉर्मैट किए गए डेटा से भरी व्यवस्थित स्प्रेडशीट बनाने के लिए फ़ंक्शन लिख सकते हैं.
आप इन चीज़ों के बारे में जानेंगे
- Apps Script में, Google Sheets की फ़ॉर्मैटिंग की कार्रवाइयों को लागू करने का तरीका.
- JSON ऑब्जेक्ट और उनके एट्रिब्यूट की सूची को Apps Script की मदद से, डेटा की व्यवस्थित शीट में बदलने का तरीका.
शुरू करने से पहले
Apps Sheets की बुनियादी बातों और Google Sheets की प्लेलिस्ट में यह चौथा कोडलैब (कोड बनाना सीखना) है. यह कोडलैब शुरू करने से पहले, पिछले कोडलैब (कोड बनाना सीखना) को पूरा करना न भूलें:
आपको इनकी ज़रूरत होगी
- इस प्लेलिस्ट के पिछले कोडलैब में एक्सप्लोर किए गए बेसिक ऐप्लिकेशन स्क्रिप्ट के विषयों को समझना.
- Apps Script एडिटर की बुनियादी जानकारी
- Google Sheets के बारे में बुनियादी जानकारी
- Sheets A1 नोटेशन को पढ़ने की सुविधा
- JavaScript और उसकी
Stringक्लास के बारे में बुनियादी जानकारी
2. सेट अप करें
जारी रखने से पहले, आपको कुछ डेटा वाली स्प्रेडशीट की ज़रूरत होगी. पहले की तरह, हमने ये कसरतों के लिए डेटा शीट उपलब्ध कराई है. यहां बताया गया तरीका अपनाएं:
- डेटा शीट कॉपी करने के लिए, इस लिंक पर क्लिक करें और फिरकॉपी बनाएं . नई स्प्रेडशीट आपके Google Drive फ़ोल्डर में रखी जाती है और उसे &डेटा कोट किया जाता है;.
- स्प्रेडशीट के शीर्षक पर क्लिक करें और इसे "डेटा फ़ॉर्मेट की कॉपी &कोटेशन; & &डेटा को कोट करें& में बदलें{/1}. आपकी शीट इस तरह दिखनी चाहिए, जिसमें शुरुआती तीन स्टार वॉर्स फ़िल्म की कुछ बुनियादी जानकारी दी गई हो:
- स्क्रिप्ट एडिटर खोलने के लिए, एक्सटेंशन &g; Apps स्क्रिप्ट को चुनें.
- Apps Script प्रोजेक्ट के शीर्षक पर क्लिक करें और इसे "बिना शीर्षक वाले प्रोजेक्ट&kot; से &कोटेशन;डेटा फ़ॉर्मैटिंग;& में बदलें; शीर्षक में किए गए बदलाव को सेव करने के लिए, नाम बदलें पर क्लिक करें.
इस स्प्रेडशीट और प्रोजेक्ट के साथ, आप कोडलैब (कोड बनाना सीखना) शुरू करने के लिए तैयार हैं. Apps Script में मूल फ़ॉर्मैटिंग के बारे में जानने के लिए, अगले सेक्शन पर जाएं.
3. कस्टम मेन्यू बनाएं
आप Apps Script में कई बुनियादी फ़ॉर्मैटिंग तरीके अपनी Sheets पर लागू कर सकते हैं. नीचे दिए गए कसरतों में डेटा को फ़ॉर्मैट करने के कुछ तरीके बताए गए हैं. अपनी फ़ॉर्मेटिंग कार्रवाई को नियंत्रित करने में सहायता के लिए, आइटम की ज़रूरत के मुताबिक आइटम के लिए #&93 बनाएं. कस्टम मेन्यू बनाने की प्रक्रिया का डेटा के साथ काम करने वाले कोडलैब में बताया गया था, लेकिन हम इसे #39;यहां और कम शब्दों में बताएंगे.
लागू करना
आइए एक कस्टम मेन्यू बनाएं.
- Apps Script एडिटर में, अपनी स्क्रिप्ट प्रोजेक्ट के कोड को इनमें से बदलें:
/**
* A special function that runs when the spreadsheet is opened
* or reloaded, used to add a custom menu to the spreadsheet.
*/
function onOpen() {
// Get the spreadsheet's user-interface object.
var ui = SpreadsheetApp.getUi();
// Create and add a named menu and its items to the menu bar.
ui.createMenu('Quick formats')
.addItem('Format row header', 'formatRowHeader')
.addItem('Format column header', 'formatColumnHeader')
.addItem('Format dataset', 'formatDataset')
.addToUi();
}
- अपना स्क्रिप्ट प्रोजेक्ट सेव करें.
- स्क्रिप्ट एडिटर में, फ़ंक्शन सूची में से
onOpenचुनें और चलाएं पर क्लिक करें. स्प्रेडशीट मेन्यू को फिर से बनाने के लिए,onOpen()चलाएं. इससे आपको स्प्रेडशीट फिर से लोड नहीं करनी पड़ती.
कोड की समीक्षा
आइए, इस कोड की समीक्षा करें और समझें कि यह कैसे काम करता है. onOpen() में, पहली लाइन getUi() वाले तरीके का इस्तेमाल करके, ऐसी Ui ऑब्जेक्ट को हासिल करती है जो इस स्क्रिप्ट से जुड़ी ऐक्टिव स्प्रेडशीट के यूज़र इंटरफ़ेस को दिखाती है.
अगली लाइनें मेन्यू (Quick formats), मेन्यू के आइटम (Format row header, Format column header, और Format dataset) बनाएं. इसके बाद, मेन्यू को स्प्रेडशीट's इंटरफ़ेस में जोड़ें. यह createMenu(caption), addItem(caption, functionName), और addToUi() तरीकों से किया जाता है.
addItem(caption, functionName) मेथड, मेन्यू आइटम लेबल और Apps Script फ़ंक्शन के बीच एक कनेक्शन बनाता है. यह फ़ंक्शन, मेन्यू आइटम चुने जाने पर चलता है. उदाहरण के लिए, Format row header मेन्यू आइटम को चुनने पर Sheets, formatRowHeader() फ़ंक्शन चलाने की कोशिश करता है (जो अब तक मौजूद नहीं है).
नतीजे
अपनी स्प्रेडशीट में, नए मेन्यू आइटम देखने के लिए Quick formats मेन्यू पर क्लिक करें:
इन आइटम पर क्लिक करने से गड़बड़ी होती है, क्योंकि आपने इनके सही फ़ंक्शन को लागू नहीं किया है. इसलिए, इसके बाद ऐसा करें.
4. हेडर पंक्ति फ़ॉर्मैट करना
स्प्रेडशीट में डेटासेट में अक्सर हर कॉलम के डेटा की पहचान करने के लिए, हेडर लाइन होती हैं. हेडर पंक्तियों को विज़ुअल रूप से स्प्रेडशीट के बाकी डेटा से अलग करने के लिए इसे फ़ॉर्मैट करना एक अच्छा तरीका है.
पहले कोडलैब में, आपने अपने हेडर के लिए एक मैक्रो बनाया और उसके कोड में बदलाव किया. यहां, आप Apps Script का इस्तेमाल करके हेडर पंक्ति को शुरू से फ़ॉर्मैट करेंगे. आप हेडर लाइन को #39;बनाएंगे, हेडर टेक्स्ट को बोल्ड करेगा. साथ ही, बैकग्राउंड को गहरा नीला-हरा रंग देगा, टेक्स्ट के रंग को सफ़ेद कर देगा, और कुछ ठोस बॉर्डरलाइन जोड़ेंगे.
लागू करना
फ़ॉर्मैटिंग की कार्रवाई लागू करने के लिए, आप उसी स्प्रेडशीट सेवा तरीके का इस्तेमाल करेंगे जिसका आपने पहले इस्तेमाल किया है, लेकिन अब आप #39; सेवा और फ़ॉर्मैट करने के कुछ तरीकों का भी इस्तेमाल करेंगे. यहां बताया गया तरीका अपनाएं:
- Apps Script एडिटर में, अपनी स्क्रिप्ट प्रोजेक्ट के आखिर में यह फ़ंक्शन जोड़ें:
/**
* Formats top row of sheet using our header row style.
*/
function formatRowHeader() {
// Get the current active sheet and the top row's range.
var sheet = SpreadsheetApp.getActiveSheet();
var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
// Apply each format to the top row: bold white text,
// blue-green background, and a solid black border
// around the cells.
headerRange
.setFontWeight('bold')
.setFontColor('#ffffff')
.setBackground('#007272')
.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}
- अपना स्क्रिप्ट प्रोजेक्ट सेव करें.
कोड की समीक्षा
कई फ़ॉर्मैटिंग टास्क की तरह, इसे लागू करने वाला Apps Script कोड भी आसान है. मौजूदा दो शीट में मौजूदा चालू शीट (sheet) और शीट की सबसे ऊपर वाली लाइन (headerRange)) का रेफ़रंस पाने के लिए, पहले इस्तेमाल किए गए तरीकों का इस्तेमाल किया गया है. Sheet.getRange(row, column, numRows, numColumns) में सबसे ऊपर वाली पंक्ति के बारे में बताया गया है. इसमें, सिर्फ़ ऐसे कॉलम शामिल हैं जिनमें डेटा मौजूद है. Sheet.getLastColumn() मैथड आखिरी कॉलम के कॉलम इंडेक्स को दिखाता है जिसमें शीट में डेटा होता है. हमारे उदाहरण में, यह कॉलम # E (url) है.
headerRange के सभी सेल पर फ़ॉर्मैटिंग के विकल्पों को लागू करने के लिए, बाकी के कोड में Range के अलग-अलग तरीकों का इस्तेमाल किया जाता है. कोड को पढ़ना आसान बनाने के लिए, हम मैथिंग चेन का इस्तेमाल करते हैं, ताकि हर फ़ॉर्मैटिंग तरीके को एक के बाद एक कॉल किया जा सके:
Range.setFontWeight(fontWeight)का इस्तेमाल फ़ॉन्ट की मोटाई को बोल्ड पर सेट करने के लिए किया जाता है.Range.setFontColor(color)का इस्तेमाल, फ़ॉन्ट के रंग को सफ़ेद पर सेट करने के लिए किया जाता है.Range.setBackground(color)का इस्तेमाल बैकग्राउंड के रंग को गहरे नीले-हरे रंग में सेट करने के लिए किया जाता है.setBorder(top, left, bottom, right, vertical, horizontal, color, style)रेंज की रेंज के आस-पास एक काले रंग का बॉर्डर लगाता है.
आखिरी विधि में कई पैरामीटर हैं, इसलिए आइए देखें कि हर एक क्या कर रहा है. यहां दिए गए पहले चार पैरामीटर (सभी true पर सेट हैं) 'Apps स्क्रिप्ट' को बताते हैं कि बॉर्डर को ऊपर, नीचे, और रेंज के बाईं और दाईं ओर जोड़ा जाना चाहिए. पांचवें और छठे पैरामीटर (null और null) चुने गए रेंज में बॉर्डर की लाइनों को बदलने से बचने के लिए, Apps Script को डायरेक्ट करते हैं. सातवां पैरामीटर (null) बताता है कि बॉर्डर का रंग डिफ़ॉल्ट रूप से काला होना चाहिए. आखिर में, आखिरी पैरामीटर SpreadsheetApp.BorderStyle से मिले विकल्पों से लिया जाने वाला, बॉर्डर की शैली का प्रकार बताता है.
नतीजे
आप यह तरीका अपनाकर, अपने फ़ंक्शन में बदलाव कर सकते हैं:
- अगर आपने पहले से ##39 नहीं बनाए हैं, तो अपने स्क्रिप्ट प्रोजेक्ट को Apps Script एडिटर में सेव करें.
- फटाफट फ़ॉर्मैट &g; फ़ॉर्मैट वाले लाइन हेडर के मेन्यू आइटम पर क्लिक करें.
नतीजे कुछ इस तरह दिखेंगे:
अब आपने सभी फ़ॉर्मैट को फ़ॉर्मैट कर दिया है. अगला सेक्शन, कॉलम हेडर के लिए एक अलग फ़ॉर्मैट स्टाइल बनाने के लिए वही तकनीक लागू करता है.
5. कॉलम हेडर फ़ॉर्मैट करना
अगर आप मनमुताबिक हेडर लाइन बना सकते हैं, तो आप कॉलम हेडर भी बना सकते हैं. कॉलम हेडर, कुछ डेटासेट के लिए रीडबिलिटी बढ़ाते हैं. उदाहरण के लिए, इस स्प्रेडशीट में शीर्षक कॉलम को फ़ॉर्मैट के इन विकल्पों की मदद से बेहतर बनाया जा सकता है:
- बोल्ड करना
- इटैलिक करना
- सेल बॉर्डर जोड़ना
- url कॉलम के कॉन्टेंट का इस्तेमाल करके, हाइपरलिंक शामिल करना. एक बार जब आप ये हाइपरलिंक हाइपरलिंक कर लेते हैं, तब शीट को साफ़ करने के लिए यूआरएल कॉलम को हटाया जा सकता है.
इसके बाद, शीट में पहले कॉलम में इन बदलावों को लागू करने के लिए, आप formatColumnHeader() फ़ंक्शन लागू करेंगे. कोड को पढ़ने में थोड़ा आसान बनाने में मदद करने के लिए, आप दो हेल्पर फ़ंक्शन भी लागू करेंगे.
लागू करना
पहले की तरह, कॉलम हेडर फ़ॉर्मैटिंग को ऑटोमेट करने के लिए, आपको एक फ़ंक्शन जोड़ना होगा. यहां बताया गया तरीका अपनाएं:
- Apps Script एडिटर में, अपने स्क्रिप्ट प्रोजेक्ट के आखिर में, नीचे दिया गया
formatColumnHeader()फ़ंक्शन जोड़ें:
/**
* Formats the column header of the active sheet.
*/
function formatColumnHeader() {
var sheet = SpreadsheetApp.getActiveSheet();
// Get total number of rows in data range, not including
// the header row.
var numRows = sheet.getDataRange().getLastRow() - 1;
// Get the range of the column header.
var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
// Apply text formatting and add borders.
columnHeaderRange
.setFontWeight('bold')
.setFontStyle('italic')
.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
// Call helper method to hyperlink the first column contents
// to the url column contents.
hyperlinkColumnHeaders_(columnHeaderRange, numRows);
}
formatColumnHeader()फ़ंक्शन के बाद, अपनी स्क्रिप्ट प्रोजेक्ट के आखिर में ये हेल्पर फ़ंक्शन जोड़ें:
/**
* Helper function that hyperlinks the column header with the
* 'url' column contents. The function then removes the column.
*
* @param {object} headerRange The range of the column header
* to update.
* @param {number} numRows The size of the column header.
*/
function hyperlinkColumnHeaders_(headerRange, numRows) {
// Get header and url column indices.
var headerColIndex = 1;
var urlColIndex = columnIndexOf_('url');
// Exit if the url column is missing.
if(urlColIndex == -1)
return;
// Get header and url cell values.
var urlRange =
headerRange.offset(0, urlColIndex - headerColIndex);
var headerValues = headerRange.getValues();
var urlValues = urlRange.getValues();
// Updates header values to the hyperlinked header values.
for(var row = 0; row < numRows; row++){
headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
+ '","' + headerValues[row] + '")';
}
headerRange.setValues(headerValues);
// Delete the url column to clean up the sheet.
SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}
/**
* Helper function that goes through the headers of all columns
* and returns the index of the column with the specified name
* in row 1. If a column with that name does not exist,
* this function returns -1. If multiple columns have the same
* name in row 1, the index of the first one discovered is
* returned.
*
* @param {string} colName The name to find in the column
* headers.
* @return The index of that column in the active sheet,
* or -1 if the name isn't found.
*/
function columnIndexOf_(colName) {
// Get the current column names.
var sheet = SpreadsheetApp.getActiveSheet();
var columnHeaders =
sheet.getRange(1, 1, 1, sheet.getLastColumn());
var columnNames = columnHeaders.getValues();
// Loops through every column and returns the column index
// if the row 1 value of that column matches colName.
for(var col = 1; col <= columnNames[0].length; col++)
{
if(columnNames[0][col-1] === colName)
return col;
}
// Returns -1 if a column named colName does not exist.
return -1;
}
- अपना स्क्रिप्ट प्रोजेक्ट सेव करें.
कोड की समीक्षा
आइए इन तीन फ़ंक्शन में से हर एक में कोड की अलग से समीक्षा करें:
formatColumnHeader()
जैसा कि आपको उम्मीद है कि यह फ़ंक्शन उम्मीद के मुताबिक है, इस फ़ंक्शन की पहली कुछ लाइनें, शीट में सेट किए गए वैरिएबल और रेंज को सेट करती हैं जिनमें हम दिलचस्पी रखते हैं:
- सक्रिय शीट
sheetमें संग्रहित है. - कॉलम हेडर में पंक्तियों की संख्या की गिनती करके, उन्हें
numRowsमें सेव किया जाता है. यहां कोड एक को घटा देता है, ताकि पंक्ति की गिनती में कॉलम हेडर शामिल न हो:title. - कॉलम हेडर को कवर करने वाली सीमा,
columnHeaderRangeमें संग्रहित है.
ठीक कोड की तरह ही formatRowHeader(), हेडर हेडर रेंज में बॉर्डर लागू करता है. यहां, टेक्स्ट को इटैलिक करने के लिए Range.setFontStyle(fontStyle) का भी इस्तेमाल किया जाता है.
हेडर कॉलम में हाइपरलिंक जोड़ना ज़्यादा मुश्किल है, इसलिए formatColumnHeader() टास्क को मैनेज करने के लिए hyperlinkColumnHeaders_(headerRange, numRows) को कॉल करता है. इससे कोड को सही और पढ़ने में आसान बनाने में मदद मिलती है.
hyperlinkColumnHeaders_(headerRange, numRows)
यह हेल्पर फ़ंक्शन सबसे पहले हेडर (कॉलम 1 माना जाता है) और url कॉलम के कॉलम इंडेक्स की पहचान करता है. यह यूआरएल कॉलम इंडेक्स पाने के लिए columnIndexOf_('url') को कॉल करता है. अगर कोई url कॉलम #33 नहीं मिलता है, तो यह तरीका किसी भी डेटा में बदलाव किए बिना बाहर निकल जाता है.
फ़ंक्शन को एक नई रेंज (urlRange) मिलती है जो हेडर कॉलम की पंक्तियों से जुड़े यूआरएल को कवर करती है. ऐसा Range.offset(rowOffset, columnOffset) के तरीके से किया जाता है, जिससे यह गारंटी मिलती है कि दोनों रेंज एक ही साइज़ की होंगी. इसके बाद, headerColumn और url कॉलम, दोनों की वैल्यू (headerValues और urlValues) हासिल की जाती हैं.
इसके बाद, यह फ़ंक्शन हर कॉलम हेडर सेल वैल्यू पर लूप करता है और इसे हेडर और url कॉलम कॉन्टेंट के साथ बनाए गए =HYPERLINK() Sheets फ़ॉर्मूला से बदल देता है. इसके बाद, बदलाव किए गए हेडर वैल्यू Range.setValues(values) का इस्तेमाल करके शीट में डाली जाती हैं.
आखिर में, शीट को साफ़ रखने और ग़ैर-ज़रूरी जानकारी को खत्म करने के लिए, Sheet.deleteColumn(columnPosition) को url कॉलम हटाने के लिए कहा जाता है.
columnIndexOf_(colName)
यह हेल्पर फ़ंक्शन, बस एक सामान्य यूटिलिटी फ़ंक्शन है जो किसी खास नाम के लिए शीट की पहली पंक्ति में खोज करता है. पहली तीन पंक्तियों में उन तरीकों का इस्तेमाल किया गया है जिन्हें आपने #39;पहले से ही स्प्रेडशीट की पंक्ति 1 से कॉलम हेडर नामों की सूची पाने के लिए देखा है. ये नाम, वैरिएबल कॉलम के नाम में स्टोर किए जाते हैं.
इसके बाद, फ़ंक्शन हर नाम की समीक्षा करता है. अगर इसे कोई ऐसा यूआरएल मिलता है जो खोजे जा रहे नाम से मेल खाता है, तो यह कॉलम और#39; इंडेक्स को इंडेक्स कर देता है. अगर यह नाम खोजे बिना नाम की सूची के आखिर तक पहुंच जाता है, तो यह -1 को दिखाता है कि नाम नहीं ##39 था.
नतीजे
आप यह तरीका अपनाकर, अपने फ़ंक्शन में बदलाव कर सकते हैं:
- अगर आपने पहले से ##39 नहीं बनाए हैं, तो अपने स्क्रिप्ट प्रोजेक्ट को Apps Script एडिटर में सेव करें.
- क्विक फ़ॉर्मैट &g; फ़ॉर्मैट कॉलम हेडर मेन्यू आइटम पर क्लिक करें.
नतीजे कुछ इस तरह दिखेंगे:
अब आपने एक और फ़ॉर्मैटिंग टास्क को अपने-आप कर लिया है. कॉलम और पंक्ति हेडर के फ़ॉर्मैट के साथ, अगला सेक्शन डेटा फ़ॉर्मैट करने का तरीका दिखाता है.
6. अपना डेटासेट फ़ॉर्मैट करना
अब आपके पास हेडर हैं, तो चलिए एक ऐसा फ़ंक्शन बनाएं जो आपकी शीट में बाकी डेटा को फ़ॉर्मैट करता है. हम'फ़ॉर्मैट के इन विकल्पों का इस्तेमाल करेंगे:
- पंक्ति के बैकग्राउंड का रंग बदलना (इसे बैंडिंग कहा जाता है)
- तारीख के फ़ॉर्मैट बदलना
- बॉर्डर लागू किए जा रहे हैं
- सभी कॉलम और पंक्तियों को अपने-आप ऑटोमेट करना
अब आप #39;0formatDataset() फ़ंक्शन बनाएंगे और आपके शीट डेटा पर ये फ़ॉर्मैट लागू करने के लिए हेल्पर का एक और तरीका बनाएंगे.
लागू करना
पहले की तरह ही, डेटा को फ़ॉर्मैट करने के लिए कोई फ़ंक्शन जोड़ें. यहां बताया गया तरीका अपनाएं:
- Apps Script एडिटर में, अपने स्क्रिप्ट प्रोजेक्ट के आखिर में, नीचे दिया गया
formatDataset()फ़ंक्शन जोड़ें:
/**
* Formats the sheet data, excluding the header row and column.
* Applies the border and banding, formats the 'release_date'
* column, and autosizes the columns and rows.
*/
function formatDataset() {
// Get the active sheet and data range.
var sheet = SpreadsheetApp.getActiveSheet();
var fullDataRange = sheet.getDataRange();
// Apply row banding to the data, excluding the header
// row and column. Only apply the banding if the range
// doesn't already have banding set.
var noHeadersRange = fullDataRange.offset(
1, 1,
fullDataRange.getNumRows() - 1,
fullDataRange.getNumColumns() - 1);
if (! noHeadersRange.getBandings()[0]) {
// The range doesn't already have banding, so it's
// safe to apply it.
noHeadersRange.applyRowBanding(
SpreadsheetApp.BandingTheme.LIGHT_GREY,
false, false);
}
// Call a helper function to apply date formatting
// to the column labeled 'release_date'.
formatDates_( columnIndexOf_('release_date') );
// Set a border around all the data, and resize the
// columns and rows to fit.
fullDataRange.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
sheet.autoResizeRows(1, fullDataRange.getNumRows());
}
- अपने स्क्रिप्ट प्रोजेक्ट के आखिर में,
formatDataset()फ़ंक्शन के बाद, यह हेल्पर फ़ंक्शन जोड़ें:
/**
* Helper method that applies a
* "Month Day, Year (Day of Week)" date format to the
* indicated column in the active sheet.
*
* @param {number} colIndex The index of the column
* to format.
*/
function formatDates_(colIndex) {
// Exit if the given column index is -1, indicating
// the column to format isn't present in the sheet.
if (colIndex < 0)
return;
// Set the date format for the date column, excluding
// the header row.
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
.setNumberFormat("mmmm dd, yyyy (dddd)");
}
- अपना स्क्रिप्ट प्रोजेक्ट सेव करें.
कोड की समीक्षा
आइए, इन दोनों फ़ंक्शन में से हर एक में कोड की अलग से समीक्षा करें:
formatDataset()
यह फ़ंक्शन, पिछले फ़ॉर्मेट फ़ंक्शन के समान पैटर्न का पालन करता है जिसे आपने पहले ही लागू किया है. सबसे पहले, इसमें चालू शीट (शीट) और डेटा रेंज (fullDataRange) के रेफ़रंस रखने के लिए वैरिएबल मिलते हैं.
इसके बाद, एक ऐसी रेंज (noHeadersRange) बनाने के लिए Range.offset(rowOffset, columnOffset, numRows, numColumns) तरीके का इस्तेमाल होता है जो शीट के सभी डेटा को कवर करती है. इसमें कॉलम और लाइन हेडर शामिल नहीं होते. फिर कोड इस बात की पुष्टि करता है कि क्या इस नई रेंज में मौजूदा बैंडिंग मौजूद है (Range.getBandings() का इस्तेमाल करके). यह ज़रूरी है, क्योंकि अगर आप नई बैंडिंग इस्तेमाल करते हैं, तो Apps Script गड़बड़ी दिखाता है. अगर बैंडिंग मौजूद नहीं है ##39; फ़ंक्शन, Range.applyRowBanding(bandingTheme, showHeader, showFooter) का इस्तेमाल करके एक हल्के स्लेटी बैंडिंग जोड़ता है. ऐसा न करने पर, फ़ंक्शन आगे बढ़ जाता है.
अगले चरण में formatDates_(colIndex) हेल्पर फ़ंक्शन को कॉल करने के लिए, कॉलम में तारीखों को फ़ॉर्मैट करेंrelease_date' (नीचे बताया गया है). आपने columnIndexOf_(colName) जिस हेल्पर फ़ंक्शन को पहले लागू किया था, उसका इस्तेमाल करके कॉलम के बारे में बताया गया है.
आखिर में, फ़ॉर्मैटिंग एक अन्य बॉर्डर (पहले की तरह) जोड़कर खत्म की जाती है. साथ ही, Sheet.autoResizeColumns(columnPosition) और Sheet.autoResizeColumns(columnPosition) तरीकों का इस्तेमाल करके, हर कॉलम और पंक्ति में मौजूद डेटा का साइज़ अपने-आप बदल जाता है.
formatDates_(colIndex)
यह हेल्पर फ़ंक्शन, दिए गए कॉलम इंडेक्स का इस्तेमाल करके किसी खास कॉलम फ़ॉर्मैट पर तारीख का खास फ़ॉर्मैट लागू करता है. खास तौर पर, यह तारीख के मानों को & तारीख, महीने, साल (हफ़्ते का दिन) और कोट के तौर पर फ़ॉर्मैट करता है.
सबसे पहले, फ़ंक्शन दिए गए कॉलम इंडेक्स की पुष्टि करता है (यानी, 0 या उससे ज़्यादा). अगर नहीं, तो कुछ किए बिना वापस लौट जाता है. इस जांच से, उन गड़बड़ियों को रोका जा सकता है जो हो सकती हैं. उदाहरण के लिए, शीट में #release_date' कॉलम न हो.
कॉलम इंडेक्स की पुष्टि होने के बाद, फ़ंक्शन को उस कॉलम में शामिल रेंज मिलती है (हेडर लाइन को छोड़कर) और फ़ॉर्मैटिंग लागू करने के लिए Range.setNumberFormat(numberFormat) का इस्तेमाल करता है.
नतीजे
आप यह तरीका अपनाकर, अपने फ़ंक्शन में बदलाव कर सकते हैं:
- अगर आपने पहले से ##39 नहीं बनाए हैं, तो अपने स्क्रिप्ट प्रोजेक्ट को Apps Script एडिटर में सेव करें.
- क्विक फ़ॉर्मैट > डेटासेट फ़ॉर्मैट करें मेन्यू आइटम पर क्लिक करें.
नतीजे कुछ इस तरह दिखेंगे:
आपने अभी तक एक और फ़ॉर्मैट टास्क नहीं किया है. अब आपके पास फ़ॉर्मैट करने के ये निर्देश उपलब्ध हैं, इसलिए इन पर लागू करने के लिए ज़्यादा डेटा जोड़ें.
7. एपीआई डेटा को फ़ेच और फ़ॉर्मैट करना
इस कोडलैब में अब तक आपने देखा है कि अपनी स्प्रेडशीट को फ़ॉर्मैट करने के लिए, अन्य विकल्प के तौर पर Apps Script का इस्तेमाल कैसे किया जा सकता है. इसके बाद, आप ऐसा कोड लिखेंगे जो किसी सार्वजनिक एपीआई से डेटा लाता हो, उसे आपकी स्प्रेडशीट में डालता हो, और उसे इस तरह फ़ॉर्मैट करता हो कि उसे पढ़ा जा सके.
पिछले कोडलैब में, आपने एपीआई से डेटा पाने का तरीका सीखा. आप यहां भी इन ही तकनीकों का इस्तेमाल करेंगे. इस अभ्यास में, हम आपकी स्प्रेडशीट को पॉप्युलेट करने के लिए सार्वजनिक Star Wars API (SWAPI) का इस्तेमाल करेंगे. खास तौर पर, जब आप एपीआई इस्तेमाल करते हैं, तो आपको इन तीन अहम किरदारों के बारे में जानकारी मिलती है.
आपका कोड, एपीआई को कॉल करेगा, ताकि बड़ी संख्या में JSON डेटा मिल सके. साथ ही, रिस्पॉन्स को पार्स करें और डेटा को नई शीट में डालें. इसके बाद, शीट को फ़ॉर्मैट करें.
लागू करना
इस सेक्शन में, आप कुछ और मेन्यू आइटम जोड़ेंगे. हर मेन्यू आइटम, एक रैपर स्क्रिप्ट को कॉल करता है, जो मुख्य फ़ंक्शन (createResourceSheet_()) से किसी खास आइटम को पास करता है. आप और #39;इस फ़ंक्शन को लागू करेंगे और तीन अन्य हेल्पर फ़ंक्शन को लागू करेंगे. पहले की तरह, हेल्पर फ़ंक्शन टास्क के लॉजिकल हिस्सों को अलग करने में मदद करते हैं और कोड को पढ़ने लायक बनाए रखने में मदद करते हैं.
ये कार्रवाइयां करें:
- Apps Script एडिटर में, स्क्रिप्ट प्रोजेक्ट में अपने
onOpen()फ़ंक्शन को अपडेट करके, इनका मिलान करें:
/**
* A special function that runs when the spreadsheet is opened
* or reloaded, used to add a custom menu to the spreadsheet.
*/
function onOpen() {
// Get the Ui object.
var ui = SpreadsheetApp.getUi();
// Create and add a named menu and its items to the menu bar.
ui.createMenu('Quick formats')
.addItem('Format row header', 'formatRowHeader')
.addItem('Format column header', 'formatColumnHeader')
.addItem('Format dataset', 'formatDataset')
.addSeparator()
.addSubMenu(ui.createMenu('Create character sheet')
.addItem('Episode IV', 'createPeopleSheetIV')
.addItem('Episode V', 'createPeopleSheetV')
.addItem('Episode VI', 'createPeopleSheetVI')
)
.addToUi();
}
- अपना स्क्रिप्ट प्रोजेक्ट सेव करें.
- स्क्रिप्ट एडिटर में, फ़ंक्शन सूची में से
onOpenचुनें और चलाएं पर क्लिक करें. आपने जो नए विकल्प जोड़े हैं उनसे स्प्रेडशीट मेन्यू को फिर से बनाने के लिए,onOpen()चलता है. - Apps Script फ़ाइल बनाने के लिए, Files फ़ाइल जोड़ें
> Script पर क्लिक करें. - नई स्क्रिप्ट नाम दर्ज करें;एपीआई&कोटेशन; और Enter दबाएं. (Apps स्क्रिप्ट, स्क्रिप्ट फ़ाइल नाम में अपने आप
.gsएक्सटेंशन जोड़ देता है.) - नई API.gs फ़ाइल में मौजूद कोड को नीचे दिए गए कोड से बदलें:
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode IV.
*/
function createPeopleSheetIV() {
createResourceSheet_('characters', 1, "IV");
}
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode V.
*/
function createPeopleSheetV() {
createResourceSheet_('characters', 2, "V");
}
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode VI.
*/
function createPeopleSheetVI() {
createResourceSheet_('characters', 3, "VI");
}
/**
* Creates a formatted sheet filled with user-specified
* information from the Star Wars API. If the sheet with
* this data exists, the sheet is overwritten with the API
* information.
*
* @param {string} resourceType The type of resource.
* @param {number} idNumber The identification number of the film.
* @param {number} episodeNumber The Star Wars film episode number.
* This is only used in the sheet name.
*/
function createResourceSheet_(
resourceType, idNumber, episodeNumber) {
// Fetch the basic film data from the API.
var filmData = fetchApiResourceObject_(
"https://swapi.dev/api/films/" + idNumber);
// Extract the API URLs for each resource so the code can
// call the API to get more data about each individually.
var resourceUrls = filmData[resourceType];
// Fetch each resource from the API individually and push
// them into a new object list.
var resourceDataList = [];
for(var i = 0; i < resourceUrls.length; i++){
resourceDataList.push(
fetchApiResourceObject_(resourceUrls[i])
);
}
// Get the keys used to reference each part of data within
// the resources. The keys are assumed to be identical for
// each object since they're all the same resource type.
var resourceObjectKeys = Object.keys(resourceDataList[0]);
// Create the sheet with the appropriate name. It
// automatically becomes the active sheet when it's created.
var resourceSheet = createNewSheet_(
"Episode " + episodeNumber + " " + resourceType);
// Add the API data to the new sheet, using each object
// key as a column header.
fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
// Format the new sheet using the same styles the
// 'Quick Formats' menu items apply. These methods all
// act on the active sheet, which is the one just created.
formatRowHeader();
formatColumnHeader();
formatDataset();
}
- API.gs स्क्रिप्ट प्रोजेक्ट फ़ाइल के आखिर में, ये हेल्पर फ़ंक्शन जोड़ें:
/**
* Helper function that retrieves a JSON object containing a
* response from a public API.
*
* @param {string} url The URL of the API object being fetched.
* @return {object} resourceObject The JSON object fetched
* from the URL request to the API.
*/
function fetchApiResourceObject_(url) {
// Make request to API and get response.
var response =
UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
// Parse and return the response as a JSON object.
var json = response.getContentText();
var responseObject = JSON.parse(json);
return responseObject;
}
/**
* Helper function that creates a sheet or returns an existing
* sheet with the same name.
*
* @param {string} name The name of the sheet.
* @return {object} The created or existing sheet
* of the same name. This sheet becomes active.
*/
function createNewSheet_(name) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Returns an existing sheet if it has the specified
// name. Activates the sheet before returning.
var sheet = ss.getSheetByName(name);
if (sheet) {
return sheet.activate();
}
// Otherwise it makes a sheet, set its name, and returns it.
// New sheets created this way automatically become the active
// sheet.
sheet = ss.insertSheet(name);
return sheet;
}
/**
* Helper function that adds API data to the sheet.
* Each object key is used as a column header in the new sheet.
*
* @param {object} resourceSheet The sheet object being modified.
* @param {object} objectKeys The list of keys for the resources.
* @param {object} resourceDataList The list of API
* resource objects containing data to add to the sheet.
*/
function fillSheetWithData_(
resourceSheet, objectKeys, resourceDataList) {
// Set the dimensions of the data range being added to the sheet.
var numRows = resourceDataList.length;
var numColumns = objectKeys.length;
// Get the resource range and associated values array. Add an
// extra row for the column headers.
var resourceRange =
resourceSheet.getRange(1, 1, numRows + 1, numColumns);
var resourceValues = resourceRange.getValues();
// Loop over each key value and resource, extracting data to
// place in the 2D resourceValues array.
for (var column = 0; column < numColumns; column++) {
// Set the column header.
var columnHeader = objectKeys[column];
resourceValues[0][column] = columnHeader;
// Read and set each row in this column.
for (var row = 1; row < numRows + 1; row++) {
var resource = resourceDataList[row - 1];
var value = resource[columnHeader];
resourceValues[row][column] = value;
}
}
// Remove any existing data in the sheet and set the new values.
resourceSheet.clear()
resourceRange.setValues(resourceValues);
}
- अपना स्क्रिप्ट प्रोजेक्ट सेव करें.
कोड की समीक्षा
आपने अभी-अभी बहुत सारा कोड जोड़ा है. हर फ़ंक्शन पर अलग-अलग काम करके यह समझने का मौका मिलता है कि वे कैसे काम करते हैं:
onOpen()
यहां आपने #39;Quick formats मेन्यू में कुछ मेन्यू आइटम जोड़े हैं. आपने #33; सेपरेटर लाइन सेट की है. इसके बाद, तीन नए आइटम के साथ नेस्ट किया गया मेन्यू स्ट्रक्चर बनाने के लिए, Menu.addSubMenu(menu) का इस्तेमाल किया है. नए आइटम, Menu.addItem(caption, functionName) तरीके से जोड़े जाते हैं.
रैपर फ़ंक्शन
जोड़े गए मेन्यू आइटम में एक जैसा काम होता है: वे #W9 से लिए गए डेटा की मदद से शीट बनाने की कोशिश कर रहे हैं. अंतर सिर्फ़ इतना है कि हर कोई अलग फ़िल्म पर फ़ोकस कर रहा है.
शीट बनाने के लिए एक फ़ंक्शन लिखना सुविधाजनक होगा और यह तय करने के लिए कि किस फ़िल्म का उपयोग करना है, फ़ंक्शन को एक पैरामीटर स्वीकार करने के लिए कहें. हालांकि, Menu.addItem(caption, functionName) मेथड आपको मेन्यू से कॉल करने पर, पैरामीटर पास नहीं करने देता. इसलिए, एक ही कोड को तीन बार लिखने से कैसे बचें?
इसका जवाब wrapper फ़ंक्शन है. ये ऐसे आसान फ़ंक्शन होते हैं जिन्हें तुरंत कॉल किया जा सकता है. ये फ़ंक्शन खास पैरामीटर वाले सेट के साथ दूसरे फ़ंक्शन को कॉल करते हैं.
यहां, कोड तीन रैपर फ़ंक्शन का इस्तेमाल करता है: createPeopleSheetIV(), createPeopleSheetV(), और createPeopleSheetVI(). मेन्यू आइटम इन फ़ंक्शन से लिंक किए गए हैं. जब किसी मेन्यू आइटम पर क्लिक होता है, तो रैपर फ़ंक्शन एक्ज़ीक्यूट होता है और मुख्य आइटम बिल्डर फ़ंक्शन createResourceSheet_(resourceType, idNumber, episodeNumber) को तुरंत कॉल करता है. साथ ही, यह मेन्यू आइटम के लिए सही पैरामीटर के साथ पास होता है. इस मामले में, शीट बिल्डर फ़ंक्शन से स्टार वॉर फ़िल्म में से किसी एक की प्रमुख कैरेक्टर डेटा की शीट बनाने के लिए कहा जाता है.
createResourceSheet_(resourceType, idNumber, episodeNumber)
यह इस अभ्यास के लिए मुख्य शीट बिल्डर फ़ंक्शन है. हेल्पर फ़ंक्शन की मदद से, एपीआई डेटा को पार्स करता है, पार्स करता है, शीट को एपीआई डेटा पर लिखता है, और शीट को एपीआई डेटा में बदलता है. साथ ही, पिछले सेक्शन में बनाए गए फ़ंक्शन का इस्तेमाल करके शीट को फ़ॉर्मैट करता है. आइए, जानकारी की समीक्षा करें:
सबसे पहले, फ़ंक्शन बुनियादी फ़िल्म की जानकारी पाने के लिए एपीआई का अनुरोध करने के लिए fetchApiResourceObject_(url) का इस्तेमाल करता है. एपीआई रिस्पॉन्स में ऐसे यूआरएल का संग्रह होता है जिनका इस्तेमाल करके, फ़िल्मों से खास लोगों के बारे में ज़्यादा जानकारी हासिल की जा सकती है (जिन्हें संसाधन कहा जाता है). कोड इन सभी को resourceUrls कैटगरी में इकट्ठा करता है.
इसके बाद, कोड resourceUrls में हर संसाधन यूआरएल के लिए एपीआई को कॉल करने के लिए बार-बार fetchApiResourceObject_(url) का इस्तेमाल करता है. नतीजे, resourceDataList कैटगरी में सेव किए जाते हैं. इस कैटगरी का हर एलिमेंट, एक ऐसा ऑब्जेक्ट होता है जो फ़िल्म से अलग वर्ण के बारे में बताता है.
रिसॉर्स डेटा ऑब्जेक्ट में कई सामान्य कुंजियां होती हैं जो उस वर्ण से जुड़ी जानकारी को मैप करती हैं. उदाहरण के लिए, कुंजी name' फ़िल्म के किरदार के नाम से मैप होती है. हम मानते हैं कि हर रिसॉर्स डेटा ऑब्जेक्ट की कुंजियां एक जैसी हैं, क्योंकि वे #39;ऑब्जेक्ट ऑब्जेक्ट की सामान्य संरचना का इस्तेमाल करने के लिए हैं. बाद में कुंजियों की सूची की ज़रूरत होगी, इसलिए कोड, JavaScript Object.keys() तरीके का इस्तेमाल करके resourceObjectKeys में कुंजी सूची को स्टोर कर लेता है.
इसके बाद, निर्माता फ़ंक्शन createNewSheet_(name) शीट को बनाने के लिए हेल्पर फ़ंक्शन को कॉल करता है, जहां नया डेटा रखा जाएगा. हेल्पर फ़ंक्शन को कॉल करने से नई शीट भी चालू हो जाती है.
शीट बनाने के बाद, हेल्पर fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) फ़ंक्शन को शीट में सभी API डेटा जोड़ने के लिए कॉल किया जाता है.
आखिर में, पहले बनाए गए सभी फ़ॉर्मैटिंग फ़ंक्शन को नए डेटा पर एक जैसे फ़ॉर्मैटिंग नियम लागू करने के लिए कहा जाता है. नई शीट चालू है, इसलिए कोड बिना किसी बदलाव के इन फ़ंक्शन का फिर से इस्तेमाल कर सकता है.
fetchApiResourceObject_(url)
यह हेल्पर फ़ंक्शन, fetchBookData_(ISBN) कोडलैब पिछले डेटा के साथ काम करने में इस्तेमाल किए गए हेल्पर फ़ंक्शन के जैसा है. यह दिए गए यूआरएल को लेता है और जवाब पाने के लिए UrlFetchApp.fetch(url, params) तरीके का इस्तेमाल करता है. इसके बाद, HTTPResponse.getContextText() और JavaScript JSON.parse(json) मेथड का इस्तेमाल करके, रिस्पॉन्स को JSON ऑब्जेक्ट में पार्स किया जाता है. इसके बाद JSON ऑब्जेक्ट मिलता है.
createNewSheet_(name)
यह हेल्पर फ़ंक्शन काफ़ी आसान है. यह पहले पुष्टि करता है कि दिए गए नाम की शीट, स्प्रेडशीट में मौजूद है या नहीं. अगर ऐसा होता है, तो फ़ंक्शन शीट को चालू कर देता है और उसे दिखाता है.
अगर शीट मौजूद नहीं है ##39, तो फ़ंक्शन इसे Spreadsheet.insertSheet(sheetName) से बनाता है, इसे चालू करता है, और नई शीट को दिखाता है.
fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)
यह हेल्पर फ़ंक्शन, नई डेटा को API डेटा से भरने के लिए ज़िम्मेदार है. यह नई शीट, ऑब्जेक्ट कुंजियों की सूची, और पैरामीटर रिसॉर्स के तौर पर एपीआई रिसॉर्स ऑब्जेक्ट की सूची के तौर पर काम करता है. हर ऑब्जेक्ट कुंजी नई शीट में एक कॉलम को दिखाती है और हर रिसॉर्स ऑब्जेक्ट एक पंक्ति को दिखाता है.
सबसे पहले, फ़ंक्शन नए एपीआई डेटा को प्रज़ेंट करने के लिए, ज़रूरी पंक्तियों और कॉलम की संख्या का हिसाब लगाता है. यह क्रम में संसाधन और कुंजी की सूची का आकार है. इसके बाद, फ़ंक्शन एक आउटपुट रेंज (resourceRange) के बारे में बताता है, जहां डेटा रखा जाएगा. साथ ही, कॉलम हेडर को होल्ड करने के लिए एक अतिरिक्त लाइन जोड़ना भी ज़रूरी है. resourceValues वैरिएबल में, resourceRange से निकाली गई 2D वैल्यू की कैटगरी होती है.
इसके बाद फ़ंक्शन, objectKeys सूची में मौजूद हर ऑब्जेक्ट कुंजी को लूप में चलाता है. कुंजी को कॉलम हेडर के तौर पर सेट किया जाता है और फिर हर रिसॉर्स ऑब्जेक्ट से एक दूसरा लूप गुज़रता है. हर (पंक्ति, कॉलम) जोड़े के लिए, एपीआई से जुड़ी जानकारी resourceValues[row][column] एलिमेंट में कॉपी की जाती है.
resourceValues भरने के बाद, डेस्टिनेशन शीट को Sheet.clear() का इस्तेमाल करके मिटा दिया जाता है, क्योंकि उसमें पिछले मेन्यू आइटम के क्लिक का डेटा होता है. आखिर में, शीट में नई वैल्यू लिख ली जाती हैं.
नतीजे
आप ये काम करके अपने काम के नतीजे देख सकते हैं:
- अगर आपने पहले से ##39 नहीं बनाए हैं, तो अपने स्क्रिप्ट प्रोजेक्ट को Apps Script एडिटर में सेव करें.
- क्विक फ़ॉर्मैट > कैरेक्टर शीट बनाएं > एपिसोड IV मेन्यू आइटम पर क्लिक करें.
नतीजे कुछ इस तरह दिखेंगे:
आपने अब##39;कोड लिखा है, ताकि डेटा को Sheets में इंपोर्ट किया जा सके और उसे अपने-आप फ़ॉर्मैट किया जा सके.
8. नतीजा
इस कोडलैब को पूरा करने के लिए बधाई. आपने कुछ Apps Sheets फ़ॉर्मैट विकल्पों को देखा है जिन्हें आप अपने Apps Script प्रोजेक्ट में शामिल कर सकते हैं. साथ ही, आपने एक असरदार ऐप्लिकेशन बनाया है जो बड़े एपीआई डेटासेट को इंपोर्ट और फ़ॉर्मैट करता है.
क्या आपको यह कोडलैब मददगार लगा?
आपने क्या सीखा
- Apps Script के साथ, Sheets की फ़ॉर्मैटिंग की कई कार्रवाइयां लागू करने का तरीका.
onOpen()फ़ंक्शन के साथ सबमेन्यू बनाने का तरीका.- JSON ऑब्जेक्ट की फ़ेच की गई सूची को Apps Script के साथ डेटा की नई शीट में फ़ॉर्मैट करने का तरीका.
अगला कदम क्या है
इस प्लेलिस्ट के अगले कोडलैब में यह बताया गया है कि चार्ट में डेटा दिखाने और Google Slides प्रज़ेंटेशन में चार्ट एक्सपोर्ट करने के लिए, Apps Script का इस्तेमाल कैसे किया जाता है.
स्लाइड में चार्ट और प्रज़ेंट करने का डेटा पर अगला कोडलैब ढूंढें.