Google Sheets یک ابزار صفحه گسترده قدرتمند مبتنی بر ابر است که به شما امکان می دهد تقریباً هر کاری را که می توانید در Microsoft Excel انجام دهید انجام دهید . اما قدرت واقعی Google Sheets ویژگی Google Scripting است که با آن همراه است.

برنامه نویسی Google Apps^{(Google Apps)} یک ابزار برنامه نویسی پس زمینه است که نه تنها در Google Sheets^{(in Google Sheets)} بلکه در Google Docs، Gmail، Google Analytics و تقریباً هر سرویس ابری دیگر Google نیز کار می کند. ^(Google)این به شما امکان می دهد آن برنامه های جداگانه را خودکار کنید و هر یک از آن برنامه ها را با یکدیگر ادغام کنید.

در این مقاله می‌آموزید که چگونه با برنامه‌نویسی Google Apps شروع کنید ، یک اسکریپت پایه در Google Sheets برای خواندن و نوشتن داده‌های سلولی و مؤثرترین عملکردهای پیشرفته اسکریپت Google Sheets ایجاد کنید.^{(Google Sheets)}

نحوه ایجاد یک اسکریپت Google Apps^{(How to Create a Google Apps Script)}

می‌توانید همین الان شروع به ایجاد اولین اسکریپت Google Apps خود از داخل ^{(Google Apps)}Google Sheets کنید. 

برای انجام این کار، ابزارها^(Tools) را از منو انتخاب کنید و سپس Script Editor را انتخاب کنید.

این پنجره ویرایشگر اسکریپت را باز می کند و به طور پیش فرض تابعی به نام myfunction() را تنظیم می کند. اینجاست که می‌توانید اسکریپت Google^{(Google Script)} خود را ایجاد و آزمایش کنید .

برای ارائه یک عکس، سعی کنید یک تابع اسکریپت Google Sheets ایجاد کنید که داده‌ها را از یک سلول می‌خواند، محاسبه‌ای را روی آن انجام می‌دهد و مقدار داده را به سلول دیگر خروجی می‌دهد.

تابعی که برای دریافت داده از یک سلول، توابع 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 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 در هر ردیف کار می کند و ستون بر اساس ستونی که می خواهید داده ها را در آن بکشید ثابت می ماند.

در صفحه گسترده مثال ما، می توانید محاسبات را روی سه ردیف داده به صورت زیر انجام دهید.

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 آسان^(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^(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^(RSS) بیرون می کشد و هر خط را به عنوان، پیوند، تاریخ و توضیحات جدا می کند. 

با استفاده از تابع appendRow ، می توانید این موارد را در ستون های مناسب برای هر آیتم در فید RSS^(RSS) قرار دهید .

خروجی در برگه شما چیزی شبیه به این خواهد بود:

به جای جاسازی URL فید RSS در اسکریپت، می توانید یک فیلد در برگه خود با URL داشته باشید، و سپس چندین صفحه داشته باشید – یکی برای هر وب سایتی که می خواهید نظارت کنید.

رشته ها^{(Concatenate Strings)} را به هم متصل کنید و یک بازگشت کالسکه ^{(Carriage Return)}اضافه کنید^(Add)

می‌توانید با افزودن برخی از توابع دستکاری متن، صفحه‌گسترده RSS را یک قدم جلوتر ببرید و سپس از توابع ایمیل برای ارسال ایمیلی حاوی خلاصه‌ای از همه پست‌های جدید در فید RSS^(RSS) سایت برای خود استفاده کنید.

برای انجام این کار، در زیر اسکریپتی که در بخش قبل ایجاد کردید، می‌خواهید تعدادی اسکریپت اضافه کنید که تمام اطلاعات صفحه گسترده را استخراج می‌کند. 

شما می خواهید خط موضوع و متن ایمیل را با تجزیه همه اطلاعات از همان آرایه "اقلام" که برای نوشتن داده های RSS در صفحه گسترده استفاده کرده اید، بسازید. 

برای انجام این کار، موضوع و پیام را با قرار دادن خطوط زیر قبل از حلقه For "اقلام" مقداردهی اولیه کنید.

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

سپس، در انتهای حلقه for "اقلام" (درست بعد از تابع appendRow)، خط زیر را اضافه کنید.

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

نماد "+" هر چهار مورد را با هم ترکیب می کند و پس از آن " " برای بازگشت کالسکه بعد از هر خط. در پایان هر بلوک داده عنوان، دو بار برای یک متن ایمیل با فرمت زیبا می خواهید.

هنگامی که همه ردیف ها پردازش شدند، متغیر "body" کل رشته پیام ایمیل را نگه می دارد. اکنون شما آماده ارسال ایمیل هستید!

نحوه ارسال ایمیل در اسکریپت Google Apps^{(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^{(Google Sheet)} و ارسال آن به همراه پیوندهای URL برای خود ، پیگیری آخرین مطالب برای هر وب‌سایتی را بسیار راحت می‌کند.

این تنها نمونه‌ای از قدرتی است که در اسکریپت‌های Google Apps برای خودکار کردن اقدامات و ادغام چندین سرویس ابری وجود دارد.

5 Google Sheets Script Functions You Need to Know

Google Sheets is a рowerful cloud-based spreadsheet toоl that lets you do nearly eνerуthing yоu could do in Microsoft Excel. But thе real power of Google Sheets is 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 روش برای تبدیل Excel به Google Sheets

• Google Sheets در مقابل Microsoft Excel - چه تفاوت هایی دارند؟

• نحوه حذف حاشیه های جدول در Google Docs

• چگونه چت Google Docs به شما کمک می کند تا در اسناد همکاری کنید

• نحوه درج واترمارک در Word و Google Docs

• نحوه تعمیر یک ردیف در اکسل

• نحوه لایه بندی اشیاء در ارائه پاورپوینت

• نحوه استفاده از تجزیه و تحلیل What-If اکسل

• نحوه تنظیم رسیدهای خواندن در Outlook

• پیغام خطای «نمی توان مجوز تأیید» را در MS Office برطرف کنید

• 10 ابزار برای بازیابی رمز عبور گم شده یا فراموش شده Outlook PST

• 7 جایگزین برای پاورپوینت که می توانید به صورت آنلاین از آنها استفاده کنید

• 9 روش برای باز کردن یک سند Word بدون Word

• در Google Docs به جهت افقی تغییر دهید

• نحوه درج کاربرگ اکسل در Word Doc

• Microsoft Outlook باز نمی شود؟ 10 راه برای رفع

• نحوه گرفتن و درج اسکرین شات با استفاده از OneNote

• تیم های مایکروسافت چیست و چگونه کار می کند؟

• چگونه Outlook Stack On Loading Profile را رفع کنیم

• نحوه ایجاد و اجرای ماکرو در Word