LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Sunday, February 28, 2021

Google Apps Scriptでトリガーを作成したい(1分後に実行して自動で削除する after(1000 * 60))


timeBased().after()を使ってみる。



コード.gs
function setTrigger() {
  const trigger = ScriptApp.newTrigger('myFunction')
    .timeBased()
    .after(1000 * 60)// 1分後にmyFunctinを実行する
    .create();
}

function myFunction() {
  console.log("myFunctionが実行されました。");
  deleteTrigger("myFunction");// トリガーを削除する
}

function deleteTrigger(functionName) {
  const allTriggers = ScriptApp.getProjectTriggers();
  for (let i = 0; i < allTriggers.length; i++) {
    if (allTriggers[i].getHandlerFunction() == functionName) {
      ScriptApp.deleteTrigger(allTriggers[i]);
      console.log("deleteTriggerを実行して、myFunctionのトリガーを削除しました。");
      break;
    }
  }
}




setTriggerを実行すると


myFunctionが登録されます。
このトリガーが実行されるのは
after(1000 * 60)
で設定したミリ秒後です。


そして
myFunctionが実行されると
deleteTrigger("myFunction");
が実行されて
トリガーから削除されます。





Wednesday, February 24, 2021

VLOOKUP関数の代わりにQUERY関数を使ってみる


Googleスプレッドシートにはクエリ言語を使用して検索できるQUERY関数があります。

本投稿では、普段VLOOKUP関数でやっていることを
QUERY関数でもできそうだったので試してみました。



今回書いた関数
=QUERY(A:B,"select B where A = '"&D2&"' limit 1")



こういうことがしたい

E2に検索結果を返す
  1. D2の値に一致するものを
  2. A列の中から探して
  3. B列の値を返す
  4. 複数結果がある場合は最初に見つかったものだけ返す

今回書いた関数
=QUERY(A:B,"select B where A = '"&D2&"' limit 1")

関数の意訳
QUERY:クエリ言語を使用して、データ全体に対するクエリを実行
A:B:検索範囲
select B:B列の値を返す
where A:A列内で検索する
D2:D2の値に一致するものを探す
limit 1:最初に見つかった結果を返す


複数結果を返したい場合
左側を検索したい場合


参考

VLOOKUP関数では左側を検索できないのでQUERY関数でやってみる


Googleスプレッドシートでは

VLOOKUP関数の範囲指定は
検索キーの右側で
左側にあるデータは参照できない(と思います)

QUERY関数を使うと
左右どちらでも参照できるようになるため
今回その関数を書いてみました。



今回書いた関数
=QUERY(A:B,"select A where B = '"&D2&"' limit 1")



こういうことがしたい

E2に検索結果を返す
  1. D2の値に一致するものを
  2. B列の中から探して
  3. A列の値を返す
  4. 複数結果がある場合は最初に見つかったものだけ返す

今回書いた関数
=QUERY(A:B,"select A where B = '"&D2&"' limit 1")

関数の意訳
QUERY:クエリ言語を使用して、データ全体に対するクエリを実行
A:B:検索範囲
select A:A列の値を返す
where B:B列内で検索する
D2:D2の値に一致するものを探す
limit 1:最初に見つかった結果を返す


複数結果を返したい場合

参考

VLOOKUP関数だけではできない複数結果の取得をQUERY関数でやってみる


Goolgeスプレッドシートでは

一致するキーが複数ある場合、VLOOKUP関数では最初に見つかった結果を返します。

本投稿では一致するすべての結果を返したくて、QUERY関数を使ってみました。



課題
VLOOKUP関数では一致する最初に見つかった結果だけを返す

解決策
QUERY関数で一致するすべての結果を返す



こういうことがしたい

E2に検索結果を返す
  1. D2の値に一致するものを
  2. B列の中から探して
  3. A列の値を返す
  4. 複数結果がある場合は改行を入れる

今回書いた関数
=JOIN(char(10),QUERY(A:B,"select A where B = '"&D2&"'"))


関数の意訳
JOIN:指定した区切り文字を使用して1次元配列の要素を結合
char(10):セルの中で改行する(今回はこれを区切り文字にする)
QUERY:クエリ言語を使用して、データ全体に対するクエリを実行
A:B:検索範囲
select A:A列の値を返す
where B:B列内で検索する
D2:D2の値に一致するものを探す


参考


JOIN

Merge cells with Join formula and CHAR(10)

QUERY syntax using cell reference

Sunday, February 21, 2021

Googleドライブのファイルやフォルダのショートカットを作りたい createShortcut(targetId)


createShortcut(targetId)というメソッドができていたので試してみました。


以下のメソッドは廃止されたようです。
 addFile(child) 
 addFolder(child) 
 removeFile(child)
 removeFolder(child) 


Deprecated methods
https://developers.google.com/apps-script/reference/drive/folder#deprecated-methods_1


ファイルのショートカットを作りたい

コード.gs
function createShortCut() {
  const fileId = "このファイルIDのショートカットを作る";
  const targetFolderId = "ショートカットを置くフォルダのID";
  
  DriveApp.getFolderById(targetFolderId).createShortcut(fileId);
}


フォルダのショートカットを作りたい

コード2.gs
function createShortCut() {
  const folderId = "このフォルダIDのショートカットを作る";
  const parentFolderId = "ショートカットを置くフォルダのID";

  DriveApp.getFolderById(parentFolderId).createShortcut(folderId);
}


参考

createShortcut(targetId) 

GoogleドライブのファイルやフォルダをmoveTo(destination)で移動したい


moveTo(destination)というメソッドができていたので試してみました。

以下のメソッドは廃止されたようです。
 addFile(child) 
 addFolder(child) 
 removeFile(child)
 removeFolder(child) 

Deprecated methods
https://developers.google.com/apps-script/reference/drive/folder#deprecated-methods_1


ファイルを移動したい

コード.gs
function moveFile() {
  const fileId = "移動したいファイルID";
  const destinationFolderId = "移動先のフォルダID";
  
  const file = DriveApp.getFileById(fileId);
  const destinationFolder = DriveApp.getFolderById(destinationFolderId);
  
  file.moveTo(destinationFolder);
}


フォルダを移動したい

コード2.gs
function moveFolder() {
  const folderId = "移動したいフォルダのID";
  const destinationFolderId = "移動先のフォルダID";
  
  const folder = DriveApp.getFileById(folderId);
  const destinationFolder = DriveApp.getFolderById(destinationFolderId);
  
  folder.moveTo(destinationFolder);
}


参考

moveTo(destination) 

Deprecated methods

Release Notes
July 27, 2020

Friday, February 19, 2021

Googleドライブの親フォルダ内の子フォルダのファイルを取得してHtmlServiceのselectに入れたい2(テキスト出力する)


先に書いた以下の続きです


Googleドライブのフォルダ構成

親フォルダ
└TEST3
└TEST2
└TEST1
    └サンプル2
    └サンプル1
        └テキスト(ドキュメントのbody)
        └説明(ファイルの説明)


サンプル1の中身


HtmlServiceでこのように表示したい




コード.gs
function doGet(e) {
  return HtmlService.createHtmlOutputFromFile("index");
}

/************************************
getFoldersInFolder
************************************/
function getFoldersInFolder() {
  const folderId = "親フォルダID";
  const parentFolder = DriveApp.getFolderById(folderId);
  const folders = parentFolder.getFolders();
  let objs = [];
  while(folders.hasNext()) {
    const folder = folders.next();
    const obj = {};
    obj["name"] = folder.getName();
    obj["id"] = folder.getId();
    objs.push(obj);
  }
  return objs;
}

/************************************
getFilesInFolder
************************************/
function getFilesInFolder(folderId) {
  const parentFolder = DriveApp.getFolderById(folderId);
  const files = parentFolder.getFiles();
  let objs = [];
  while(files.hasNext()) {
    const file = files.next();
    const obj = {};
    obj["name"] = file.getName();
    obj["id"] = file.getId();
    objs.push(obj);
  }
  return objs;
}

/************************************
getDataInFile
************************************/
function getDataInFile(fileId) {
  const file = DriveApp.getFileById(fileId);
  const obj = {};
  obj["name"] = file.getName();
  obj["id"] = file.getId();
  obj["description"] = file.getDescription();
  obj["text"] = getDocText(fileId)
  return obj;
}

/************************************
getDocText
************************************/
function getDocText(fileId) {
  const doc = DocumentApp.openById(fileId);
  const body = doc.getBody().getText();
  return body;
}




index.html
<!DOCTYPE html>
<html>
<body>
<div id="main">
  <select id="folders_select"></select>
  <br>
  <select id="files_select"></select>
  <br>
  <textarea id="text"></textarea>
  <textarea id="description"></textarea>
</div>
<script>

elem("folders_select").onchange = selectFolderChanged;
elem("files_select").onchange = selectFileChanged;

get_data();

/************************************
get_data()
************************************/
function get_data() {
  google.script.run
  .withFailureHandler(onFailure)
  .withSuccessHandler(onSuccess)
  .getFoldersInFolder();
}

/************************************
onSuccess(result)
************************************/
function onSuccess(folders) {
  const select = elem("folders_select");
  for(let i = 0; i < folders.length; i++) {
    const name = folders[i]["name"];
    const id = folders[i]["id"];
    console.log(name, id)
    createSelectOptions(select, name, id);
  }
}

/************************************
createSelectOptions
************************************/
function createSelectOptions(select, name, id) {
  const option = document.createElement("option");
  option.textContent = name;
  option.setAttribute("id", id);
  select.appendChild(option);
}

/************************************
selectFolderChanged
************************************/
function selectFolderChanged() {
  const select = elem("folders_select");
  const index = select.selectedIndex;
  const id = select[index].id;
  
  google.script.run
  .withFailureHandler(onFailure)
  .withSuccessHandler(onSuccessFolderChanged)
  .getFilesInFolder(id);
}

/************************************
selectFolderChanged
************************************/
function selectFileChanged() {
  const select = elem("files_select");
  const index = select.selectedIndex;
  const id = select[index].id;
  
  google.script.run
  .withFailureHandler(onFailure)
  .withSuccessHandler(onSuccessFileChanged)
  .getDataInFile(id);
}

/************************************
onSuccessFolderChanged
************************************/
function onSuccessFolderChanged(files) {
  const select = elem("files_select");
  for(let i = 0; i < files.length; i++) {
    const name = files[i]["name"];
    const id = files[i]["id"];
    console.log(name, id)
    createSelectOptions(select, name, id);
  }
}

/************************************
onSuccessFileChanged
************************************/
function onSuccessFileChanged(data) {
  console.log(data);
  elem("text").value = data["text"];
  elem("description").value = data["description"];
}

/************************************
onFailure(e)
************************************/
function onFailure(e) {
  alert([e.message, e.stack]);
}

/************************************
elem(id)
************************************/
function elem(id) {
  return document.getElementById(id);
}

</script>
</body>
</html>




Thursday, February 18, 2021

Googleドライブの親フォルダ内の子フォルダのファイルを取得してHtmlServiceのselectに入れたい


先に書いた
Googleドライブの特定フォルダ内のフォルダをHtmlServiceでselectのoptionに追加したい
ではselect要素にoption要素を追加しました。

今回は
optionの選択を変更した時に反応する処理を作ります。

ここでは
Googleドライブの親フォルダ内の子フォルダのファイルを取得して
2つ目のselectにoptionを追加します。


Googleドライブ内のフォルダとファイル

親フォルダ
└TEST3
└TEST2
└TEST1
    └サンプル2
    └サンプル1



HtmlServiceで、2つのselectのoptionの値を操作したい。



コード.gs
function doGet(e) {
  return HtmlService.createHtmlOutputFromFile("index");
}

/************************************
getFoldersInFolder
************************************/
function getFoldersInFolder() {
  const folderId = "親フォルダのID";
  const parentFolder = DriveApp.getFolderById(folderId);
  const folders = parentFolder.getFolders();
  let objs = [];
  while(folders.hasNext()) {
    const folder = folders.next();
    var obj = {};
    obj["name"] = folder.getName();
    obj["id"] = folder.getId();
    objs.push(obj);
  }
  return objs;
}

/************************************
getFilesInFolder
************************************/
function getFilesInFolder(folderId) {
  const parentFolder = DriveApp.getFolderById(folderId);
  const files = parentFolder.getFiles();
  let objs = [];
  while(files.hasNext()) {
    const file = files.next();
    var obj = {};
    obj["name"] = file.getName();
    obj["id"] = file.getId();
    objs.push(obj);
  }
  return objs;
}



index.html
<!DOCTYPE html>
<html>
<body>
<div id="main">
  <select id="folders_select"></select>
  <br>
  <select id="files_select"></select>
</div>
<script>

elem("folders_select").onchange = selectChanged;

get_data();

/************************************
get_data()
************************************/
function get_data() {
  google.script.run
  .withFailureHandler(onFailure)
  .withSuccessHandler(onSuccess)
  .getFoldersInFolder();
}

/************************************
onSuccess(result)
************************************/
function onSuccess(folders) {
  const select = elem("folders_select");
  for(let i = 0; i < folders.length; i++) {
    const name = folders[i]["name"];
    const id = folders[i]["id"];
    console.log(name, id)
    createSelectOptions(select, name, id);
  }
}

/************************************
createSelectOptions
************************************/
function createSelectOptions(select, name, id) {
  const option = document.createElement("option");
  option.textContent = name;
  option.setAttribute("id", id);
  select.appendChild(option);
}

/************************************
selectChanged
************************************/
function selectChanged() {
  const select = elem("folders_select");
  const index = select.selectedIndex;
  const id = select[index].id;
  
  google.script.run
  .withFailureHandler(onFailure)
  .withSuccessHandler(onSuccessChanged)
  .getFilesInFolder(id);
}

/************************************
onSuccessChanged
************************************/
function onSuccessChanged(files) {
  const select = elem("files_select");
  for(let i = 0; i < files.length; i++) {
    const name = files[i]["name"];
    const id = files[i]["id"];
    console.log(name, id)
    createSelectOptions(select, name, id);
  }
}

/************************************
onFailure(e)
************************************/
function onFailure(e) {
  alert([e.message, e.stack]);
}

/************************************
elem(id)
************************************/
function elem(id) {
  return document.getElementById(id);
}

</script>
</body>
</html>




Googleドライブの特定フォルダ内のフォルダをHtmlServiceでselectのoptionに追加したい


実現したいこと


Googleドライブ内で
以下のように
フォルダの中にフォルダがあって

親フォルダ
└TEST3
└TEST2
└TEST1

その子フォルダを

HtmlServiceの select > option に入れたい。



コード.gs
function doGet(e) {
  return HtmlService.createHtmlOutputFromFile("index");
}

/************************************
getFoldersInFolder
************************************/
function getFoldersInFolder() {
  const folderId = "親フォルダのID";
  const parentFolder = DriveApp.getFolderById(folderId);
  const folders = parentFolder.getFolders();
  let objs = [];
  while(folders.hasNext()) {
    const folder = folders.next();
    var obj = {};
    obj["name"] = folder.getName();
    obj["id"] = folder.getId();
    objs.push(obj);
  }
  return objs;
}



index.html
function doGet(e) {
  return HtmlService.createHtmlOutputFromFile("index");
}

/************************************
getFoldersInFolder
************************************/
function getFoldersInFolder() {
  const folderId = "16H_yu5VcqSaEy4EHhod0HrPOuGnud233";
  const parentFolder = DriveApp.getFolderById(folderId);
  const folders = parentFolder.getFolders();
  let objs = [];
  while(folders.hasNext()) {
    const folder = folders.next();
    var obj = {};
    obj["name"] = folder.getName();
    obj["id"] = folder.getId();
    objs.push(obj);
  }
  return objs;
}



Googleドライブ内の特定フォルダ内のフォルダ名とidを取得したい


Googleドライブ内で
指定した親フォルダ内の
子フォルダの名前とIDを取得するコードの例です。



コード.gs
function getFoldersInFolder() {
  const folderId = "親フォルダのID";
  const parentFolder = DriveApp.getFolderById(folderId);
  const folders = parentFolder.getFolders();
  let array = [];
  while(folders.hasNext()) {
    const folder = folders.next();
    var obj = {};
    obj["name"] = folder.getName();
    obj["id"] = folder.id();
    array.push(obj);
  }
  Logger.log(array);
}



Saturday, February 13, 2021

MacBookのSiriを無効にしたい


最近突然Siriが喋り出すことが複数回ありました。

そんなSiriを無効にしようと試みた備忘録です。

OS:Catalina


やったこと
システム環境設定 > Siri > ”Siriに頼む”のチェックを外す


STEP


STEP1
左上のメニューから「システム環境設定」を開きます


STEP2
「Siri」をクリックします


STEP3
”Siriに頼む”を有効にするにチェックが入っている

クリックしてチェックを外します


これで勝手に喋り出さなくなってほしい。


参考

MacのSiri環境設定を変更する
https://support.apple.com/ja-jp/guide/mac-help/mchl3fd7fc15/mac

MacでSiriを使用する

Wednesday, February 10, 2021

Slackbotでランダム返信させたい(コードを書かずに)


コードを書かずにSlackの機能でできました。



Preferencesの中にカスタマイズメニューがあります。
(階層や名称は環境によって異なるかも)


以下のURLのNAME部分を
自分の環境のものにしてアクセスすると設定画面が開くはず。
https://NAME.slack.com/customize/slackbot


STEP

Preferences > Customize



Slackbotを選択して
Add New Responseをクリックします。


When someone says:なんて言ったら
Slackbot responds:なんて返す(複数入れるとどれか一つを返すらしい)
を決めてSaveをクリックします。


試してみます。

Tuesday, February 9, 2021

スクリプトのプロパティをコードで削除したい


Google Apps Scriptの旧UIでプロパティを削除できなかったのでコードでやってみる。



スクリプトのプロパティの右端にある「削除」をクリック→保存しても削除されなかった。




これで削除できました。

コード.gs
function deleteProperty() {
  var key = "foobar";// 削除したいプロパティのキーを指定
  PropertiesService.getScriptProperties().deleteProperty(key);
}


補足

新UIではそもそもプロパティを表示する画面がみつからない。
これからはコードで読み書きしていくことになりそうだ。

Sunday, February 7, 2021

正規表現でAND検索を試してみる3


のパターンを動的に作る方法を分解したコード。

"文字列1", "文字列2"を配列で渡して

こういう結果を得たくて書いたコードです。
 ^(?=.*文字列1)(?=.*文字列2)



コード.gs
function myFunction() {
  var values = ["文字列1", "文字列2"];
  var pattern = createAndSearchPattern(values);
  Logger.log(pattern);
}

function createAndSearchPattern(values) {
  var pattern = "^";
  for(var i = 0; i < values.length; i++) {
    pattern += "(?=.*" + values[i] + ")";
  }
  return pattern;
}



Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...