コード.gs
function get_view_path() {
var ids = [];
var folder = DriveApp.getFolderById("FOLDER_ID");
var contents = folder.getFiles();
var view_path = "https://drive.google.com/file/d/";
while(contents.hasNext()) {
var file = contents.next();
var id = file.getId();
ids.push(view_path + id+ "/view");
}
Logger.log(ids);
}
|
コード.gsfunction doPost(e) {
console.log(e.postData.contents);
}
| 意訳受け取ったデータeを GCPのStackdriver Loggingに出す |
コード.gs
function split_text() {
var str = "take your time and take it easy.";
var pattern = /[々〆〇〻㐂-頻]+|[ぁ-ゟー]+|[゠-ヿ]+|[ｦ-ﾟ]+|[a-zA-Z0-9]+|[ａ-ｚＡ-Ｚ０-９]+/g;
var result = str.match(pattern);
var count_text = get_count_text3(result);
Logger.log(count_text);
}
function get_count_text1(array) {
var num = 0;
var word = [];
var count = [];
array = array.sort();
for (var i = 0; i < array.length; i++) {
if(i == 0){//配列内の最初の要素の場合
word.push(array[i]);
count.push(1);
num = i;
}else if(array[i] == array[(i-1)]) {//前の要素と同じ値の場合
count[num] = count[num] + 1;//カウントアップ
}else {//前の要素と違う場合
word.push(array[i]);
count.push(1);
num = num + 1;
}
}
var result = [word, count];
return result;
}
function get_count_text2(array) {
var num = 0;
var result = [];
array = array.sort();
for (var i = 0; i < array.length; i++) {
if(i == 0){
result.push([array[i], 1]);
num = i;
}else if(array[i] == array[(i-1)]) {
result[num][1] = result[num][1] + 1;
}else {
result.push([array[i], 1]);
num = num + 1;
}
}
return result;
}
function get_count_text3(array){
var sorted = array.sort();
var arrays = [];
var count;
var unique_i;
for(var i = 0; i < sorted.length; i++){
var obj = {}
var value = sorted[i];
if(value === sorted[i-1]){
count++;
arrays[unique_i]["count"] = count;
}else{
count = 1;
obj["text"] = value;
obj["count"] = count;
unique_i = arrays.length;
arrays.push(obj);
}
}
return arrays;
}
|
コード.gs
function split_text() {
var str = "遊んで食べて寝る";
var pattern = /[々〆〇〻㐂-頻]+|[ぁ-ゟー]+|[゠-ヿ]+|[ｦ-ﾟ]+|[a-zA-Z0-9]+|[ａ-ｚＡ-Ｚ０-９]+/g;
var result = str.match(pattern);
Logger.log(result);
}
|
意訳この機能がやること テキストを用意して どの単位で区切るか指定して 区切って ログに出す |
コード.gsfunction split_text() {
var str = "漢字ひらがなカタカナｶﾀｶﾅabc123ＡＢＣ１２３";
var pattern = /[々〆〇〻㐂-頻]+|[ぁ-ゟー]+|[゠-ヿ]+|[ｦ-ﾟ]+|[a-zA-Z0-9]+|[ａ-ｚＡ-Ｚ０-９]+/g;
var result = str.match(pattern);
Logger.log(result);
}
|
コード.gs
function doGet() {
return HtmlService.createHtmlOutputFromFile("index");
}
|
意訳この機能がやること 指定したHTMLファイルを表示する |
index.html
<!DOCTYPE html>
<html>
<body>
<button id="bt">bt</button>
<script>
bt.onclick = function() { alert("hello") };
//bt.onclick = () => { alert("hello") };
</script>
</body>
</html>
|
意訳ボタン ボタンをクリックしたらアラートを出す アロー関数で書くとこう |
コード.gs
function create_table(){
var resource = {
"kind": "fusiontables#table",
"name": "TABLE NAME",
"isExportable": false,
"columns": [
{
"kind": "fusiontables#column",
"name": "Text",
"type": "STRING"
},
{
"kind": "fusiontables#column",
"name": "Number",
"type": "NUMBER"
}
]
};
var table = FusionTables.Table.insert(resource);
var BASE_URL = "https://fusiontables.google.com/data?docid=";
Logger.log(BASE_URL + table.tableId);
}
|
Resource representations{
"kind": "fusiontables#column",
"columnId": integer,
"name": string,
"type": string,
"description": string,
"graphPredicate": string,
"formatPattern": string,
"validateData": boolean,
"validValues": [
string
],
"baseColumn": {
"tableIndex": integer,
"columnId": integer
},
"columnPropertiesJson": string,
"columnJsonSchema": string
}
|
コード.gsfunction create_table(){
var values = [
["ID", "NUMBER"],
["QUESTION", "STRING"],
["ANSWER", "STRING"],
["DATE", "STRING"]
];
var columns = [];
for(var i = 0; i < values.length; i++){
var obj = {};
obj["name"] = values[i][0];
obj["type"] = values[i][1];
obj["kind"] = "fusiontables#column";
columns.push(obj);
}
var resource = {
"kind": "fusiontables#table",
"name": "TABLE NAME",
"isExportable": false,
"columns": columns
};
var table = FusionTables.Table.insert(resource);
var BASE_URL = "https://fusiontables.google.com/data?docid=";
Logger.log(BASE_URL + table.tableId);
}
|
コード.gsfunction select_data(){
var sql = 'SELECT Text FROM ' + table_id + " WHERE Number = 1";
var result = FusionTables.Query.sqlGet(sql);
Logger.log(result);
}
|
OR is not supported. (If you are looking for OR, consider the IN operator. It will do the trick in certain queries).
コード.gsfunction update_table(){
var location = "むこう";
var date = "2018/07/17";
var query = "UPDATE " + table_id + " SET Location = '" + location + "', Date = '" + date + "' WHERE Number = " + 1;
FusionTables.Query.sql(query);
}
|
コード.gsfunction delete_row(){
var sql = "DELETE FROM " + table_id + " WHERE Text = ''";
var res = FusionTables.Query.sql(sql);
}
|
コード.gsfunction import_rows(){
var records =
"TWO, 2, ここ, 2018/07/18\n" +
"THREE, 3, そこ, 2018/07/19\n";
var blob = Utilities.newBlob(records, "application/octet-stream");
FusionTables.Table.importRows(table_id, blob);
}
|
コード.gsfunction import_rows2(){
var values = [["FOUR", 4, "あそこ", "2018/07/20"], ["FIVE", 5, "どこ", "2018/07/21"]];
var records = "";
for(var i = 0; i < values.length; i++){
records += values[i] + "\n"
}
var blob = Utilities.newBlob(records, "application/octet-stream");
FusionTables.Table.importRows(table_id, blob);
}
|
コード.gsfunction import_rows3(){
var ss_url = "対象のスプレッドシートURL";
var ss = SpreadsheetApp.openByUrl(ss_url);
var sh = ss.getSheets()[0];
var range = sh.getDataRange();
var values = range.getDisplayValues();
var records = "";
for(var i = 0; i < values.length; i++){
records += values[i] + "\n"
}
var rowsBlob = Utilities.newBlob(records, "application/octet-stream");
FusionTables.Table.importRows(table_id, rowsBlob);
}
|
コード.gsvar table_id = "TABLE_ID";
function get_sheet_by_url() {
var ss_url = "https://docs.google.com/spreadsheets/d/スプレッドシートIDedit#gid=シートID";
var sh = get_sheet(ss_url);
import_rows4(sh);
}
function get_sheet(ss_url) {
var ss = SpreadsheetApp.openByUrl(ss_url);
var sh_id = ss_url.split("gid=")[1];
var shs = ss.getSheets();
for (var i = 0; i < shs.length; i++) {
if (shs[i].getSheetId() == sh_id) {
break;
}
}
var sheet = ss.getSheets()[i];
return sheet;
}
function import_rows4(sh){
var range = sh.getDataRange();
var values = range.getDisplayValues();
var records = "";
for(var i = 0; i < values.length; i++){
var array = values_without_newline(values[i]);
records += array + "\n"
}
var rowsBlob = Utilities.newBlob(records, "application/octet-stream");
FusionTables.Table.importRows(table_id, rowsBlob);
}
function values_without_newline(values){
var array = [];
for(var i = 0; i < values.length; i++){
array.push(values[i].replace(/\r\n|\n|"|'|,/g, ""));
}
return array;
}
|
コード.gs
function insert_into(){
var table_id = "TABLE_ID";
var sql = "INSERT INTO " + table_id + " (Text, Number) VALUES ('ONE', 1);"
FusionTables.Query.sql(sql);
}
|
Note: To insert a large number of rows, use the import method instead, which will be faster and more reliable than using many SQL INSERT statements.
Stackdriver logs are attached to the GCP project associated with your Apps Script. You can view a simplified version of these logs in the Apps Script dashboard. To make full use of Stackdriver Logging and its capabilities, use a standard GCP project with your script project. This lets you access Stackdriver logs directly in the GCP Console and gives you more viewing and filtering options.
using_stackdriver_logging
コード.gs
function myFunction() {
console.log("出したいログ");
}
|
コード.gs
/************************************
ヘッダにしたい文字列
************************************/
var array = [
"TITLE",
"DETAIL",
"NAME",
"DATE"
];
/************************************
1行目に自動入力する
************************************/
function set_header(){
var sh = SpreadsheetApp.getActiveSheet();
var start_row = 1;
var start_col = 1;
var num_rows = 1;
var num_cols = array.length;
var range = sh.getRange(start_row, start_col, num_rows, num_cols);
range.setValues([array]);
}
|
コード.gs/************************************
オブジェクトに中身をシートに書き出す順番を指定
************************************/
var array = [
"TITLE",
"DETAIL",
"NAME",
"DATE"
];
/************************************
ヘッダーをD列以降に自動入力する
ss_url：入力したいスプレッドシートのURL
sh_id：その中のシートID
************************************/
function set_header(){
var ss_url = "対象のスプレッドシートのURL";
var ss = SpreadsheetApp.openByUrl(ss_url);
var sh_id = 0;
var sh = get_sheet(ss, sh_id);
var start_row = 1;
var start_col = 1;
var num_rows = 1;
var num_cols = array.length;
var range = sh.getRange(start_row, start_col, num_rows, num_cols);
range.setValues([array]);
}
/************************************
シートIDを受け取ってシートを見つけて返す
************************************/
function get_sheet(ss, sh_id) {
var shs = ss.getSheets();
for (var i = 0; i < shs.length; i++) {
if (shs[i].getSheetId() == sh_id) {
break;
}
}
var sh = ss.getSheets()[i];
return sh;
}
|
コード.gs
var obj = [
{"en": "Aries", "ja": "おひつじ座"},
{"en": "Taurus", "ja": "おうし座"},
{"en": "Gemini", "ja": "ふたご座"},
{"en": "Cancer", "ja": "かに座"},
{"en": "Leo", "ja": "しし座"},
{"en": "Virgo", "ja": "おとめ座"},
{"en": "Libra", "ja": "てんびん座"},
{"en": "Scorpius", "ja": "さそり座"},
{"en": "Sagittarius", "ja": "いて座"},
{"en": "Capriconus", "ja": "やぎ座"},
{"en": "Aquarius", "ja": "みずがめ座"},
{"en": "Pisces", "ja": "うお座"}
]
function filter_obj() {
var filtered = obj.filter(judge);
var result = get_result(filtered);
Logger.log(result);
}
function judge(items) {
if(items["en"] === "Libra"){
return true;
}else{
return false;
}
}
function get_result(filtered) {
var result = [];
for (var i = 0; i < filtered.length; i++) {
result.push([filtered[i]["en"], filtered[i]["ja"]]);
}
return result;
}
|
