HOME
  Security
   Software
    Hardware
  
FPGA
  CPU
   Android
    Raspberry Pi
  
nLite
  Xcode
   etc.
    ALL
  
English Translate 中文翻訳
LINK
BACK
 

[NEW] 2019/08/10

Google Apps Scriptを使って外部スプレッドシートの情報を取得してシート名と URLの一覧表を作る方法 Google Apps Scriptを使って外部スプレッドシートの情報を取得してシート名と URLの一覧表を作る方法

(GASを使って外部のスプレッドシートの全シートの情報を取得して、シート名と URLリンク情報を取得する方法)

Tags: [Windows開発]





● Google Apps Scriptを使って外部スプレッドシートの情報を取得してシート名と URLの一覧表を作る方法

 GASを使って外部のスプレッドシートの全シートの情報を取得して、各シート名と URLリンクの情報を取得する方法を解説します。


 これの目的は外部の第三者が更新管理している Googleスプレッドシートの情報を監視して、情報が更新され次第、その中身の情報を取得して、処理をすると言う処理が必要な為です。

 そして、その更新方法が「日々シートを追加して情報を更新」と言う方法となっています。

 「日々シートを追加」と言う更新方法なので固定の URL情報を監視して更新情報を取得するという方法が取れません。

 と言う訳で、第三者が管理する外部の Google スプレッドシートの情報を取得する処理が必要になってきます。

 必要な情報としては、「スプレッドシート ID(ssid)」と呼ばれる文字列情報で、URLの下記の部分になります。
 取得する外部のスプレッドシートのスプレッドシート ID
https://docs.google.com/spreadsheets/d/{ssidはここの部分の文字列}/edit#gid=xxxx
 ちなみに gid=の後ろの ID情報は「各シート」の ID(gid)となります。

 例として、
https://docs.google.com/spreadsheets/d/hogehogefugahuga123/edit#gid=1234567890
 の場合は
 スプレッドシート ID(ssid) = hogehogefugahuga123
 シートの ID(gid) = 1234567890
 となります。

 スプレッドシート ID(ssid)の情報が分かればそのスプレッドシートが持つ各シート(複数のシート)のそれぞれの情報(シート名称、gid情報、シートの中身の各セルの情報、等々)を全部取得できます。


 対象のスプレッドシートは、複数のシートで管理しており、
 ・最新測定値
 それ以前の古い日付の測定情報は、
 ・8月14日
 ・8月13日
 ・8月12日
 、、、
 の様なシート名で管理しています。

 次の日の8月16日になると、「最新測定値」のシート名が「8月15日」になり、新たに「最新測定値」のシートが追加されます。
 (なので、8月15日時点での「最新測定値」と、翌日以降の「8月15日」は同一の gidのシートとなります)

 では、手順を説明します。

 まずは Googleのアカウントを取得します。
 これは、自分の Googleアカウントで情報取得用のスプレッドシートを作成する為です。

 そして、情報取得用のスプレッドシートを新規作成します。
 この情報取得用のスプレッドシートは自分だけが編集閲覧できる「非公開」で大丈夫です。

 情報取得用のスプレッドシートに「外部のスプレッドシートの情報を取得する」スクリプトを記述します。

 この「スクリプト(Google Apps Script、または単に Apps Script)」が主役となります。
 この「スクリプト」は「エクセル」で言う所の VBAマクロに相当します。

● Googleスプレッドシートに Google Apps Scriptの「スクリプト」を追加する方法

 スプレッドシートのメニューの「ツール」から「スクリプト エディタ」を選択します。
 「スクリプト エディタ」が新しいブラウザ窓で開きます。

 最初は function myFunction()の下記の内容となっています。
function myFunction() {
}

 「無題のプロジェクト」の編集エリアに下記の内容をコピペします。
function myFunction() {
  GetOtherSpreadSheetInformation();
}

 // 外部スプレッドシートの全シートのシート名とシート ID(gid)を取得する
function GetOtherSpreadSheetInformation()
{
  // 外部スプレッドシートの全シートのシート名とシート ID(gid)を取得する
  // 取得する外部のスプレッドシートのスプレッドシート ID
  // https://docs.google.com/spreadsheets/d/{ssIdはここの部分の文字列}/edit#gid=xxxx
  var ssId = "{外部のスプレッドシートの ssidの文字列}";

  // 指定した ssIDの外部スプレッドシートの情報を丸ごと取得する
  var spreadsheet = SpreadsheetApp.openById(ssId);

  // 現在の自分のスプレッドシートの情報を取得する場合
  // var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // 全シートのシート属性情報を取得する
  var sheets = spreadsheet.getSheets()

  // 現在の自分のシートを取得する
  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 現在の自分のシートの内容をクリアする
  mySheet.clear();
  // 現在の自分のシートの選択中のセルを取得する
  var myCell = mySheet.getActiveCell();
  // 現在の自分のシートの選択中のセルを A1にする
  myCell = mySheet.getRange('A1');
  myCell.activate();

  // シートの数だけループする
  for (var i=0; i<sheets.length; ++i) {

    // 処理対象のシート
    var sheet = sheets[i];

    // シートが「不可視」の場合は処理をスキップする
    if (sheet.isSheetHidden()) {
        continue;
    }

    // シートの ID(gid)とシートの名前
    var sheetId = sheet.getSheetId();
    var sheetName = sheet.getSheetName();
    // シートのセル A1を取得する
    var cellA1 = sheet.getRange('A1');

    // 単純な文字列結合で各シートの URL文字列を生成する
    var sheetUrl = "https://docs.google.com/spreadsheets/d/" + ssId + "/edit#gid=" + sheetId;

    // 現在の自分のシートの選択中のセルに取得した外部スプレッドシートの情報を格納する
    myCell.clear();

    // シートが「不可視」の場合はセルの背景色をグレーにする
    if (sheet.isSheetHidden()) {
        myCell.setBackground("#AAAAAA");
    }

    // シート名を代入する
    myCell.setValue(sheetName);
    // シートの gidを代入する
    myCell.offset(0, 1).setValue(sheetUrl);

    // シートのセル A1の内容を代入する
    myCell.offset(0, 2).setValue(cellA1.getValue());

    // ハイパーリンク形式の場合は下記の様に HYPERLINK()文字列を setValueする
    var sheetLink = '=HYPERLINK("' + sheetUrl + '","' + sheetName + '")';
    myCell.offset(0, 3).setValue(sheetLink);

    // 選択中のセルを一つ下に移動する
    myCell = myCell.offset(1, 0).activate();
  }
}

・「無題のプロジェクト」の編集エリアに Google Apps Scriptの内容をコピペします
「無題のプロジェクト」の編集エリアに Google Apps Scriptの内容をコピペします




● Google Apps Script スクリプトの実行方法

 スクリプトエディタのメニューバーの下の「関数を選択」のプルダウンで、「myFunction」または「GetOtherSpreadSheetInformation」を選択して、その左横の▲マークを押すとスクリプトが実行されます。

・Google Apps Script スクリプトの実行方法
Google Apps Script スクリプトの実行方法


・Google Apps Script スクリプトの実行方法
Google Apps Script スクリプトの実行方法


・Google Apps Script スクリプトの実行方法
Google Apps Script スクリプトの実行方法




●上記のスクリプトで使用している Google Apps Scriptの各命令の説明

Apps Script - Google Developers

● Class SpreadsheetApp
 「エクセル」で言う所の「エクセル」アプリケーションに相当します。

Class SpreadsheetApp
openById(id)指定したスプレッドシートにアクセスするOpens the spreadsheet with the given ID. A spreadsheet ID can be extracted from its URL.
 For example、 the spreadsheet ID in the URL
 https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0
 is "abc1234567" .

● Class Spreadsheet
 「エクセル」で言う所の「ブック」に相当します。

Class Spreadsheet
getSheets()スプレッドシートが持つ全シートの属性情報を取得するGets all the sheets in this spreadsheet .

● Class Sheet
 「エクセル」で言う所の「シート」に相当します。

Class Sheet
isSheetHidden()シートが「不可視」かどうかを確認するReturns true if the sheet is currently hidden .
getSheetId()シートの gid情報を取得するReturns the ID of the sheet represented by this object .
getSheetName()シートのシート名称を取得するReturns the sheet name .

● Class Range
 「エクセル」で言う所の「セル(データを入力するマス)」および「セルの選択状態(カーソル位置)」に相当します。

Class Range
setValue(value)セルに値を代入するSets the value of the range. The value can be numeric、 string、 boolean or date . If it begins with '=' it is interpreted as a formula .
getValue()セルの値を取得するReturns the value of the top-left cell in the range .
activate()選択中のセルを有効にするSets the specified range as the active range、 with the top left cell in the range as the current cell .
setBackground(color)選択中のセルの背景色を設定するSets the background color of all cells in the range in CSS notation (such as '#ffffff' or 'white') .
clear()すべてのセルの内容をクリアする
(シート全体をクリアする)
Clears the range of contents、 formats、 and data validation rules .


● Google Apps Scriptと Excelとの用語の対応表

Google Apps ScriptExcel
Spreadsheet
スプレッドシート
(ssidで管理)
ブック
Sheet
(gidで管理)
シート
Rangeセル・カーソル位置
Apps ScriptVBAマクロ


● Google Apps Scriptを使わないで外部スプレッドシートの情報を取得する方法

 Google Apps Scriptを使わないで外部スプレッドシートの情報を取得する事もできます。
 この場合は、下記の 2つの情報が必要となります。(事前に知っている必要がある)

 ・取得する外部のスプレッドシートのスプレッドシート ID
 ・取得するシートのシート名

 下記の様にセルに IMPORTRANGE関数を記述します。
IMPORTRANGE - 指定したスプレッドシートからセルの範囲を読み込みます。

構文
IMPORTRANGE(スプレッドシートキー, 範囲の文字列)

 下記はどちらも同じ意味で、「スプレッドシートキー」の指定は ssid文字列だけでも大丈夫です。
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/{ssid}","{シート名}!A5")
=IMPORTRANGE("{ssid}","{シート名}!A5")

記述例
 ・取得する外部のスプレッドシートのスプレッドシート ID = abc1234567
 ・取得する外部のスプレッドシートのシート名 = 最新測定値
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc1234567","最新測定値!A5")
=IMPORTRANGE("abc1234567","最新測定値!A5")

取得内容:
hogehoge123


● Google Apps Scriptを Web APIとして実行する方法

 スプレッドシートを開いて手動でスクリプトを実行だと「自動化」が不完全です。
 httpの Webアクセスで情報を取得できるように改善します。

Executing Functions using the Apps Script API

 スクリプトエディタのメニューの「公開」「ウェブ アプリケーションとして導入...」を選択して、「現在のウェブ アプリケーションの URL」として「スクリプト実行用」の URLを発行します。

 PHPや Python等の外部のプログラムからその URLにアクセスすればスクリプトの実行結果を取得できます。

 変更内容の抜粋
 // Apps Script API
function doGet(e) {
  var response = GetOtherSpreadSheetInformation();
  return ContentService.createTextOutput(response);
}

function GetOtherSpreadSheetInformation()
{
  // レスポンス文字列
  var response = "";

~~ 略 ~~

    // レスポンス文字列(タブ区切り)
    response += sheetName;
    response += "\t";
    response += sheetUrl;
    response += "\t";
    response += cellValue;
    response += "\n";
  }

  return response;
}

・Google Apps Scriptを Web APIとして実行する方法
Google Apps Scriptを Web APIとして実行する方法


・Google Apps Scriptを Web APIとして実行する方法
Google Apps Scriptを Web APIとして実行する方法




● Google Apps Scriptを Web Appとして実行した時に「スクリプト関数が見つかりません: doGet」エラーが出る場合

 doGet関数を追加します。

・Google Apps Scriptを Web Appとして実行した時に「スクリプト関数が見つかりません: doGet」エラーが出る場合
Google Apps Scriptを Web Appとして実行した時に「スクリプト関数が見つかりません: doGet」エラーが出る場合



 doGet関数を追加してもエラーが出る場合は「ウェブ アプリケーションを無効にする」で一旦無効にしてから再度有効にします。

 // Apps Script API GET method
function doGet() {
  // Apps Script API用のレスポンス
  var response = {
    meta: { status: 'success' }
  };
  return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
}

 この場合はレスポンスで下記の文字列が返ってきます。
{"meta":{"status":"success"}}

 POST methodの場合は下記の様に doPost関数を追加します。
 // Apps Script API POST method
function doPost() {
  // Apps Script API用のレスポンス
  var response = {
    meta: { status: 'success' }
  };
  return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
}


● Google Apps Scriptを完全自動のスクリプトとして実行して更新情報をメールで通知

 上記の Google Apps Scriptを Web API化すると、PHP言語や Python言語等で cron crontab等の定時バッチで完全自動のスクリプトとして実行できます。

1) 定時バッチで PHP言語で Web APIを実行
2) 実行結果のレスポンスから各シートの情報を取得
3) 各シートの情報(シートの内容)を取得
4) 取得したシートの内容を整形してメールで通知
5) (゚д゚)ウマー


[NEW] 2019/04/13
Raspberry Piで cronを使って起動時にコマンドを自動実行したりn分間隔で繰り返し実行する方法
Raspberry Piで cronを使って起動時にコマンドを自動実行したりn分間隔で繰り返し実行する方法

  ラズパイで電源オンで PHPスクリプト等を自動で実行する方法、監視プログラムや定時バッチ等の実行に便利 crontab


●アフィリエイト情報の更新処理の全自動化にも便利です

[UPDATE] 随時更新
【随時更新】Gearbestの割引クーポン & セール情報、ギアベスト【更新即反映】
【随時更新】Gearbestの割引クーポン & セール情報、ギアベスト【更新即反映】

  Gearbest 割引クーポン一覧(ノートPC・タブレット・スマホ・その他)

[UPDATE] 随時更新
【随時更新】Banggoodの割引クーポン & セール情報【更新即反映】
【随時更新】Banggoodの割引クーポン & セール情報【更新即反映】

  Banggood 割引クーポン一覧(ノートPC・タブレット・スマホ・その他)



Tags: [Windows開発]


[HOME] | [BACK]
リンクフリー(連絡不要、ただしトップページ以外は Web構成の変更で移動する場合があります)
Copyright (c) 2019 FREE WING,Y.Sakamoto
Powered by 猫屋敷工房 & HTML Generator

http://www.neko.ne.jp/~freewing/software/google_app_script_get_other_spread_sheet_inforation/