Google Sheets е мощен облачен инструмент за електронни таблици, който ви позволява да правите почти всичко, което можете да правите в Microsoft Excel . Но истинската сила на Google Sheets е функцията Google Scripting , която идва с него.

Скриптовете на Google Apps^{(Google Apps)} са инструмент за фонови скриптове, който работи не само в Google Таблици^{(in Google Sheets)} , но и в Google Документи, Gmail, Google Analytics и почти всяка друга облачна услуга на Google . Тя ви позволява да автоматизирате тези отделни приложения и да интегрирате всяко от тези приложения едно с друго.

В тази статия ще научите как да започнете със скриптовете на Google Apps , да създадете основен скрипт в Google Sheets за четене и запис на данни от клетките и най-ефективните разширени функции на скриптове на Google Sheets .

Как да създадете скрипт на Google Apps^{(How to Create a Google Apps Script)}

Можете да започнете да създавате първия си скрипт за Google Apps от ^{(Google Apps)}Google Sheets . 

За да направите това, изберете Инструменти^(Tools) от менюто, след това Редактор на скриптове^{(Script Editor)} .

Това отваря прозореца на редактора на скриптове и по подразбиране има функция, наречена myfunction() . Тук можете да създадете и тествате своя Google Script .

За да опитате, опитайте да създадете функция за скрипт на Google Sheets , която ще чете данни от една клетка, ще извърши изчисление върху нея и ще изведе количеството данни в друга клетка.

Функцията за получаване на данни от клетка е функциите getRange()^(getRange()) и getValue() . Можете да идентифицирате клетката по ред и колона. Така че, ако имате стойност в ред 2 и колона 1 (колоната A), първата част от вашия скрипт ще изглежда така:

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

Това съхранява стойността от тази клетка в променливата данни . ^(data)Можете да извършите изчисление върху данните и след това да запишете тези данни в друга клетка. Така че последната част от тази функция ще бъде:

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

Когато приключите с писането на вашата функция, изберете иконата на диска, за да запазите. 

Първият път, когато стартирате нова функция на скрипт на Google Таблици^{(Google Sheets)} като тази (като изберете иконата за изпълнение), ще трябва да предоставите оторизация^{(Authorization)} , за да може скриптът да се изпълнява във вашия акаунт в Google^{(Google Account)} .

Разрешаване на разрешенията да продължат. След като вашият скрипт се изпълни, ще видите, че скриптът е написал резултатите от изчисленията в целевата клетка.

Сега, когато знаете как да напишете основна функция за скрипт на Google Apps , нека да разгледаме някои по-разширени функции.

Използвайте getValues ​​за зареждане на масиви^{(Use getValues To Load Arrays)}

Можете да изведете концепцията за извършване на изчисления върху данни във вашата електронна таблица със скриптове на ново ниво, като използвате масиви. Ако заредите променлива в скрипта на Google Apps с помощта на getValues, променливата ще бъде масив, който може да зареди множество стойности от листа.

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

Променливата с данни е многоизмерен масив, който съдържа всички данни от листа. За да извършите изчисление на данните, използвате цикъл for . Броячът на цикъла for ще работи през всеки ред, а колоната остава постоянна, въз основа на колоната, в която искате да изтеглите данните.

В нашата примерна електронна таблица можете да извършвате изчисления върху трите реда данни, както следва.

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

Запазете^(Save) и стартирайте този скрипт точно както направихте по-горе. Ще видите, че всички резултати са попълнени в колона 2 във вашата електронна таблица.

Ще забележите, че препратката към клетка и ред в променлива на масив е различна от тази с функция getRange. 

data[i][0] се отнася до измеренията на масива, където първото измерение е редът, а второто е колоната. И двете започват от нула.

getRange(i+1, 2) се отнася до втория ред, когато i=1 (тъй като ред 1 е заглавието), а 2 е втората колона, където се съхраняват резултатите.

Използвайте appendRow за запис на резултати^{(Use appendRow To Write Results)}

Ами ако имате електронна таблица, в която искате да запишете данни в нов ред вместо в нова колона?

Това е лесно да се направи с функцията appendRow . Тази функция няма да пречи на съществуващите данни в листа. Той просто ще добави нов ред към съществуващия лист.

Като пример направете функция, която ще брои от 1 до 10 и ще показва брояч с кратни на 2 в колона Counter .

Тази функция ще изглежда така:

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

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

Ето резултатите, когато стартирате тази функция.

Обработвайте RSS емисии с URLFetchApp^{(Process RSS Feeds With URLFetchApp)}

Можете да комбинирате предишната скриптова функция на Google Sheets и ^{(Google Sheets)}URLFetchApp , за да изтеглите RSS емисия от всеки уебсайт и да напишете ред в електронна таблица за всяка статия, публикувана наскоро на този уебсайт.

Това е основно метод „ Направи^(DIY) си сам“ за създаване на своя собствена електронна таблица за четец на RSS емисии!^(RSS)

Скриптът за това също не е твърде сложен.

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]);
}
}

Както можете да видите, Xml.parse изтегля всеки елемент от RSS канала и разделя всеки ред в заглавие, връзка, дата и описание. 

Използвайки функцията appendRow , можете да поставите тези елементи в подходящи колони за всеки отделен елемент в RSS емисията.

Резултатът във вашия лист ще изглежда така:

Вместо да вграждате URL адреса на ^(URL)RSS емисия в скрипта, можете да имате поле във вашия лист с URL адреса^(URL) и след това да имате няколко листа – по един за всеки уебсайт, който искате да наблюдавате.

Конкатениране на низове^{(Concatenate Strings)} и добавяне^(Add) на връщане на карета^{(Carriage Return)}

Можете да направите RSS електронната таблица още една стъпка напред, като добавите някои функции за манипулиране на текст и след това да използвате функциите за имейл, за да си изпратите имейл с обобщение на всички нови публикации в RSS емисията на сайта.

За да направите това, под скрипта, който сте създали в предишния раздел, ще искате да добавите някакъв скрипт, който ще извлече цялата информация в електронната таблица. 

Ще искате да изградите темата и текста на имейла, като анализирате заедно цялата информация от същия масив „items“, който сте използвали, за да запишете RSS данните в електронната таблица. 

За да направите това, инициализирайте темата и съобщението, като поставите следните редове преди цикъла For.

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

След това, в края на "items" for цикъл (точно след функцията appendRow), добавете следния ред.

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

Символът „+“ ще обедини всичките четири елемента заедно, последван от „ ” за връщане на карета след всеки ред. В края на всеки блок с данни за заглавие ще искате две връщания на карета за добре форматирано тяло на имейла.

След като всички редове бъдат обработени, променливата „body“ съдържа целия низ от имейл съобщение. Сега сте готови да изпратите имейла!

Как да изпращате имейл в Google Apps Script^{(How To Send Email In Google Apps Script)}

Следващият раздел от вашия Google Script ще бъде да изпратите „темата“ и „тялото“ по имейл. Да направите това в Google Script е много лесно.

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

MailApp е много удобен клас в скриптовете на Google ^(MailApp)Apps^{(Google Apps)} , който ви дава достъп до имейл услугата на вашия акаунт в Google за изпращане или получаване на имейли. Благодарение на това единичният ред с функцията sendEmail ви позволява да изпращате всеки имейл^{(send any email)} само с имейл адреса, темата и основния текст.

Ето как ще изглежда получения имейл. 

Комбинирането на възможността за извличане на RSS^(RSS) емисия на уебсайт , съхраняване в Google Sheet и изпращане до себе си с включени URL връзки, прави много удобно да следите най-новото съдържание за всеки уебсайт.

Това е само един пример за силата, която е налична в скриптовете на Google Apps за автоматизиране на действията и интегриране на множество облачни услуги.

5 Google Sheets Script Functions You Need to Know

Google Ѕheetѕ is a powerful cloud-based spreadsheet tool that lets you dо nearly everything yоu could do in Microsoft Excel. But the real power of Goоgle Sheets is the Google Scripting feаture that cоmes 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 начина да конвертирате Excel в Google Sheets

• Google Sheets срещу Microsoft Excel – какви са разликите?

• Как да премахнете границите на таблицата в Google Docs

• Как Google Docs Chat ви помага да си сътрудничите по документи

• Как да вмъкнете воден знак в Word и Google Docs

• Как да правите и вмъквате екранни снимки с помощта на OneNote

• Как да поправите грешка в отметката не е дефинирана в Word

• Как да използвате анализа какво-ако на Excel

• Как да вмъкнете анимиран GIF в PowerPoint

• Как да добавя бележки под линия в Word

• Как да създадете VBA макрос или скрипт в Excel

• Как да коригирате Outlook, блокиран при зареждане на профил

• Как да възстановите изтрити имейли в Office 365

• Как да сортирате по дата в Excel

• Как да изтриете страница в Microsoft Word

• Как да създадете и стартирате макрос в Word

• Microsoft Outlook няма да се отвори? 10 начина за поправяне

• Как да изчистите кеша на Outlook

• Как да добавите номера на слайдове към презентация на PowerPoint

• Направете OpenOffice Writer да изглежда и функционира повече като Microsoft Word