Zenn
🕋

Google Sheets API v4で行を挿入する

2021/06/01に公開
C#
Google
Google Sheets
sheetsapi
tech

概要

スプレッドシートの指定した位置に行を挿入する方法です。

末尾に挿入するのは簡単ですが、途中に挿入するのがつまづきました。

Sheets APIは巷にたくさん情報がありますが、毎度案外と欲しい情報にたどり着けない気がします。ゆえに書き残します。

結論

以下3ステップです。

  1. シートID (整数値) を取得
  2. あらかじめ空行を足す
  3. 空行に値を書き込む

2と3を同時に行う方法もあり、最後に示します。

環境

1. シートIDを取得

スプレッドシートは複数のシートから構成され、それぞれのシートには名前がついています。その名前から、ID(整数値)を取得します。これは次のステップで必要になってきます。

ここで spreadsheetId は、URLの https://docs.google.com/spreadsheets/d/ に続く後ろの部分です。

APIの仕様: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get

int? GetSheetId(SheetsService sheetsService, string spreadsheetId, string sheetTitle)
{
    var sheetsInfoRequest = sheetsService.Spreadsheets.Get(spreadsheetId);
    var sheetsInfo = sheetsInfoRequest.Execute();

    var sheet = sheetsInfo.Sheets
        .Select(s => s.Properties)
        .FirstOrDefault(p => p.Title == sheetTitle);
    if (sheet is null)
        throw new Exception($"Sheet '{sheetTitle}' not found"); // 本当は何か別の例外クラスで... 以降も同様
    return sheet.SheetId;
}

ここを参考にしました: https://github.com/googleapis/google-api-nodejs-client/issues/1910

2. 空行を挿入

指定した行の位置に空行を入れるメソッドを作ります。ここでシートIDが生きてきます。

例えば2行目に1行分足したければ startIndex=1, endIndex=2 とします。複数行足すこともでき、10行目に20行分足したいなら startIndex=9, endIndex=29 とします。

APIの仕様: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

BatchUpdateSpreadsheetResponse InsertBlankLines(
    SheetsService sheetsService,
    string spreadsheetId,
    string sheetTitle,
    int startIndex,
    int endIndex)
{
    var sheetId = GetSheetId(sheetsService, spreadsheetId, sheetTitle)
        ?? throw new Exception("SheetID not found");

    var requestBody = new BatchUpdateSpreadsheetRequest
    {
        Requests = new[]
        {
            new Request
            {
                InsertDimension = new InsertDimensionRequest
                {
                    Range = new DimensionRange
                    {
                        Dimension = "ROWS",
                        SheetId = sheetId,
                        StartIndex = startIndex,
                        EndIndex = endIndex
                    },
                    InheritFromBefore = false
                },
            }
        }
    };

    var request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
    var response = request.Execute();
    return response;
}

3. 空行に値を書き込む

ここまで作成したメソッドを使って、目的を達します。

値の書き込みを行う方法は複数ありますが、今回は values.append が最も手軽と考えています。上から見ていって最初に出現した空行に書き込みます。[1]

以下では、2行目に空行を挿入し、そこに1, 2, 3を左から順に書き込みます。

const string spreadsheetId = "abcdefghijk...";
const string sheetName = "<your sheet name>";

var sheetsService = new SheetsService(new BaseClientService.Initializer
{
    HttpClientInitializer = GetCredential(), // 省略
    ApplicationName = "<Your Application Name>",
});

// 空行を2行目に挿入
InsertBlankLines(sheetsService, spreadsheetId, sheetName, 1, 2);

// 挿入した空行に値を設定		
var valueRange = new ValueRange
{
    MajorDimension = "ROWS",
    Values = new List<IList<object>> { new object[]{1, 2, 3} },
};
var request = sheetsService.Spreadsheets.Values.Append(
    body: valueRange,
    spreadsheetId: spreadsheetId,
    range: $"{sheetName}!A2");
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.OVERWRITE;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
request.Execute();

ここで、InsertDataOptionEnum には他に INSERT_ROWS という値もあります(参考)。そこで当初はINSERT_ROWSを使えばこのValues.Append一発で希望を満たせると見込んでいたのですが、表の途中への挿入はできないようでした。必ず末尾に挿入してしまいます。

別解: 空行挿入と値の設定を同時に行う

batchUpdate は、以下示すように膨大な機能があり、それを複数同時にリクエストに含められます。

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#Request

そこで、空行の挿入と値の追加を1つのリクエストに含めてみます。以下は、2行目に1行分挿入して書き込む例です。

const string spreadsheetId = "abcdefghijk...";
const string sheetName = "<your sheet name>";

var sheetsService = new SheetsService(new BaseClientService.Initializer
{
    HttpClientInitializer = GetCredential(), // 省略
    ApplicationName = "<Your Application Name>",
});

var sheetId = GetSheetId(sheetsService, spreadsheetId, sheetName)
    ?? throw new Exception("SheetID not found");

var requestBody = new BatchUpdateSpreadsheetRequest
{
    Requests = new[]
    {
        new Request
        {
            InsertDimension = new InsertDimensionRequest
            {
                Range = new DimensionRange
                {
                    Dimension = "ROWS",
                    SheetId = sheetId,
                    StartIndex = 1,
                    EndIndex = 2
                },
                InheritFromBefore = false
            },
        },
        new Request
        {
            UpdateCells = new UpdateCellsRequest
            {
                Range = new GridRange
                {
                    SheetId = sheetId,
                    StartRowIndex = 1,
                    EndRowIndex = 2,
                    StartColumnIndex = 0
                },
                Rows = new[]
                {
                    new RowData
                    {
                        Values = new []
                        {
                            new CellData{ UserEnteredValue = new ExtendedValue{ NumberValue = 1 } },
                            new CellData{ UserEnteredValue = new ExtendedValue{ NumberValue = 2 } },
                            new CellData{ UserEnteredValue = new ExtendedValue{ NumberValue = 3 } },
                        }
                    }
                },
                Fields = "userEnteredValue"
            }
        }
    }
};

var request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
var response = request.Execute();

書き込む操作を事細かに指定できます。言い換えると長々とした記述を要します。効率は良いはずですが使い方が難しいので、一概にこれが最適とは言えないかもしれません。

脚注

  1. 言い換えれば、今回挿入した空行以外にすでに空行があると狂います。 ↩︎

Discussion