Google Apps Script（GAS）でスプレッドシートを外部から操作する

今回はGoogle Apps Script（GAS）を使ってスプレッドシートを外部から操作してみることにします。

ネットショップもどきで購入ボタンをおすと在庫管理のデモのセルの値が減少していきます。

Contents

1 スプレッドシートをGASで操作する
2 スプレッドシートを外部から操作する
- 2.1 デプロイするときの注意点
- 2.2 在庫管理をする

スプレッドシートをGASで操作する

まずスプレッドシートを作成します。そして一番上のメニューから[ツール] ⇒ [スクリプトエディタ]を選択します。そして[コード.gs]にやりたいことを書けばよいのですが、まずは以下のように書きます。

スプレッドシートのデータを取得

ここでの引数は、取得を開始する行番号、取得を開始する列番号、取得したい行数、取得したい列数です。

function ShowResult(){
    let ret = GetCellValues();
    console.log(ret);
}

function GetCellValues(){
    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spreadsheet.getSheetByName("シート1");
    return sheet.getRange(1,1, 5, 3).getValues();
}

function ShowResult(){

let ret = GetCellValues();

console.log(ret);

}

function GetCellValues(){

let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

let sheet = spreadsheet.getSheetByName("シート1");

return sheet.getRange(1,1, 5, 3).getValues();

}

シートの状態はこうなっています。

これを保存して実行する関数をShowResult()と指定してから実行ボタンをおすと

[ [ '', '在庫数', '価格' ],
  [ '商品1', 86, 1000 ],
  [ '商品2', 96, 1200 ],
  [ '商品3', 82, 1500 ],
  [ '商品4', 97, 2000 ] ]

[ [ '', '在庫数', '価格' ],

[ '商品1', 86, 1000 ],

[ '商品2', 96, 1200 ],

[ '商品3', 82, 1500 ],

[ '商品4', 97, 2000 ] ]

と表示されます。

セルをひとつだけ特定してその値を取得したいというのであれば

function ShowResult(){
    let ret = GetCellValues();
    console.log(ret);
}

function GetCellValue(){
    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spreadsheet.getSheetByName("シート1");
    return sheet.getRange("B2").getValue();
}

function ShowResult(){

let ret = GetCellValues();

console.log(ret);

}

function GetCellValue(){

let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

let sheet = spreadsheet.getSheetByName("シート1");

return sheet.getRange("B2").getValue();

}

実行結果は 86 となります。

現在時刻を取得

現在時刻を示す文字列が必要なら

function ShowResult(){
    let ret = GetNowText();
    console.log(ret);
}

function GetNowText(){
  let date = new Date();
  return Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd: HH-mm-ss');
}

function ShowResult(){

let ret = GetNowText();

console.log(ret);

}

function GetNowText(){

let date = new Date();

return Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd: HH-mm-ss');

}

実行結果は 2021-08-10: 21-32-20 となります。この記事を作成しているときはこの時間でした。

セルの値を1減らす

以下は指定したセルの値を1減らします。

function ShowResult(){
    let ret = DecrementValue("B2");
    console.log(ret);
}

function DecrementValue(cell){
    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spreadsheet.getSheetByName("シート1");
    let value = sheet.getRange(cell).getValue();
    value--;
    sheet.getRange(cell).setValue(value);
    return cell + " = " + value;
}

function ShowResult(){

let ret = DecrementValue("B2");

console.log(ret);

}

function DecrementValue(cell){

let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

let sheet = spreadsheet.getSheetByName("シート1");

let value = sheet.getRange(cell).getValue();

value--;

sheet.getRange(cell).setValue(value);

return cell + " = " + value;

}

実行結果は B2 = 85 となります。値が1減りました。

今回使うのはこれくらいです。では本題にはいりましょう。外部から操作するにはどうすればいいのでしょうか？

スプレッドシートを外部から操作する

まずdoPostという関数を作成します。結果はJSONで返します。引数が設定されていない場合は不正な引数が渡されたことがわかるようにします。これで実行結果は{“value”:”OK”}、{“value”:”Illegal parameter”}のどちらかになります。

function doPost(e){
    let array = [];
    if (e == undefined || e.parameter == undefined)
        return ReturnResult("Illegal parameter");

    // 引数を使ってやりたいことを書く
    return ReturnResult("OK");
}

function ReturnResult(ret){
    //結果はJSONで返す
    let JsonData = {};
    JsonData.value = ret;
    return ContentService.createTextOutput(JSON.stringify(JsonData));
}

function doPost(e){

let array = [];

if (e == undefined || e.parameter == undefined)

return ReturnResult("Illegal parameter");

// 引数を使ってやりたいことを書く

return ReturnResult("OK");

}

function ReturnResult(ret){

//結果はJSONで返す

let JsonData = {};

JsonData.value = ret;

return ContentService.createTextOutput(JSON.stringify(JsonData));

}

デプロイするときの注意点

次に右上にあるデプロイのボタンを押します。新しいデプロイ、デプロイを管理、デプロイのテストの3つのメニューが表示されますが、最初は[新しいデプロイ]を選択します。そのあと表示される左側のメニューの上のほうにある歯車のようなマークをクリックして[ウェブアプリ]を選択します。

そのあと[説明]の部分には自分ああとで見たときにわかりやすい説明文を書き、[次のユーザーとして実行]の部分は[自分]を選択、[アクセスできるユーザー]は[全員]を選択します。そのあと[デプロイ]をクリックします。このとき[ウェブアプリ url]が発行されるのでメモしておきます。

コードを修正した場合は[デプロイの管理] ⇒ [新バージョン]を選択しないと反映されません。知らないと気づきにくいハマりどころとなります。

在庫管理をする

外部からGASを使うために外部にHTMLファイルを作成します。ボタンがクリックされると購入されたことになり、在庫が1減ります。

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>サンプル</title>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
</head>

<body>
<p>サンプル</p>
<p>サンプルでつくった店です。実際にはなにも購入できません。</p>
<button type="button" onclick="ToBuy('item1')">商品1を購入</button>
<button type="button" onclick="ToBuy('item2')">商品2を購入</button>
<button type="button" onclick="ToBuy('item3')">商品3を購入</button>
<button type="button" onclick="ToBuy('item4')">商品4を購入</button>
<button type="button" onclick="ToBuy('item5')">商品5を購入</button>

<script>
function ToBuy(itemID){
    let posturl = "https://script.google.com/macros/s/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/exec";
    let obj = {
        functionName:"ToBuy",
        p1:itemID,
        p2:"", // 特に必要ないけどあとで必要になるかもしれないので他の引数もつくっておく
        p3:"",
        p4:"",
    }
    $.post(posturl, obj, ShowResult);
}

function ShowResult(data, status, xhr){
    console.log(data);
    alert(data);
}
</script>

</body>
</html>

<!DOCTYPE html>

<head>

</head>

<body>

<p>サンプルでつくった店です。実際にはなにも購入できません。</p>

function ToBuy(itemID){

let posturl = "https://script.google.com/macros/s/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/exec";

let obj = {

functionName:"ToBuy",

p1:itemID,

p2:"", // 特に必要ないけどあとで必要になるかもしれないので他の引数もつくっておく

p3:"",

p4:"",

}

$.post(posturl, obj, ShowResult);

}

function ShowResult(data, status, xhr){

console.log(data);

alert(data);

}

</script>

</body>

</html>

そしてGASはこのように書きます。書き直したら[デプロイ] ⇒ [デプロイの管理]を選択して鉛筆のようなマークをクリック、バージョンを新バージョンにします。これをやらないと変更が反映されません。

function doPost(e){
    if (e == undefined || e.parameter == undefined)
        return ReturnResult("Illegal parameter");

    if(e.parameter.functionName == "ToBuy"){
        let itemID = e.parameter.p1;
        if(ToBuy(itemID))
          return ReturnResult("OK");
    }
    return ReturnResult("Illegal parameter");
}

function ToBuy(itemID){
    let stockCell = "";
    let dayCell = "";
    let timeCell = "";

    if(itemID == "item1"){
        stockCell = "B2";
        dayCell = "D2";
        timeCell = "E2";
    }
    else if(itemID == "item2"){
        stockCell = "B3";
        dayCell = "D3";
        timeCell = "E3";
    }
    else if(itemID == "item3"){
        stockCell = "B4";
        dayCell = "D4";
        timeCell = "E4";
    }
    else if(itemID == "item4"){
        stockCell = "B5";
        dayCell = "D5";
        timeCell = "E5";
    }
    else
        return false;
        // itemID == "item5" はあえて書いていない

    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spreadsheet.getSheetByName("シート1");
    let value = sheet.getRange(stockCell).getValue();

    // 在庫がある場合は1減らす
    if(value > 0){
        value--;
        sheet.getRange(stockCell).setValue(value);

        // 在庫が変更された日と時刻も記録する
        let date = new Date();
        let day = Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd');
        let time = Utilities.formatDate( date, 'Asia/Tokyo', 'HH-mm-ss');

        sheet.getRange(dayCell).setValue(day);
        sheet.getRange(timeCell).setValue(time);
        return true;
    }
    return false;
}

function doPost(e){

if (e == undefined || e.parameter == undefined)

return ReturnResult("Illegal parameter");

if(e.parameter.functionName == "ToBuy"){

let itemID = e.parameter.p1;

if(ToBuy(itemID))

return ReturnResult("OK");

}

return ReturnResult("Illegal parameter");

}

function ToBuy(itemID){

let stockCell = "";

let dayCell = "";

let timeCell = "";

if(itemID == "item1"){

stockCell = "B2";

dayCell = "D2";

timeCell = "E2";

}

else if(itemID == "item2"){

stockCell = "B3";

dayCell = "D3";

timeCell = "E3";

}

else if(itemID == "item3"){

stockCell = "B4";

dayCell = "D4";

timeCell = "E4";

}

else if(itemID == "item4"){

stockCell = "B5";

dayCell = "D5";

timeCell = "E5";

}

else

return false;

// itemID == "item5" はあえて書いていない

let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

let sheet = spreadsheet.getSheetByName("シート1");

let value = sheet.getRange(stockCell).getValue();

// 在庫がある場合は1減らす

if(value > 0){

value--;

sheet.getRange(stockCell).setValue(value);

// 在庫が変更された日と時刻も記録する

let date = new Date();

let day = Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd');

let time = Utilities.formatDate( date, 'Asia/Tokyo', 'HH-mm-ss');

sheet.getRange(dayCell).setValue(day);

sheet.getRange(timeCell).setValue(time);

return true;

}

return false;

}

これでHTMLにアクセスして購入ボタンを押してみましょう。すると{“value”:”OK”}と表示され、在庫が1減ります。ところが商品5に対応する記述はToBuy関数のなかにはないので、{“value”:”Illegal parameter”}と表示されます。

在庫管理のデモ

ネットショップもどき

Google Apps Script（GAS）でスプレッドシートを外部から操作する

スプレッドシートをGASで操作する

スプレッドシートのデータを取得

現在時刻を取得

セルの値を1減らす

スプレッドシートを外部から操作する

デプロイするときの注意点

在庫管理をする

鳩でも分かるC#管理人からのお願い

コメントについて

管理人のモチベーションアップのために

コメントをどうぞ