スクレイピングしたデータをGASを使ってスプレッドシートに書き込むのは簡単と思っていたら意外に難儀した話

GASを使ってスプレッドシートに書き込むというのは、Google Apps Script（GAS）でスプレッドシートを外部から操作するでもやりました。またスクリプトエディタで以下のようなものをつくればスクリプト内の任意の関数を呼び出すことができます。

Contents

1 GASを外部から実行させる
2 GASは外部からデータを二次元配列で渡せない
3 どうしても二次元配列をGASに渡したい場合
4 C#ではありえないJavaScriptならではのハマりどころ

GASを外部から実行させる

function doPost(e){
    if (e == undefined || e.parameter == undefined)
        return ReturnResult("不正なパラメータ");

    if(e.parameter.functionName == "呼び出したい関数名その1"){
        呼び出したい関数その1(e.parameter);
        return ReturnResult("処理完了");
    }

    if(e.parameter.functionName == "呼び出したい関数名その2"){
        呼び出したい関数その2(e.parameter);
        return ReturnResult("処理完了");
    }
}

function 呼び出したい関数その1(param){
    // やりたいことを書く
}

function 呼び出したい関数その2(param){
    // やりたいことを書く
}

function ReturnResult(ret){
    //結果はJSONで返す
    //JSONオブジェクト格納用の入れ物
    let JsonData = {};
    JsonData.value = ret;
    return ContentService.createTextOutput(JSON.stringify(JsonData));
}

function doPost(e){

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

return ReturnResult("不正なパラメータ");

if(e.parameter.functionName == "呼び出したい関数名その1"){

呼び出したい関数その1(e.parameter);

return ReturnResult("処理完了");

}

if(e.parameter.functionName == "呼び出したい関数名その2"){

呼び出したい関数その2(e.parameter);

return ReturnResult("処理完了");

}

function 呼び出したい関数その1(param){

// やりたいことを書く

}

function 呼び出したい関数その2(param){

// やりたいことを書く

}

function ReturnResult(ret){

//結果はJSONで返す

//JSONオブジェクト格納用の入れ物

let JsonData = {};

JsonData.value = ret;

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

}

ということで外部からデータを二次元配列で渡せばスプレッドシートに書き込めるのではないかと考えていたのですが、実際にやってみるとうまくいきません。

GASは外部からデータを二次元配列で渡せない

const sheetName = '書き込みたいシート名';

function SetCellValues(array){
    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spreadsheet.getSheetByName(sheetName);
    sheet.getRange(1,1, array.length, array[0].length).setValues(array);
}

const sheetName = '書き込みたいシート名';

function SetCellValues(array){

let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

let sheet = spreadsheet.getSheetByName(sheetName);

sheet.getRange(1,1, array.length, array[0].length).setValues(array);

}

上記のコードは

function function1(){
    let array = [['A1に書きたいこと', 'B1に書きたいこと'],['A2に書きたいこと', 'B2に書きたいこと']];
    SetCellValues(array)
}

function function1(){

let array = [['A1に書きたいこと', 'B1に書きたいこと'],['A2に書きたいこと', 'B2に書きたいこと']];

SetCellValues(array)

}

のように内部で実行する場合はうまくいきますが、外から実行しようとするとSetCellValues関数に渡された配列がundefinedになってしまいます。これは仕様なのでどうすることもできません。

どうしても二次元配列をGASに渡したい場合

ではどうするのかというと配列で渡せないなら文字列で渡します。そしてGASの内部で二次元配列に展開して自作関数SetCellValuesに渡します。

たとえば以下のようなDataクラスのリストがあったとします。

public class Data
{
    public string A = "";
    public string B = "";
    public string C = "";
    public string D = "";
}

List<Data> datas = new List<Data>();
datas.Add(new Data { A = "A1の内容", B = "B1の内容", C = "C1の内容", D = "D1の内容" });
datas.Add(new Data { A = "A2の内容", B = "B2の内容", C = "C2の内容", D = "D2の内容" });
datas.Add(new Data { A = "A3の内容", B = "B3の内容", C = "C3の内容", D = "D3の内容" });

public class Data

{

public string A = "";

public string B = "";

public string C = "";

public string D = "";

}

List<Data> datas = new List<Data>();

datas.Add(new Data { A = "A1の内容", B = "B1の内容", C = "C1の内容", D = "D1の内容" });

datas.Add(new Data { A = "A2の内容", B = "B2の内容", C = "C2の内容", D = "D2の内容" });

datas.Add(new Data { A = "A3の内容", B = "B3の内容", C = "C3の内容", D = "D3の内容" });

これをスプレッドシートに書き込むためには以下の方法でPostします。

やろうとしていることは二次元配列では送れないのでひとつの文字列に変換しています。各列にセットしたい文字列を改行で区切り、次の行に書き込みたい場合は連続する二つの改行で区切ります。そのため各セルにセットする文字列のなかには改行は存在しないというのが前提です。

それからサンプルプログラムのなかにはWebClientを最後にdisposeしたり、usingで囲っているものがありますが、これはよくないそうです。これに関してはHttpClientをusingで囲わないでくださいを参照してください。

using System.Collections.Specialized;
using System.Net;

public partial class Form1 : Form
{
    string endpoint = "スクリプトをwebアプリとしてデプロイしたときに発行されるUrl";
    WebClient WebClient = new WebClient();

    private void button1_Click(object sender, EventArgs e)
    {
        // リストを文字列に変換する
        List<string> Vs = new List<string>();
        foreach (Data data in datas)
        {
            List<string> vs = new List<string>();
            vs.Add(data.A);
            vs.Add(data.B);
            vs.Add(data.C);
            vs.Add(data.D);
            Vs.Add(String.Join("\n", vs.ToArray()));
        }
        string stringedArray = String.Join("\n\n", Vs.ToArray());

        // Postする（WebClientはPostするたびにDisposeしてはならない）
        // Gasで自作したSetCellValuesStringedArray関数にstringedArrayを渡す
        NameValueCollection collection = new NameValueCollection();
        collection.Add("functionName", "SetCellValuesStringedArray");
        collection.Add("stringedArray", stringedArray);
        byte[] resBytes = WebClient.UploadValues(endpoint, collection);
        MessageBox.Show(Encoding.UTF8.GetString(resBytes));
    }
}

using System.Collections.Specialized;

using System.Net;

public partial class Form1 : Form

{

string endpoint = "スクリプトをwebアプリとしてデプロイしたときに発行されるUrl";

WebClient WebClient = new WebClient();

private void button1_Click(object sender, EventArgs e)

{

// リストを文字列に変換する

List<string> Vs = new List<string>();

foreach (Data data in datas)

{

List<string> vs = new List<string>();

vs.Add(data.A);

vs.Add(data.B);

vs.Add(data.C);

vs.Add(data.D);

Vs.Add(String.Join("\n", vs.ToArray()));

}

string stringedArray = String.Join("\n\n", Vs.ToArray());

// Postする（WebClientはPostするたびにDisposeしてはならない）

// Gasで自作したSetCellValuesStringedArray関数にstringedArrayを渡す

NameValueCollection collection = new NameValueCollection();

collection.Add("functionName", "SetCellValuesStringedArray");

collection.Add("stringedArray", stringedArray);

byte[] resBytes = WebClient.UploadValues(endpoint, collection);

MessageBox.Show(Encoding.UTF8.GetString(resBytes));

}

GAS側では以下のようにすればスプレッドシートに書き込むことができます。ただ最後にSetCellValues関数を呼び出していますが、sheet.getRange関数で指定した部分とsetValues関数で渡した配列のサイズが合わないとエラーになります。

function doPost(e){
    if (e == undefined || e.parameter == undefined)
        return ReturnResult("不正なパラメータ");

    if(e.parameter.functionName == "SetCellValuesStringedArray"){
        SetCellValuesStringedArray(e.parameter);
        return ReturnResult("完了：SetCellValuesStringedArray関数");
    }

    if(e.parameter.functionName == "他にも呼び出したい関数"){
        他にも呼び出したい関数(e.parameter);
        return ReturnResult("処理完了");
    }
}

function SetCellValuesStringedArray(param){
    let str = param.stringedArray;
    let rows = str.split('\n\n');

    let array = [];
    for(let i=0; i<rows.length; i++){
        let tempArray = rows[i].split('\n');
        array.push(tempArray);
    }
    SetCellValues(array);
}

function SetCellValues(array){
    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spreadsheet.getSheetByName(sheetName);
    sheet.getRange(1,1, array.length, array[0].length).setValues(array);
}

function doPost(e){

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

return ReturnResult("不正なパラメータ");

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

SetCellValuesStringedArray(e.parameter);

return ReturnResult("完了：SetCellValuesStringedArray関数");

}

if(e.parameter.functionName == "他にも呼び出したい関数"){

他にも呼び出したい関数(e.parameter);

return ReturnResult("処理完了");

}

function SetCellValuesStringedArray(param){

let str = param.stringedArray;

let rows = str.split('\n\n');

let array = [];

for(let i=0; i<rows.length; i++){

let tempArray = rows[i].split('\n');

array.push(tempArray);

}

SetCellValues(array);

}

function SetCellValues(array){

let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

let sheet = spreadsheet.getSheetByName(sheetName);

sheet.getRange(1,1, array.length, array[0].length).setValues(array);

}

C#ではありえないJavaScriptならではのハマりどころ

他にハマったことがあって配列をそのままではなくソートしてから書き込みたい場合、

7,000
4.5万
10万

実際には 7,000 ＜ 4.5万＜ 10万なのですが、このままでは文字列として処理されるので期待した結果が得られません。そこで文字列を数値に変換します。この方法は「10万2400」のようなときに使うとおかしなことになります。最後は「万」で終わっていることが前提です。

function StringToNumber(str){
    str = str.replace(/,/g ,'');

    if(str.match('万')) {
        str = str.replace('万', '');
        let num = Number(str);
        return num * 10000;
    }
    return Number(str);
}

function StringToNumber(str){

str = str.replace(/,/g ,'');

if(str.match('万')) {

str = str.replace('万', '');

let num = Number(str);

return num * 10000;

}

return Number(str);

}

ここで注意しなければならないのはうっかりすでに数値に変換されているものをまたStringToNumberに渡すとエラーが発生することです。数値なのでreplaceという関数は存在しないと怒られます。

以下のような失敗をしてしまいました。同じ変数にstrig型でもNumber型でも代入できてしまうところがJavaScriptの怖いところです。C#の場合はコンパイル段階でエラーになるのでやらかしてもすぐに気がつきます。

let a = '10人';
a = a.replace('人' ,'');
a = StringToNumber(a);

// ほかにいろいろな処理

// すでにNumber型に変換されていることをコロッと忘れて事故発生
a = StringToNumber(a);

let a = '10人';

a = a.replace('人' ,'');

a = StringToNumber(a);

// ほかにいろいろな処理

// すでにNumber型に変換されていることをコロッと忘れて事故発生

a = StringToNumber(a);

// 文字列型の変数が格納されていた変数にNumber型を再代入するのはやめましょう
let str = '10人';
str = str.replace('人' ,'');
let num = StringToNumber(a);

// 文字列型の変数が格納されていた変数にNumber型を再代入するのはやめましょう

let str = '10人';

str = str.replace('人' ,'');

let num = StringToNumber(a);

またNumber型に変換できない文字列を渡すと例外が発生すると思い込んでいたのですが、実際にはNaNが返されます。

あとJavaScriptを使っている人なら周知のことと思いますが、この場合「同じ」と表示されます。C#ばっかり使っていると不思議に見えますが、JavaScriptだと同じと判定されます。違うと判定させるためには if(a === b)としなければなりません。動的型付け言語の便利だけどややこしい部分です。

let a = 46 + 2;
let b = '4' + '8';
if(a == b)
    console.log('同じ');
else
    console.log('違う');

let a = 46 + 2;

let b = '4' + '8';

if(a == b)

console.log('同じ');

else

console.log('違う');

let x = 'AKB48';
x = x.replace('AKB', '');
console.log(typeof(x)); // x は文字列であることが確認できる

x -= 2; // x は文字列なのに引き算ができる
x = '乃木坂' + x;
console.log(x); // 乃木坂46と出力される

let x = 'AKB48';

x = x.replace('AKB', '');

console.log(typeof(x)); // x は文字列であることが確認できる

x -= 2; // x は文字列なのに引き算ができる

x = '乃木坂' + x;

console.log(x); // 乃木坂46と出力される

スクレイピングしたデータをGASを使ってスプレッドシートに書き込むのは簡単と思っていたら意外に難儀した話

GASを外部から実行させる

GASは外部からデータを二次元配列で渡せない

どうしても二次元配列をGASに渡したい場合

C#ではありえないJavaScriptならではのハマりどころ

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

コメントについて

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

コメントをどうぞ