Google Apps Script試行錯誤 Blog: 2023

2023年9月30日土曜日

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 can be used with the filter function to extract data from a range of cells.

This can be useful for a variety of tasks, such as filtering data by a specific pattern, extracting specific information from a string, or matching data to a specific format.

In this blog post, I will give an example of how regular expressions can be used with the filter function to extract data.

Example

1. Select the range you want to filter.

2. Click the filter icon in the toolbar.

3. Select "Custom formula is" from the filter condition drop-down menu.

Filter by condition > Custom formula is

4. Enter the formula =REGEXMATCH(range, "[A-Z]") in the input box.

5. Click OK.

The data will be extracted based on the regular expression that I set.

Tips

=REGEXMATCH(B:B, "[A-Z]")

Reference

Use conditional formatting rules in Google Sheets
https://support.google.com/docs/answer/78413?hl=en&co=GENIE.Platform%3DDesktop

REGEXMATCH
https://support.google.com/docs/answer/3098292?hl=en

Web colors
https://en.wikipedia.org/wiki/Web_colors

2023年9月26日火曜日

Get Hidden Rows in Google Sheets with Google Apps Script

Introduction

Google Sheets allows you to hide rows and columns.

Hidden rows are not displayed on the sheet and cannot be edited.

To get hidden rows, you can use Google Apps Script.

Google Apps Script provides the isRowHiddenByUser() method in the Sheet class to determine if a specified row is hidden.

Source Code

Code.gs

function getHiddenRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("A1:A" + lastRow);
  var values = range.getValues();
  var hiddenRows = [];
  for(var i = 0; i < values.length; i++) {
    var row = i + 1
    var hidden = sheet.isRowHiddenByUser(row);
    if(hidden) {
      hiddenRows.push(row);
    }
  }
  Logger.log(["hiddenRows: ", hiddenRows]);
}

Reference

isRowHiddenByUser(rowPosition)

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#isrowhiddenbyuserrowposition

2023年9月24日日曜日

New line removal App using Google Apps Script

Introduction

This is a simple application for removing new lines from the input textarea to the output textarea.

Application

After typing in the input text area, if you click on the output text area, the text will be input with line breaks removed.

Sample App

Source Code

Code.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index')
    .evaluate()
    .setTitle("Title");
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

index.html

<!DOCTYPE html>
<html>
<head>
  <?!= include("css"); ?>
</head>
<body>
  <textarea id="input"></textarea>
  <br>
  <textarea id="output"></textarea>
  <?!= include("javascript"); ?>
</body>
</html>

css.html

<style>
  textarea {
    width: 90vw;
    height: 40vh;
  }
</style> 

javascript.html

<script>
  function elem(id) {
    return document.getElementById(id);
  }

  elem("input").onchange = inputChanged;

  function inputChanged() {
    elem("output").value = elem("input").value.replace(/\n/g, "");
  }
</script>

2023年9月23日土曜日

Creating a template copier app in Google Apps Script

Introduction

This article will show you a template copier application in Google Apps Script.

This application will automatically copy a template to your MyDrive when you access the deployed URL.

This is useful for anyone who wants to save time and effort when developing web applications with HTMLService.

Application

When you access the deployed URL, the "OPEN FILE" link will be displayed on the screen.

The application Code1.gs is deployed as a web application.

When you access the URL, the Template file, which includes Code2.gs, index.html, css.html and javascript.html are copied.

A link to the file is displayed in the browser.

Clicking the link opens the file that copied the Template.

Source Code

This code is for an application that is deployed to copy templates.

Code1.gs

const FILE_ID = "FILE_ID";

function doGet() {
  const copyFileUrl = copyFileToMydrive();
  const html = '<a target="_blank" href="' + copyFileUrl + '">OPEN FILE</a>';
  return HtmlService.createHtmlOutput(html);
}

function copyFileToMydrive() {
  const file = DriveApp.getFileById(FILE_ID);
  const name = "Copy of " + file.getName();
  const copyFile = file.makeCopy(name);
  return copyFile.getUrl();
}

As a template to copy from, we will prepare the following code.

The contents of the code are up to you.

The following is just one example, so please feel free to write it as you like.

Template:

Code2.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index')
    .evaluate()
    .setTitle("Title");
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function returnHello(name) {
  return "Hello" + " " + name;
}

index.html

<!DOCTYPE html>
<html>
<head>
  <?!= include("css"); ?>
</head>
<body>
  <input type="text" id="name">
  <button id="bt">submit</button>
  <?!= include("javascript"); ?>
</body>
</html>

css.html

<style>
  #name {
    width: 10vw;
    height: 5vh;
    font-size: 20px;
  }
</style> 

javascript.html

<script>
  document.getElementById("bt").onclick = runHello;

  function runHello() {
    var name = document.getElementById("name").value;
    google.script.run
      .withFailureHandler(onFailure)
      .withSuccessHandler(onSuccess)
      .withUserObject("how are you?")
      .returnHello(name);
  }

  function onSuccess(result, userObject) {
    alert(result + " " + userObject);
  }

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

This template app is as follows:

Tips

By changing the FILE_ID in Code1.gs, you can copy any file in Google Drive, such as scripts, spreadsheets, or documents.

Reference

Class File > makeCopy() Method

https://developers.google.com/apps-script/reference/drive/file#makecopy

2023年9月22日金曜日

Trying Google Translate with Google Apps Script

Introduction

We will create a simple English to Japanese translator using Google Apps Script.

The application will have a front-end input form and a back-end server that will process the translation results.

Application

We need to create a front-end form that will allow users to enter the English text they want to translate.

When you enter English that you want to translate to Japanese and click the button, the English is passed to the server side, and the translated Japanese is returned to the front end using LanguageApp.translate.

Source Code

Code.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index').setTitle("Title");
}

function returnJapanese(value) {
  return LanguageApp.translate(value, "en", "ja");
}

index.html

<!DOCTYPE html>
<html>
<body>
  <textarea id="text"></textarea>
  <button id="submit">Translate to Japanese</button>
  <script>
    document.getElementById("submit").onclick = runTranslate;

    function runTranslate() {
      var value = document.getElementById("text").value;
      google.script.run
        .withFailureHandler(onFailure)
        .withSuccessHandler(onSuccess)
        .returnJapanese(value);
    }

    function onSuccess(result) {
      alert(result);
    }

    function onFailure(e) {
      alert([e.message, e.stack]);
    }
  </script>
</body>
</html>

Tips

LanguageApp.translate(text, sourceLanguage, targetLanguage)

If source language is empty, it will be auto-detected.

Reference

Class LanguageApp
https://developers.google.com/apps-script/reference/language/language-app

Language support
https://cloud.google.com/translate/docs/languages

English

Take your time.

Japanese

ゆっくりしてください。

2023年9月21日木曜日

A simple application using google.script.run

Introduction

I will introduce a simple application that uses Google Apps Script and google.script.run to display a message with the user's name.

The application places a text box and a button on the screen.

When the button is clicked, the name entered in the text box is passed to a server-side Apps Script function, which returns a message with the user's name.

The message is then displayed on the screen.

I will also explain how to use the google.script.run API to call server-side Apps Script functions from client-side JavaScript.

The google.script.run API allows you to pass arguments to the server-side functions and to receive results back from them.

Application

A text box and a button on the screen

Enter your name and click the button, a message will be displayed on the screen.

Source Code

Code.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index').setTitle("Title");
}

function returnHello(name) {
  return "Hello" + " " + name;
}

index.html

<!DOCTYPE html>
<html>
<body>
  <input type="text" id="name">
  <button id="bt">submit</button>
  <script>
    document.getElementById("bt").onclick = runHello;

    function runHello() {
      var name = document.getElementById("name").value;
      google.script.run
        .withFailureHandler(onFailure)
        .withSuccessHandler(onSuccess)
        .withUserObject("how are you?")
        .returnHello(name);
    }

    function onSuccess(result, userObject) {
      alert(result + " " + userObject);
    }

    function onFailure(e) {
      alert([e.message, e.stack]);
    }
  </script>
</body>
</html>

Tips

google.script.run

call server-side Apps Script functions(in Code.gs)

.withFailureHandler(function)

a callback function to run if the server-side function throws an exception

.withSuccessHandler(function)

a callback function to run if the server-side function returns successfully

.withUserObject(object)

a second parameter to the success and failure handlers

.returnHello(name)

run a function in Code.gs

Reference

Class google.script.run (Client-side API)

https://developers.google.com/apps-script/guides/html/reference/run

2023年9月20日水曜日

Creating HTML with Google Apps Script(createHtmlOutputFromFile)

This is an example of code that creates a simple HTML page with Google Apps Script's HtmlService.createHtmlOutputFromFile().

The output HTML page will be as follows.

The script editor will look like this:

Code.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index').setTitle("Title");
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <style>
      textarea {
        width: 80vw;
        height: 80vh;
        font-size: 15px;
      }
    </style>
  </head>
  <body>
    <textarea></textarea>
    <script>
      myFunction();
      function myFunction() {
        console.log("This is a log message.");
      }
    </script>
  </body>
</html>

Reference

HTML Service: Create and Serve HTML

https://developers.google.com/apps-script/guides/html/

2023年9月19日火曜日

Creating HTML with Google Apps Script(createTemplateFromFile)

This is an example of code that creates a simple HTML page with Google Apps Script's HtmlService.createTemplateFromFile().

HtmlService.createTemplateFromFile() allows you to manage CSS and JavaScript files separately from index.html.

The output HTML page will be as follows.

The script editor will look like this:

We can create a .html file by clicking the Plus icon to the right of Files and selecting HTML.

Code.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index')
    .evaluate()
    .setTitle("Title");
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <?!= include('css'); ?>
  </head>
  <body>
    <textarea></textarea>
    <?!= include('javascript'); ?>
  </body>
</html>

css.html

<style>
  textarea {
    width: 80vw;
    height: 80vh;
    font-size: 15px;
  }
</style>

javascript.html

<script>
  myFunction();
  function myFunction() {
    console.log("This is a log message.");
  }
</script>

Reference

HTML Service: Templated HTML
https://developers.google.com/apps-script/guides/html/templates

HTML Service: Best Practices
https://developers.google.com/apps-script/guides/html/best-practices

2023年3月31日金曜日

JIRA APIで選択リスト（複数選択）を課題作成時に選択してPOSTしたい

JIRA APIを利用して選択リスト（複数選択）フィールドに値を入れたくて書いたコードです。

コード.gsのこの部分で複数選択の値を選択できました。

customfield_10043 は手元のJIRAでの選択リスト（複数選択）のフィールドIDなので、各自の環境によって異なります。

valueの値は選択肢の文字列です。

customfield_10043: [// 複数選択チェックボックス

{

"value": "チェック2"

{

"value": "チェック4"

}

他の部分はPOSTする際に共通で使っているコードです。

コード.gs

var BASE_URL = 'https://SITENAME.atlassian.net/rest/api/2/';
function doPost() {
  var payload = get_payload();
  var options = get_options(payload);
  var response = UrlFetchApp.fetch(BASE_URL + "issue/", options);
  var key = get_key(response);
  Logger.log(key);
}

function get_payload() {
  var data = {
    project: {key: "KEY"},
    issuetype: {name: "Bug"},
    summary: "たいとる",
    description: "タスクの説明です\n説明の２行め",
    customfield_10043: [// 複数選択チェックボックス
      {
        "value": "チェック2"
      },
      {
        "value": "チェック4"
      }
    ],
    reporter: {id: getAccountId()},
  };
  var fields = {fields: data};
  var payload = JSON.stringify(fields);
  return payload;
}

function get_options(payload) {
  var options = {
    method: "post",
    payload: payload,
    contentType: "application/json",
    headers: {"Authorization": " Basic " + getToken()}
  }
  return options;
}

function getAccountId() {
  var email = Session.getActiveUser().getEmail();
  var url = BASE_URL + 'user/search?query=' + email;
    var options = {
    method: "get",
    contentType: "application/json",
    headers: {"Authorization": " Basic " + getToken()},
    muteHttpExceptions: true
  }
  var response = UrlFetchApp.fetch(url, options);
  var jobj = JSON.parse(response);
  var accountInfo = getAccountInfo(jobj, email);
  var accountId = accountInfo['accountId'];
  return accountId;
}

function getAccountInfo(jobj, email) {
  var accountInfo;
  for(var i = 0; i < jobj.length; i++) {
    var emailAddress = jobj[i]['emailAddress'];
    if(emailAddress === email) {
      accountInfo = jobj[i];
    }
  }
  return accountInfo;
} 

function get_key(response){
  var jobj = JSON.parse(response);
  var key = jobj["key"];
  return key;
}

function getToken() {
  return PropertiesService.getScriptProperties().getProperty('token');
}

Google Apps Script試行錯誤 Blog

2023年9月30日土曜日

Extracting data from Google Sheets with regular expressions

2023年9月26日火曜日

Get Hidden Rows in Google Sheets with Google Apps Script

2023年9月24日日曜日

New line removal App using Google Apps Script

2023年9月23日土曜日

Creating a template copier app in Google Apps Script

2023年9月22日金曜日

Trying Google Translate with Google Apps Script

2023年9月21日木曜日

A simple application using google.script.run

2023年9月20日水曜日

Creating HTML with Google Apps Script(createHtmlOutputFromFile)

2023年9月19日火曜日

Creating HTML with Google Apps Script(createTemplateFromFile)

2023年3月31日金曜日

JIRA APIで選択リスト（複数選択）を課題作成時に選択してPOSTしたい

Latest post

Google Apps Scriptの障害時はIssueTrackerを見てみる - Incidents for Apps Script are reported on Issue Tracker