I took the first step in work automation with "GAS" which can process various data of Google Drive automatically

Script execution environment provided by Google "Google Apps Script(GAS hereafter), you can easily execute JavaScript. I tried to manipulate the Google spreadsheet as a trial this time because I can easily operate various applications provided by Google such as Google Docs and Gmail.

Apps Script | Google Developers
https://developers.google.com/apps-script/

First, we will create the original data. Click New in the upper left corner of your Google Drive screen and select Google Spreadsheet.


This time, we prepared "test data" that there is a monthly sheet such as "201708" or "201709", and the total amount is described in the "C2" cell of that sheet.


Similarly, "Test data part 2" was prepared. I will write a script to combine the total amounts of "test data" and "test data 2" on one sheet.


In order to create a script file, add the "Google Apps Script" application. Go back to the Google Drive screen, select "Other" from "New" and click "Add Application".


Enter "google apps script" in the search field at the top right to display "Google Apps Script". Click "+ Connect" on the right.


"Google Apps Script is connected to Google Drive" will be displayed, click "OK" to close.


Again select "Other" from "New". Since "Google Apps Script" is newly added, it clicks.


For clarity, I changed the name to "total script".


I will examine how to write a script.Google Apps Script reference pageOpen the Sheets icon.


As you scroll through the list of classes, it says in the explanation that "This class allows users to open Google Sheets files and to create new ones. (A class for opening and creating Google Sheets files)" A class was found. Click "SpreadsheetApp" to check details.


Looking at the method list, I found "openByUrl (url)", a method with a name that seems to open a spreadsheet when I enter the URL. Click to confirm details.


How to use was described. I will write this example as a model.


Return to the code screen,

[code] var testdata = SpreadsheetApp.o[/code]

Since candidates come out when you enter up to, select "openByUrl" with the cursor key and press the enter key.


Open the test data you just created and copy and paste the URL. Put the URL between "'" as a model.


Since I want to log to see if it works well,

[code] Logger.log(testdata);[/code]

Insert below and click the execute button.


A dialog saying "Approval is necessary" appears, so click "Confirm permission".


Select an account ......


If you click "Allow" to allow access to the spreadsheet sheet, the script will be executed.


Click "Log" on the "Display" tab or press "Ctrl + Enter" to check the log.


It is OK if the object contained in the variable "testdata" is Spreadsheet.


We will also write the "Test Data Part 2" file in the same way.


As returned to the log, the return value of "SpreadsheetApp.openByUrl ()" is "Spreadsheet" object. Click "Spreadsheet" from the left menu bar to see details.


There is a method called "getSheetByName ()" in the method of the "Spreadsheet" class and it seemed convenient to open the sheet with the name, so I tried using it.


For the argument, set the sheet name "201709" ". Like a sample, I enclose it with "" (double quotation). Set Logger.log (testdatasheet) which is a function for outputting the log as before, and click the execute button.


Press "Ctrl + Enter" to display the log screen. Successful if the variable "testdatasheet" contains a Sheet object.


There is a method called "getSheetValues ​​()" in the method of the "Sheet" class that seems to get values ​​from the spreadsheet, so use this.


The cell you want to read this time is "C2".


Set "2, 3, 1, 1" so that it means "Acquire 2nd row, 3rd row to 1st row and 1st column" as an argument. Set the log output and click the execute button.


Move to the log screen with "Ctrl + Enter" and confirm that it is readable. Here, the number is not just "8321.0" but "[[8321.0]]"The double parenthesized form indicates that this number is an element of a two-dimensional array. However, since JavaScript automatically reads element [[8321.0]] of two-dimensional array to numerical value "8321.0", we will ignore the difference here.


Next I will set up to write the read data. I created an empty sheet named "Summary Result".


After preparing the spreadsheet for writing, we will make it possible to operate from the script in the same way as when loading it. I used the part I used to read "test data".


According to the reference, it is impossible to write directly from the object of the "sheet" class, and it uses the "Range" class. Looking for a way to acquire the "Range" object, we used this as a method of the "sheet" class with the name "getRange ()" as it is a straightball name. I will refer to examples of use.


Set the argument to "1, 1, 2, 2" because it is desired to write from the upper left, that is, from the cell of the first row and the first column to the second row and the second column. Click on the execute button ......


I got an error. Since it is "line 17", check line 17.


According to the error sentence, "null" seems to be included in the variable "matomesheet".


When checking the line declaring "matomesheet", the notation "matome.getSheetByName (" 201709 ")". The fact that this result is "null" means that there is no sheet named "201709" in the "matome" object.


When checking the "total result" spreadsheet, the seat name was "Sheet 1". Change the sheet name to "201709" and try running the script again.


When you press "Ctrl + Enter" to check the log, the "Range" object was assigned properly.


When we are looking for methods of "Range" class for writing, we found "setValues ​​()". Since it is input to a two-dimensional cell, it is necessary to prepare a two-dimensional array as an argument.


Prepare an array "values" for input and execute it.


We were able to read each value from "test data" "test data part 2" and write it to another spreadsheet.


We were able to manipulate spreadsheet data using GAS. As JavaScript is also not necessary to prepare the execution environment, it is a perfect service to take the first step to start automating work.