Programming in Google Sheets. Google Apps script: getting data from Flurry
Good day, dear readers, miscreants, villains, well-wishers and other personalities. Today we're talking about Google Scripts, or rather scripts in tables as such.
I think that many of you know how to use Excel"I eat or, and some, maybe even .
Those who use the disk Google (Google Drive), probably already used Tables (Spreadsheets) and noticed that they are slightly inferior in functionality to Excel, but nevertheless it is still a powerful tool.
So, there were macros in Excel (sort of commands that simplify and automate calculations), written in a notorious language VBA (Visual Basic for Applications). In Tables Google There are also macros, which are called scripts and are written in the language Javascript. We will get to know them today.
I warn you in advance about possible complexity further example, because it is not so much educational as.. Um.. So to speak, the final fact that you can use and.. And develop if this is familiar to you.
Gather yourself into a ball of brain... And let's get started :)
Creating a Google Drive / Scripts table and filling it with content
Let's consider this simple problem:
We have two columns, in the first we write the names of the fruits, and in the second the color that corresponds to this fruit. And we want that when you enter a color in the color column, the color of the fruit name will automatically change.
If you have forgotten how to use documents at all Google, then you are welcome to read the corresponding one and already mentioned above. If you don’t need this at all, then there’s probably no point in reading further. Although, of course, who cares what :)
So, let's create new table Google, we call it, for example, " Fruits". Well, for example.. Considering that the example is about fruits, then.. Well, you understand :)
Now we add our fruits and colors to the first sheet:
Note! To count fruits, enter in the cell A1 formula:
Do you want to know and be able to do more yourself?
We offer you training in the following areas: computers, programs, administration, servers, networks, website building, SEO and more. Find out the details now!
="fruit("&COUNTA(A2:A)&")"
Now let's create a macro. To do this, go to the menu " Tools"and select" Script management". A pop-up menu will appear where we click on the button " Create".
In the window that appears, select " Empty project".
An editor will open, which at first glance (and even on the second) may cause stupor.
Actually, what's next? And then we start writing our own macros by hand (yes, all on your own). What will our macro look like? You need to draw up a diagram of this process (otherwise this process will take you a lot of time).
We need:
- Get the color values from the second column;
- In accordance with these values, set colors for the first column.
So.. Everything seems simple.. If you know how to do it, of course :)
wCodding
Let's move on to the code itself:
Function onOpen() ( var sheet = SpreadsheetApp.getActiveSpreadsheet(); var entries = [(name: "Color",functionName: "MakeMeHappy")]; sheet.addMenu("Scripts", entries); ); function MakeMeHappy())( var sheet = SpreadsheetApp.getActiveSpreadsheet(); var range = sheet.getActiveRange(); var data = range.getValues(); if(range.getColumn() == 2)( for (var i=0 ;i< data.length;i++){ range.offset(i,-1,1,1).clearFormat(); range.offset(i,-1,1,1).setHorizontalAlignment("center"); range.offset(i,-1,1,1).setVerticalAlignment("center"); switch (data[i]){ case "зеленый": case "Зеленый": range.offset(i,-1,1,1).setFontColor("#00dd00"); break; case "салатовый": case "Салатовый": range.offset(i,-1,1,1).setBackgroundColor("#87dd47"); break; case "желтый": case "Желтый": range.offset(i,-1,1,1).setBackgroundColor("#ffff00"); break; case "оранжевый": case "Оранжевый": range.offset(i,-1,1,1).setFontColor("#dd7711"); break; case "красный": case "Красный": range.offset(i,-1,1,1).setFontColor("#dd0000"); break; case "фиолетовый": case "Фиолетовый": range.offset(i,-1,1,1).setFontColor("#800080"); break; default: break; } } } };
Now I will try to explain it to you. Function onOpen adds menu " Scripts" to the table when opening it. And it looks like this:
Now for the code:
Var sheet = SpreadsheetApp.getActiveSpreadsheet();
This line adds to the variable sheet identifier of the document we opened, so that we can then use it to access the document.
Var entries = [(name: "Paint",functionName: "MakeMeHappy")]);
This array variable contains a list of menu names and functions that are executed when these menus are clicked.
Sheet.addMenu("Scripts", entries);
This method adds a menu to our document " Scripts".
Function MakeMeHappy, in fact, it will be ours main function, which colors fruits.
First I declare the variables:
Var sheet = SpreadsheetApp.getActiveSpreadsheet(); var range = sheet.getActiveRange(); var data = range.getValues();
Accordingly, in the variable sheet is the identifier of our document. In variable range the area we have selected is located (for example, cells B2:B6), in the variable data The values of these cells are found in the form of an array.
If(range.getColumn() == 2)(...)
In this condition, we check that the selected range of cells matches the second column (which contains the colors of the fruits).
For (var i=0;i< data.length;i++){...}
In this loop we go through each cell in the range B2:B
Range.offset(i,-1,1,1).clearFormat(); range.offset(i,-1,1,1).setHorizontalAlignment("center"); range.offset(i,-1,1,1).setVerticalAlignment("center");
These three properties remove cell formatting A[i](For example, A1, A2, A3 etc., because we are inside the loop), and also center the values in the cell vertically and horizontally.
Here it should be borne in mind that since our range matches second column ( B2:B), and we need to remove the formatting and center first column, then the method is used for this offset(range row number, column number, number of rows, number of columns). For example, the method range.offset(0,1,4,3) for cell B2(those. range corresponds B2:B2) will mean that we will not act on the cell B2:B2, and for the range [ B+ 1][ 2 + 0]:[ IN+ 3][ 2 + (4 -1)] = C2:E5. For more details, see.
Switch (data[i])( case "green": case "Green": range.offset(i,-1,1,1).setFontColor("#00dd00"); break; case "light green": case "Light green ": range.offset(i,-1,1,1).setBackgroundColor("#87dd47"); break; ... )
Function switch is the so-called switch. It looks at the value of a variable and, in accordance with what is stored in it, fulfills a certain condition" case". You can rewrite it in standard form if else. But it will be very inconvenient. For example:
Switch (c)( case 1: condition_1; break; case 2: condition_2; break; case 3: condition_3; break; default: condition_4; break; )
Will be equivalent to the function:
If (c == 1) condition_1; else if (c == 2) condition_2; else if (c == 3) condition_3; else condition_4;
Because you can enter a color with both a capital and a small letter, then we need two conditions each, which corresponds to the entry case "green": case "Green": action; break; (I have this written in a block structure). You need to keep in mind that after each action you need to write a function break; because otherwise we will fulfill all the conditions in order, and not the one we need. Condition default used when there is no suitable condition for our variable.
Range.offset(i,-1,1,1).setFontColor("#00dd00");
Methods setFontColor And setBackgroundColor set the text and background colors in the form #rrggbb (r-red, g-green, b-blue, color ranges) respectively.
Now let's check the function. Selecting a range B2:B9, go to the menu " Scripts" and select the option " Paint". Let's see how our fruits have acquired a life of color :)
In general, that's all. But not really.
Google Sheets Scripts and Macros, Add-on
But you can make a small addition so that you don’t have to run the functions manually every time.
To do this, go to the menu in the script editor " Resources" and select there " Current Project Triggers". A menu will open in which our function will already be onLoad. Add new feature (1 ) and set the name of the function (2 ) and the type of activation thereof (3 ). You can also click on " Notifications" and add/remove your mailing address from the notification list.
They will come if the script was executed with errors.
The final result of the action:
Continuations - -we are preparing three. Well, the comments certainly contain a lot of tasty things.
P.S. For the existence of this article, special thanks to a friend of the project and a member of our team under the nickname “barn4k“.
Google Apps Script is a language for automating work with online applications, which appeared in 2009. Its basis is classic JavaScript, enriched with extensions for working with Google services. After reading this article, you will master the basics of using this language, learn a couple of techniques for manipulating mail and documents, and also get an idea of the vast Google capabilities Apps Script.
Basics of use
It's very easy to start writing Google Apps scripts. The first step is to open the script editor in any application with which we will automate interaction. In Google Docs it is located in the menu "Tools -> Script Editor". Next, you need to select the project within which the script will be located (see Fig. 1). In the window that opens, write the code:
Function FirstExampleFunc() ( Browser.msgBox("This is JS!"); )
The declared function can be launched from “Tools -> Script Management” or done for it separate button. To do this, you need to write one more function:
Function menu() ( var ss = SpreadsheetApp.getActiveSpreadsheet(); var entries = [ (name: "My only function", functionName: "FirstExampleFunc"), ss.addMenu("My functions", entries); )
Now in our menu there is an item called ExampleFunc, when clicked on it, a one-line submenu “My only function” opens.
Declared functions can be used in formulas that are entered inside spreadsheet cells (see Fig. 3). Now let's move on to more practically useful examples.
WARNING
Before running the script, do not forget to do backup copy important information. Actions performed by GS cannot be undone by pressing
Working with Google Docs
When the task of automating work with office documents arises, the first thing that comes to mind is VBA, the mere mention of which has a total anti-ecstatic effect on many, evoking painful memories from the school and university past. Google Script is definitely more convenient and easier to understand. Especially for web developers, because this is native, familiar and beloved JS! Let's look at a couple of example scripts for Google Docs. The following code fills the top left cell of the first sheet of the active table:
Var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets(); sheet.getRange("A1").setValue("Cell Contents");
And this code creates a copy of the text document and puts it in a specific place:
Var source = DocsList.getFileById("SOURCE_ID"); var newFile = source.makeCopy("new filename"); var targetFolder = DocsList.getFolderById("ID of the folder in which the newly created file will be placed"); newFile.addToFolder(targetFolder);
And this is how you can replace lines in a text document:
Var doc = DocumentApp.openById("ID of the document being edited"); doc.editAsText().replaceText("old text", "new text"); Logger.log(doc.getText())
The following code example highlights specific words in text:
Var doc = DocumentApp.openById("document id"); var textToHighlight = "text to highlight"; var highlightStyle = (); highlightStyle = "#FF0000"; var paras = doc.getParagraphs(); var textLocation = (); for (i=0; i Details: If you are writing a script that includes text search/replace functions, remember that GS supports regular expressions. The letter is sent in one short line: MailApp.sendEmail(" [email protected]", "subject of the letter", "text of the letter") If you add a little more code to it, you can organize a mailing to a list of addresses from a spreadsheet (look for the source in the application): Var sheet = SpreadsheetApp.getActiveSheet(); var numRows = sheet.getLastRow(); var dataRange = sheet.getRange(1, 1, numRows, 2) var data = dataRange.getValues(); for (var i = 0; i< data.length; ++i)
{
var row = data[i];
var name = row;
var email = row;
var subject = "Тема письма";
var message = "Здравствуйте, " + name + "!";
MailApp.sendEmail(email, subject, message);
}
In addition to mailings, using GS you can perform automated processing of mailbox contents. An example is deleting all letters from the addressee with whom you are offended: Var threads = GmailApp.search(" [email protected]"); for(var i = 0; i< threads.length; i++)
{
var messages = threads[i].getMessages();
for(var j = 0; j < messages.length; j++)
{
messages[j].moveToTrash();
}
}
Keep in mind that Gmail not only protects against incoming spam, but also limits outgoing spam. More than 500 letters per day from using Google Apps Script will not work. Using Google Apps Script, you can translate text strings from one language to another. Example: Var word = LanguageApp.translate("cuckoo", "ru", "es"); Logger.log(word); // The script will output to the console "cuco" - cuckoo in Spanish Codes for languages can be viewed in the address bar of the Google Translate service. If you highlight the code and press Google Apps Script can work with user files hosted on Google Drive. This script displays the names of all user files to the console: Var files = DriveApp.getFiles(); while (files.hasNext()) ( var file = files.next(); Logger.log(file.getName()); ) Several dozen different methods can be applied to files. Here are some of them: The address book can also be subject to automated processing. The code below copies all contacts from the Editorial group to a Google Spread Sheet: Var group = ContactsApp.getContactGroup("Editorial"); var contacts = group.getContacts(); var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Editorial contacts"); for (var i in contacts) ( // Save contact data in cells: first name, last name, phone number sheet.getRange(i, 1, 1, 1).setValue(contacts[i].getGivenName()); sheet. getRange(i, 2, 1, 1).setValue(contacts[i].getFamilyName()); sheet.getRange(i, 3, 1, 1).setValue(contacts[i].getPhones()); // And there is also a method for getting the pager number (!) of a contact sheet.getRange(i, 4, 1, 1).setValue(contacts[i].getPager()); ) Using Google Apps Scripts, you can work with the Google Task service - create new tasks and parse existing ones. This code creates a new case in the list: // You can find the tasklist ID inside the address bar in the Google Task service var taskListId="tasklist id,"; var newTask = ( title: "Throw away the date pits", notes: "Don't forget the date pits under the bed" ); newTask = Tasks.Tasks.insert(newTask , taskListId); Logger.log("Task with ID "%s" created", newTask.id); And this way you can display a list of numbered tasks in the console: // Put all the tasks of the list into an array var tasks = Tasks.Tasks.list(taskListId); for (var i = 0; i< tasks.items.length; i++)
{
var task = tasks.items[i];
Logger.log(i. ". ",%s, task.title, task.id);
}
Tasks can be moved from one list to another using the move method, added to using the update method, and deleted using the delete method. You can also create events in the calendar automatically (and, just as in the case of mailing, generate information about them from table rows). Code to create the event: Var timeZone = CalendarApp.getTimeZone(); var description = Utilities.formatString("%s from %s to %s", "event title", dateString_("event start date", "time zone"), dateString_("event end date", "time zone") ); CalendarApp.createEventFromDescription(description); A gadget is a container application that is placed on a web page and performs certain functions. Examples: a mini-block in the corner of a page with a weather forecast or calendar. To place Google Script inside the gadget, you need to select “Publish -> Deploy as web app” in the script editor menu. And if you press You can configure a script to run after a specific event. For example, after opening/editing a spreadsheet or submitting form data. There is a service for working with databases for this purpose. Google Cloud SQL. Essentially - classic MySQL in the cloud. Can interact with Google Apps Script using the Java Database Connectivity standard. Here is an example of code that reads records from a table: Var conn = Jdbc.getConnection(dbUrl, user, userPwd); var start = new Date(); var stmt = conn.createStatement(); stmt.setMaxRows(1000); var results = stmt.executeQuery("SELECT * FROM entries"); var numCols = results.getMetaData().getColumnCount(); while (results.next()) ( var rowString = ""; for (var col = 0; col< numCols; col++)
{
rowString += results.getString(col + 1) + "\t";
}
Logger.log(rowString)
}
results.close();
stmt.close();
The cost of using the service is $88 per year for 10 GB free space. Unfortunately, Google Apps Script cannot work with other databases. If you plan to write a script that needs to interact with data, you shouldn’t immediately get upset or deplete your budget with donations to purchase ATVs for residents of Silicon Valley. There are two ways to get out of this situation: I think that not every one of our readers has had time to try all of Google’s online services. In order to broaden your horizons and stimulate your creative imagination, we present short review application capabilities that can be automated using Google Apps Script. According to the good old tradition, which is almost three months old, we have posted several sources on GitHub: Google has many APIs for developers to implement in programs written in Google Apps Script. To do this, you need to enable this feature in the script editor (in the Resources menu, then Advanced Google services). After this, you can use the capabilities of the following services: I'm a little confused about how new libraries should be published. I'm creating what I think is a very cool and useful library for Google Apps scripts. But I don't know where I can post this so others can enjoy and provide some feedback. I notice that I can publish my script as a template, but I believe this will allow others to import my script and edit as they wish, but if there are updates they will need to import my code again. the new library feature, on the other hand, requires users to know about your app ID in order to import it into their projects. To date, the only place where libraries are listed is "Notable Script Libraries" on the website Google developers. But this is only for hand-selected libraries from Google. So my questions are here: When should I publish my script as a template rather than publishing it as a library? They seem a bit overlapping to me. How do other people share their libraries? Is Google planning some kind of directory?INFO
Working with mail
WARNING
Working with Google Translate
INFO
Working with Google Drive
Working with Google Contacts
Working with Google Tasks
Working with the calendar
Script exchange forms
INFO
Events
Working with databases
Applications that Google Apps Script can interact with
SRC
Advanced Google Services