Google Sheets waa qalab faafin ku salaysan daruur oo awood badan kaas oo kuu ogolaanaya inaad ku samayso wax kasta oo aad ku samayn karto Microsoft Excel . Laakin awooda dhabta ah ee Google Sheets waa qaabka qoraalka Google^{(Google Scripting)} ee la socota.

Qorista Google Apps^{(Google Apps)} waa qalab qoraal ah oo aan ka shaqayn oo keliya Google Sheets^{(in Google Sheets)} , laakiin sidoo kale Google Docs, Gmail, Google Analytics , iyo ku dhawaad ​​adeeg kasta oo kale oo Google ah. Waxay kuu ogolaanaysaa inaad otomaatig u samayso abkaas gaarka ah, oo aad mid kasta oo ka mid ah abkaas isku xidho midba midka kale.

Maqaalkan waxaad ku baran doontaa sida loogu bilaabo Google Apps scripting, samaynta qoraal aasaasi ah oo Google -ka ah si aad u akhrido oo u qorto xogta unugga, iyo hawlaha qoraalka ^{(Google Sheets)}Google^{(Google Sheets)} ee horumarsan ee ugu waxtarka badan .

Sida loo Sameeyo Qoraalka Apps Google^{(How to Create a Google Apps Script)}

Waxaad hadda bilaabi kartaa abuurista qoraalka Google Apps ee ugu horreeya gudaha Google Sheets . 

Si tan loo sameeyo, ka dooro Tools ka menu, ka dibna Script Editor .

Tani waxay fureysaa daaqada tifaftiraha qoraalka waxayna kuxirantahay shaqada la yiraahdo myfunction() . Tani waa meesha aad ka abuuri karto oo aad tijaabin karto qoraalkaaga Google^{(Google Script)} .

Si aad u siiso tallaal, isku day inaad abuurto shaqo qoraal Google Sheets oo akhrin doonta xogta hal unug, ku samee xisaabin, oo u soo saari doonta cadadka xogta unug kale.

Shaqada laga helo xogta unuggu waa shaqooyinka getRange()^(getRange()) iyo getValue() . Waxaad ku garan kartaa unugga saf iyo tiir. Haddaba haddii aad qiima ku leedahay safka 2 iyo tiirka 1 (Tiirarka A), qaybta hore ee qoraalkaagu waxay u ekaan doontaa sidan:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();
}

Tani waxay ku kaydisaa qiimaha unuggaas doorsoomiyaha xogta . ^(data)Waxaad samayn kartaa xisaabinta xogta, ka dibna ku qor xogtaas unug kale. Markaa qaybta ugu dambeysa ee shaqadani waxay noqon doontaa:

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

Marka aad dhammayso qorista shaqadaada, dooro calaamadda saxanka si aad u kaydiso. 

Marka ugu horraysa ee aad samayso hawl cusub oo Google Sheets ah oo sidan oo kale ah (adoo dooranaysa astaanta run), waxaad u baahan doontaa inaad bixiso Oggolaanshaha^{(Authorization)} qoraalka si uu ugu shaqeeyo Koontadaada Google^{(Google Account)} .

Oggolow ogolaanshaha inay sii socdaan Marka qoraalkaagu socdo, waxaad arki doontaa in qoraalku u qoray natiijooyinka xisaabinta unugga la beegsanayo.

Hadda oo aad taqaanid sida loo qoro shaqada aasaasiga ah ee Google Apps , aynu eegno qaar ka mid ah hawlaha horumarsan.

Isticmaal getValues ​​si aad u soo dejiso qalabyada^{(Use getValues To Load Arrays)}

Waxaad ku qaadan kartaa fikradda xisaabinta xogta ku jirta xaashidaada warqad qoraal ah oo heer cusub ah adiga oo isticmaalaya arrays. Haddii aad ku shubato doorsoome qoraalka Google Apps adiga oo isticmaalaya getValues, doorsoomuhu waxa uu noqonayaa hannaan ka soo rari kara qiimayaal badan xaashida.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

Doorsoomka xogtu waa hannaan dhinacyo badan leh oo haysa dhammaan xogta xaashida. Si aad u samayso xisaabinta xogta, waxaad isticmaashaa loop^(for) . Miisaanka loop-ku wuxuu ka shaqayn doonaa saf kasta, tiirkuna wuxuu ahaanayaa mid joogto ah, oo ku salaysan tiirka meesha aad rabto inaad xogta jiiddo.

Tusaalahayaga xaashida faafinta, waxaad samayn kartaa xisaabinta saddexda saf ee xogta sida soo socota.

for (var i = 1; i < data.length; i++) {
   var result = data[i][0] * 100;
   sheet.getRange(i+1, 2).setValue(result); 
   }
}

Kaydi^(Save) oo u socodsii qoraalkan sida aad kor ku samaysay. Waxaad arki doontaa in dhammaan natiijooyinka ay ka buuxaan tiirka 2 ee xaashidaada.

Waxaad ogaan doontaa in tixraaca unugga iyo safka doorsoomayaasha array ay ka duwan yihiin shaqada getRange. 

xogta[i] [0]^(data[i][0]) waxa ay tilmaamaysaa cabbirada habaynta halka cabbirka koowaad uu yahay safka kan labaadna uu yahay tiirka. Labadani waxay ku bilowdaan eber.

getRange(i+1, 2) waxa ay tilmaamaysaa safka labaad marka i=1 (maadaama safka 1 uu yahay madaxa), iyo 2 waa tiirka labaad ee natiijada lagu kaydiyo.

Isticmaal appendRow si aad u qorto natiijooyinka^{(Use appendRow To Write Results)}

Maxaa dhacaya haddii aad haysatid xaashida faafinta oo aad rabto inaad ku qorto xogta saf cusub halkii aad ka qori lahayd tiir cusub?

Tani way fududahay in lagu sameeyo shaqada appendRow . Shaqadani ma dhibayso wax xog ah oo xaashida ku jirta. Waxay kaliya ku dhejin doontaa saf cusub xaashida hadda jirta.

Tusaale ahaan, samee shaqo tirin doonta 1 ilaa 10 oo tus xisaabiye leh 2 dhufasho oo ku jira tiirka counter .

Shaqadani waxay u ekaan doontaa sidan:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<11; i++) {
      var result = i * 2;
     sheet.appendRow([i,result]);
   }
}

Waa kuwan natiijooyinka marka aad hawshan waddo.

Ku socodsii quudinta RSS ee URLFetchApp^{(Process RSS Feeds With URLFetchApp)}

Waxaad isku dari kartaa shaqada qoraalka Google Sheets ee hore iyo URLFetchApp si aad feed RSS ka soo jiidato degel kasta, oo aad saf ugu qorto xaashida xaashida maqaal kasta oo dhawaan lagu daabacay mareegahaas.

Tani asal ahaan waa hab DIY ah si aad u abuurto xaashida akhristaha quudiyahaaga RSS !

Qoraalka si tan loo sameeyo isna aad uma dhib badna.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var item, date, title, link, desc; 
   var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
   var doc = Xml.parse(txt, false);  

   title = doc.getElement().getElement("channel").getElement("title").getText();
   var items = doc.getElement().getElement("channel").getElements("item");   

// Parsing single items in the RSS Feed

for (var i in items) {
   item  = items[i];
   title = item.getElement("title").getText();
   link  = item.getElement("link").getText();
   date  = item.getElement("pubDate").getText();
   desc  = item.getElement("description").getText();
   
   sheet.appendRow([title,link,date,desc]);
}
}

Sida aad arki karto, Xml.parse waxa^(Xml.parse) ay shay kasta ka soo saartaa quudka RSS oo u kala saarayaa sadar kasta ciwaanka, isku xidhka, taariikhda iyo sharaxaada. 

Adigoo isticmaalaya shaqada appendRow , waxaad walxahan gelin kartaa tiirar ku habboon shay kasta oo ku jira quudinta RSS .

Wax-soo-saarka xaashidaadu waxay u ekaan doontaa sidan:

Halkii aad ku dhejin lahayd URL -ka ^(URL)RSS -ka qoraalka, waxaad ku yeelan kartaa beer xaashida URL- ka ah , ka dibna aad haysato xaashiyo badan - mid ka mid ah shabakad kasta oo aad rabto inaad la socoto.

Xadhkaha isku xidha^{(Concatenate Strings)} oo ku dar^(Add) soo laabashada gaadhiga^{(Carriage Return)}

Waxaad qaadi kartaa tallaabo dheeraad ah xaashida RSS -ka adiga oo ku daraya qaar ka mid ah hawlaha wax-is-daba-marinta qoraalka ah, ka dibna adeegso hawlaha iimaylka si aad isugu dirto iimaylka oo kooban dhammaan qoraallada cusub ee bogga RSS feed.

Si tan loo sameeyo, hoosta qoraalka aad ku abuurtay qaybta hore, waxaad u baahan doontaa inaad ku darto qoraalo ka soo saari doona dhammaan macluumaadka ku jira xaashida xaashida. 

Waxaad u baahan doontaa inaad dhisto khadka mawduuca iyo qoraalka iimaylka adiga oo si wada jir ah u kala saaraya dhammaan macluumaadka laga soo qaatay isku diyaarsanaanta “alaabta” ee aad u isticmaashay inaad ku qorto xogta  RSS xaashida.^(RSS)

Si aad tan u samayso, billow mawduuca iyo fariinta adigoo saaraya sadarradan soo socda "alaabta" Loop.

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

Ka dib, dhamaadka "alaabta" loop (laakin ka dib shaqada appendRow), ku dar xariiqda soo socota.

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

Astaanta "+" waxay isku xiri doontaa dhammaan afarta shay oo ay ku xigto "" soo noqoshada qaadida ka dib sadar kasta. Dhammaadka xannibaadda xogta cinwaan kasta, waxaad u baahan doontaa laba soo celin oo gaadhi-qaad ah oo loogu talagalay jidhka iimaylka si fiican loo qaabeeyey.

Marka dhammaan safafka la habeeyo, doorsoomaha "jirka" wuxuu hayaa dhammaan xargaha fariinta iimaylka. Hadda waxaad diyaar u tahay inaad dirto iimaylka!

Sida loogu soo diro iimaylka qoraalka Apps Google^{(How To Send Email In Google Apps Script)}

Qaybta xigta ee Google Script waxay noqon doontaa inaad "mawduuca" iyo "jirka" ku dirto iimaylka. Ku samaynta tan Google Script waa mid aad u fudud.

var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

MailApp - ku waa fasal aad ugu habboon gudaha qoraallada Apps-ka Google^{(Google Apps)} kaas oo ku siinaya marin u helka adeegga iimaylka Koontadaada Google si aad u dirto ama u hesho iimaylo. Mahadsanid tan, xariiqda kaliya ee leh shaqada diritaankaEmailku waxay kuu ogolaanaysaa inaad dirto iimayl kasta^{(send any email)} oo wata ciwaanka iimaylka, khadka mawduuca, iyo qoraalka jidhka.

Tani waa waxa iimaylka ka soo baxay uu u ekaan doono. 

Isku-dubarid awoodda lagu soo saaro bogga RSS -ka ee bogga , ku kaydi xaashida Google^{(Google Sheet)} , oo u soo dir naftaada URL-yada^(URL) ku jira, waxay ka dhigaysaa mid aad u habboon in la raaco nuxurka ugu dambeeya ee shabakad kasta.

Kani waa hal tusaale oo kaliya ee awooda laga heli karo qoraalada Apps Google^{(Google Apps)} si loo toosiyo ficilada iyo isku dhafka adeegyada daruuraha badan.

5 Google Sheets Script Functions You Need to Know

Google Sheets is a powerful cloud-based spreadsheet tool that lets you do nearly everything yoυ could do in Microsoft Excel. Bυt the real power of Google Sheets iѕ the Google Scripting feature that comes with it.

Google Apps scripting is a background scripting tool that works not only in Google Sheets but also Google Docs, Gmail, Google Analytics, and nearly every other Google cloud service. It lets you automate those individual apps, and integrate each of those apps with each other.

In this article you’ll learn how to get started with Google Apps scripting, creating a basic script in Google Sheets to read and write cell data, and the most effective advanced Google Sheets script functions.

How to Create a Google Apps Script

You can get started right now creating your first Google Apps script from inside Google Sheets. 

To do this, select Tools from the menu, then Script Editor.

This opens the script editor window and defaults to a function called myfunction(). This is where you can create and test your Google Script.

To give it a shot, try creating a Google Sheets script function that will read data from one cell, perform a calculation on it, and output the data amount to another cell.

The function to get data from a cell is the getRange() and getValue() functions. You can identify the cell by row and column. So if you have a value in row 2 and column 1 (the A column), the first part of your script will look like this:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();
}

This stores the value from that cell in the data variable. You can perform a calculation on the data, and then write that data to another cell. So the last part of this function will be:

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

When you’re done writing your function, select the disk icon to save. 

The first time you run a new Google Sheets script function like this (by selecting the run icon), you’ll need to provide Authorization for the script to run on your Google Account.

Allow permissions to continue. Once your script runs, you’ll see that the script wrote the calculation results to the target cell.

Now that you know how to write a basic Google Apps script function, let’s take a look at some more advanced functions.

Use getValues To Load Arrays

You can take the concept of doing calculations on data in your spreadsheet with scripting to a new level by using arrays. If you load a variable in Google Apps script using getValues, the variable will be an array that can load multiple values from the sheet.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

The data variable is a multi-dimensional array that holds all of the data from the sheet. To perform a calculation on the data, you use a for loop. The counter of the for loop will work through each row, and the column remains constant, based on the column where you want to pull the data.

In our example spreadsheet, you can perform calculations on the three rows of data as follows.

for (var i = 1; i < data.length; i++) {
   var result = data[i][0] * 100;
   sheet.getRange(i+1, 2).setValue(result); 
   }
}

Save and run this script just as you did above. You’ll see that all of the results are filled into column 2 in your spreadsheet.

You’ll notice that referencing a cell and row in an array variable is different than with a getRange function. 

data[i][0] refers to the array dimensions where the first dimension is the row and the second is the column. Both of these start at zero.

getRange(i+1, 2) refers to the second row when i=1 (since row 1 is the header), and 2 is the second column where the results are stored.

Use appendRow To Write Results

What if you have a spreadsheet where you want to write data into a new row instead of a new column?

This is easy to do with the appendRow function. This function won’t bother any existing data in the sheet. It’ll just append a new row to the existing sheet.

As an example, make a function that will count from 1 to 10 and show a counter with multiples of 2 in a Counter column.

This function would look like this:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<11; i++) {
      var result = i * 2;
     sheet.appendRow([i,result]);
   }
}

Here are the results when you run this function.

Process RSS Feeds With URLFetchApp

You could combine the previous Google Sheets script function and the URLFetchApp to pull the RSS feed from any website, and write a row to a spreadsheet for every article recently published to that website.

This is basically a DIY method to create your own RSS feed reader spreadsheet!

The script to do this isn’t too complicated either.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var item, date, title, link, desc; 
   var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
   var doc = Xml.parse(txt, false);  

   title = doc.getElement().getElement("channel").getElement("title").getText();
   var items = doc.getElement().getElement("channel").getElements("item");   

// Parsing single items in the RSS Feed

for (var i in items) {
   item  = items[i];
   title = item.getElement("title").getText();
   link  = item.getElement("link").getText();
   date  = item.getElement("pubDate").getText();
   desc  = item.getElement("description").getText();
   
   sheet.appendRow([title,link,date,desc]);
}
}

As you can see, Xml.parse pulls each item out of the RSS feed and separates each line into the title, link, date and description. 

Using the appendRow function, you can put these items into appropriate columns for every single item in the RSS feed.

The output in your sheet will look something like this:

Instead of embedding the RSS feed URL into the script, you could have a field in your sheet with the URL, and then have multiple sheets – one for every website you want to monitor.

Concatenate Strings and Add a Carriage Return

You could take the RSS spreadsheet a step further by adding some text manipulation functions, and then use email functions to send yourself an email with a summary of all new posts in the site’s RSS feed.

To do this, under the script you created in the previous section, you’ll want to add some scripting that will extract all of the information in the spreadsheet. 

You’ll want to build the subject line and the email text body by parsing together all of the information from the same “items” array that you used to write the RSS data to the spreadsheet. 

To do this, initialize the subject and message by placing the following lines before the “items” For loop.

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

Then, at the end of the “items” for loop (right after the appendRow function), add the following line.

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

The “+” symbol  will concatenate all four items together followed by “\n” for a carriage return after each line. At the end of each title data block, you’ll want two carriage returns for a nicely formatted email body.

Once all rows are processed, the “body” variable holds the entire email message string. Now you’re ready to send the email!

How To Send Email In Google Apps Script

The next section of your Google Script will be to send the “subject” and the “body” via email. Doing this in Google Script is very easy.

var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

The MailApp is a very convenient class inside of Google Apps scripts that gives you access to your Google Account’s email service to send or receive emails. Thanks to this, the single line with the sendEmail function lets you send any email with just the email address, subject line, and body text.

This is what the resulting email will look like. 

Combining the ability to extract a website’s RSS feed, store it in a Google Sheet, and send it to yourself with URL links included, makes it very convenient to follow the latest content for any website.

This is just one example of the power that’s available in Google Apps scripts to automate actions and integrate multiple cloud services.

Related posts

• 4 siyaabood oo Excel loogu badalo Google Sheets

• Google Sheets vs Microsoft Excel - Waa maxay faraqa u dhexeeya?

• Sida Looga Saaro Xudduudaha Shaxda Google Docs

• Sida Google Docs Chat uu kuugu caawiyo inaad ka wada-shaqayso dukumentiyada

• Sida loo Geliyo Watermark gudaha Word iyo Google Docs

• 10 qalab oo lagu soo kabsado erayga lumay ama la illoobay ee PST Password

• Sida loo Beddelayo Cabbirka Islaaydhka ee PowerPoint si loo soo bandhigo Wanaagsan

• U samee qoraha OpenOffice muuqaal oo u shaqeeyo si ka badan sida Microsoft Word

• Hel Xogta Iimaylka Outlook adoo isticmaalaya StatView

• Sida Loo Geliyo GIF Animated gudaha PowerPoint

• Sida loo soo ceshado iimaylada la tirtiray ee Office 365

• Sida loogu daro Nambarada Slide Bandhiga PowerPoint

• U beddel hanuuninta muuqaalka muuqaalka Google Docs

• Sida loo Hagaajiyo #N/A Khaladaadka gudaha Foomamka Excel sida VLOOKUP

• Sida loo tirtiro bogga Microsoft Word

• Hagaaji MS Office "Ma Xaqiijin Karo Shatiga" Fariinta Khaladka

• Sida loo abuuro oo loo socodsiiyo Macro gudaha Word

• Sida loo isticmaalo Excel's What-If Analysis

• Sida loo lakabka walxaha ee bandhigga PowerPoint

• Sida loo hagaajiyo Bookmark aan lagu qeexin qalad gudaha Word