LINE予約「LUCA」の機能と料金はこちら

Google Sheets APIのappendが途中に挿入される条件と確実な書き込み方

実務ノート 実務ノート 公開

Google スプレッドシートへ記録を「末尾に1行ずつ足す」処理は、業務の自動化でよく使います。Google Sheets API の append(values:append)を使うと、どの行から書くかは Google が決めます。その位置は、こちらが渡す範囲(range)の書き方で変わり、表と表のあいだに書かれることもあります。

この記事の結論

  • append は、渡した範囲の中から Google が「表」を探し、その表の次の行に書きます。どの表の次に書くかは、渡す範囲で変わります。
  • 公式ガイドの例(A1:C2 と B4:D6 の2つの表)では、範囲の中に表がいくつかあると、最後の表の次の B7 から書きます。範囲の中に上の表しか無い A1 を渡すと、表と表のあいだの A3 から書きます。
  • 既定(OVERWRITE)では、書く場所にあるデータを上書きします。INSERT_ROWS にすると、新しいデータのために行を差し込みます。
  • 範囲の書き方ひとつで位置が変わる仕組みに頼らず、読んで最終行を数え、範囲を明示して PUT で書くと確実です。

実際に起きたこと

弊社で動かしているワークフローの一つで、共有のスプレッドシートの「更新ログ」のタブへ作業の記録を追記していました。追記は、範囲 A:G を渡す append の形で考えていました。

このタブの並びは、上から「ログ行 → 空行 → 【記録のルール】のまとまり → またログ行」でした。つまり、途中に空行が挟まり、データのまとまりがいくつかに分かれています。

10日分をまとめて追記する直前に、append では書く場所を Google が決めることに気づきました。そこで、append に書く場所を決めさせない書き方に変えました。実際に append で書き込む前だったので、記録が崩れる事故にはなっていません。

公式ガイドの例に当てはめると、A:G のように両方のまとまりを含む範囲なら、最後のまとまりの次に書かれる側です。弊社はこの範囲で append を試していないので、実際にどこへ入ったかは確かめていません。それでも、範囲の書き方を少し変えるだけで書く位置が変わる仕組みには頼らないと決めました。

このシートでは以前、行番号を推測して書き込み、既存の2ブロックを上書きする事故を起こしています。同じシートで2度目の事故を起こさないことを優先しました。

原因

append は、渡した範囲の中で「表」を探す

Google の公式リファレンスでは、append の range は「データの論理的な表を探す範囲」と説明されています。append は、その範囲の中で既存のデータを探して「表」を見つけ、その表の次の行に、表の最初の列からそろえて書きます。

ここでいう「表」は、データがひとかたまりになっている部分です。あいだに空行があれば、上と下は別の表になります。

どんな範囲を渡すと途中に入るか

公式ガイドには、1枚のシートに A1:C2 と B4:D6 の2つの表がある例が載っています。あいだの行が空いていて、表が上下に分かれている形です。渡す範囲ごとに、書き始める位置は次のようになります。

渡す範囲 書き始める位置 ガイドの説明
Sheet1(シート全体) B7 シートの全データを調べ、B4:D6 を最後の表と判断する
B4 または C5:D5 B7 どちらも B4:D6 の表の中にあるから
B2:D4 B7 範囲の中の最後の表が B4:D6 だから(A1:C2 の表も含んでいても)
A3:G10 B7 範囲の中の最後の表が B4:D6 だから(範囲が表より前から始まり、後ろで終わっていても)
A1 A3 A1 は A1:C2 の表の中にあるから
E4 E4 どの表の中にもないから
A4 A4 E4 と同じ理由

注目したいのは B2:D4 の行です。書き出しの B2 は上の表 A1:C2 の中にあります。それでも、範囲の中の最後の表が B4:D6 なので、下の表の次の B7 から書きます。書き出しが上の表の中にあるだけでは、表と表のあいだには入りません。

表と表のあいだに書かれるのは、A1 の行です。A1 の1マスだけを渡すと、範囲の中には上の表 A1:C2 しかありません。そのため、上の表の次の A3、つまり2つの表のあいだから書きます。

ガイドの例から読めるのは、「渡した範囲の中で最後の表の次に書く」ということです。範囲に下の表がかかっていれば下の表の次に、上の表しかかかっていなければ上の表の次に書きます。どの表にもかかっていない位置(E4 や A4)を渡したときは、その位置からそのまま書きます。途中に空行があるかどうかより、どの範囲を渡したかで決まります。

既定では、表の後ろを上書きする

append には insertDataOption(書くときに行を差し込むかどうかの指定)があります。公式ガイドでは、既定では表の後ろにあるデータを上書きすると説明されています。

  • OVERWRITE:書く場所にあるデータを上書きします。
  • INSERT_ROWS:新しいデータのために行を差し込みます。

上の例で A1 を渡し、A3 から何行も書くと、すぐ下の B4:D6 の表のマスにかかることがあります。既定のままなら、そのマスにあったデータは上書きされます。INSERT_ROWS にすれば、新しいデータのために行が差し込まれるので、上書きは避けられます。

公式ガイドでも、この指定は「表の後ろのデータを上書きするか、新しい行を差し込むか」を選ぶものとして書かれています。どの表の次に書くかは、前の節のとおり、渡す範囲の例で説明されています。末尾に足したつもりの記録が表と表のあいだに入るときは、まず範囲の渡し方を見直します。

n8n の Google Sheets ノードの場合

ここからは、n8n 2.19.2 に入っている n8n-nodes-base のソースで確かめた内容です。お使いの版が違うときは、同じ箇所をご自分の環境でも確かめてください。

Google Sheets ノードの Append の処理は、まずシートのデータを読み、読めた行の数に1を足した行を書く行にしています。そして、シートの末尾に空の行を1行足してから書きます。

const sheetData = await sheet.getData(range, 'FORMATTED_VALUE');
// …
//if no trailing empty row exists in the sheet update operation will fail
await sheet.appendEmptyRowsOrColumns(sheetId, 1, 0);
// …
const lastRow = (sheetData ?? [{}]).length + 1;

末尾に空の行を足すのは、末尾に空行がないと書き込みが失敗するからだと、ソースのコメントに書かれています。なお、Google の値の読み取りでは、末尾の空行と空列は応答に含まれません。

書く範囲は、計算した行から足す行の数だけの「シート名!開始行:終了行」として組み立てます。既定ではこの範囲に PUT(指定した範囲の値を書き換える書き方)で書きます。

const range = `${name}!${row}:${rowsLength ? row + rowsLength - 1 : row}`;

オプションの「Minimise API Calls」をオンにすると、動きが少し変わります。先に数えた行の番号は渡しません。書く処理の中でシートをもう一度読み、その行の数に1を足した行から範囲を組み立てます。そして、その範囲を PUT ではなく Google の append(POST の :append)に渡します。

async appendData(range, data, valueInputMode, lastRow, useAppend) {
    const lastRowWithData = lastRow ||
        ((await this.getData(range, 'UNFORMATTED_VALUE')) || []).length + 1;
    // …
}
// …
if (useAppend) {
    response = await transport_1.apiRequest.call(this.executeFunctions, 'POST', `/v4/spreadsheets/${this.id}/values/${this.encodeRange(range)}:append`, body, query);
}

この設定は既定ではオフです。

displayName: 'Minimise API Calls',
name: 'useAppend',
type: 'boolean',
default: false,
hint: 'Use if your sheet has no gaps between rows or columns',

n8n の画面には「行や列に隙間のないシートで使う」というヒントが出ます。説明文にも「場合によってはデータがずれることがある」と書かれています。n8n 自身が隙間のないシート向けとしているので、途中に空行のあるシートではオフのままにしておきます。

直し方

追記は次の4手で書きます。考え方は一つで、append に書く場所を決めさせないことです。ここでは書き込み先のタブの名前を Log、列を A〜G、スプレッドシートの ID を XXXX としています。

先に読む

まず、書き込み先のタブの中身を読みます。読み取り範囲は、いまの行数より十分に広くとります。

GET https://sheets.googleapis.com/v4/spreadsheets/XXXX/values/Log!A1:G500

n8n の Code ノードから呼ぶときは、fetch ではなく this.helpers.httpRequest で呼びます。理由は「n8nのCodeノードで「fetch is not defined」が出る原因と直し方」に書きました。

最終行を数える

読んだ実物から、対象の列のどれかに中身がある最後の行を数えます。行番号は、人の入力やプロンプトから受け取りません。推測した行番号で書いたことが、以前の上書き事故の原因でした。

// values は GET で読んだ応答の values(A1 から読んだ2次元配列)
// シートが空だと応答に values が入らないので、空の配列として受ける
function lastFilledRow(values) {
  let last = 0;
  (values || []).forEach((row, i) => {
    if ((row || []).some(cell => String(cell ?? '').trim() !== '')) last = i + 1;
  });
  return last;
}

const rows = resp.values || [];
const n = lastFilledRow(rows); // 最終行
const m = newRows.length;      // 足す行の数

書き込み先が空かを確かめる

書く予定の行が空かを、読んだ実物で確かめます。1マスでも埋まっていたら、書かずに中止して行番号を返します。読み取り範囲の端に近づいたときも、黙って切れる前に止めて、範囲を広げます。

// 読み取り範囲(500行)の端に近づいたら、書かずに止める
if (n + m > 480) {
  throw new Error(`読み取り範囲の端に近いので中止(最終行 ${n})`);
}

// 書き込み先(n+1〜n+m 行目)が空かを確かめる
for (let r = n + 1; r <= n + m; r++) {
  const row = rows[r - 1] || [];
  if (row.some(cell => String(cell ?? '').trim() !== '')) {
    throw new Error(`${r} 行目が空ではないので中止`);
  }
}

const range = `Log!A${n + 1}:G${n + m}`;

いまの数え方では、最終行より下は必ず空なので、この確認は必ず通ります。これは、あとで最終行を特定の列だけで数えるように変えたときの保険です。ほかの列に残った中身を上書きする前に止まります。

この確認が見ているのは、読んだ時点の実物だけです。読んでから書くまでのあいだに、別の処理が同じシートへ書いた場合までは見ていません。

範囲を明示して PUT する

最後に、計算した範囲を明示して PUT で書きます。append は使いません。

PUT https://sheets.googleapis.com/v4/spreadsheets/XXXX/values/Log!A{n+1}:G{n+m}?valueInputOption=USER_ENTERED

{
  "range": "Log!A{n+1}:G{n+m}",
  "values": [
    ["…", "…", "…", "…", "…", "…", "…"]
  ]
}

valueInputOption は、送った値をどう入れるかの指定です。{n+1} と {n+m} には、前の手で計算した数を入れます。

直ったかを確かめる

  • PUT の応答の updatedRange が、狙った範囲(A{n+1}:G{n+m})と同じかを見ます。
  • 書いた後にもう一度読み直し、最終行が n+m になっているかを数えます。
  • 途中の空行の上にあるログ行と、【記録のルール】のようなまとまりが、書く前と同じ行のまま残っているかを見ます。

append の動きを確かめたいときは、応答の tableRange を見ます。公式リファレンスでは「値を足す相手の表の範囲(足す前のもの)」で、表が見つからなければ空と説明されています。Google がどの表を選んだかが分かります。試すときは、本番のシートではなく写しで行ってください。

それでも直らないとき

  • 読み取り範囲が足りていない:ログが増えて読み取り範囲の端に近づくと、最終行を数え違えます。範囲を広げてから書き直してください。
  • append のまま使いたい:渡している範囲に、足したい表(いちばん下のまとまり)がかかっているかを見ます。公式ガイドの例では、範囲の中に上の表しか無い A1 を渡すと、上の表のすぐ下に書かれました。応答の tableRange で、狙った表が選ばれているかも確かめます。
  • n8n のノードのまま使いたい:「Minimise API Calls」がオフになっているかを見ます。見出しの行が1行目でない場合は、オプションの Header Row で見出しの行を指定します。n8n のソースでは、この値は「見出しの行の番号で、1から数える」と説明されています。
  • ほかのワークフローが同じシートに append している:弊社でも、append を使っている既存のワークフローは、まだ全部を確かめ終えていません。対象のシートに空行があるかを見るまでは、「危ない」とも「安全」とも決めずに、シートの実物を開いて確かめてください。

「0件」や「書けた」という結果を、相手のサービスの判定にそのまま任せないことは、Airtable でも同じでした。「Airtable filterByFormulaでリンク先のレコードIDが0件になる原因と回避策」もあわせてご覧ください。

まとめ

  • Google Sheets API の append は、渡した範囲の中から Google が「表」を探し、その次の行に書きます。
  • 範囲の中に表がいくつかあると、最後の表の次に書きます。公式ガイドの例では、範囲の中に上の表しか無い A1 を渡したときに、表と表のあいだに書かれます。
  • 既定(OVERWRITE)では書く場所のデータを上書きします。上書きを避けたいときは INSERT_ROWS で行を差し込みます。
  • n8n の Google Sheets ノードは、「Minimise API Calls」をオンにしたときに append を使います。隙間のあるシートではオフのままにします。
  • 確実に足したいときは、自分で最終行を数え、書く範囲を自分で決めて PUT します。書いた後は updatedRange と、読み直した最終行で確かめます。

参考にした公式ドキュメント

n8nの点検と構築のご相談

「スプレッドシートへの記録が思った行に入らない」といった、n8nとGoogleのサービスをつなぐ処理の点検や、業務の自動化の仕組みづくりをお引き受けしています。

お問い合わせはこちら

この記事を書いた人

貫名 孝夫(マルタマーケティング株式会社 代表取締役)。n8nで140本を超えるワークフローを本番で動かしながら、実際に起きた不具合と直し方を記録しています。会社概要