[GAS]LINE -> スプレッドシート指定した箇所に反映させる

前回の記事はこちら↓

前回の概要
つくるもの
ステップ1: スプレッドシートの準備
ステップ2: Google Apps Scriptの設定
ステップ3: 簡易テスト
綺麗な表じゃない時
ステップ4:デプロイして、LINEからメッセージを送る

前回の概要

前回の記事では、LINEで送信した内容をスプレッドシートに反映させるまでの流れを実施しました。

基本的な流れを掴んだら、次は自分好みの形に整えていきましょう！
特定のセルに動的に配置できれば、データを見やすく整理できるので、分析しやすくなります。

この記事では、スプレッドシートにいい感じに反映させるためのコードを書いていきます。

つくるもの

LINEからのメッセージを送ったらスプレッドシードの末尾に反映されていく
メッセージ内容は “値段、商品名、リンク” とし、A列に値段、B列に商品名、C列にリンクが反映される

ステップ1: スプレッドシートの準備

まずは、Googleスプレッドシートを用意します。
今回は例として、料金、商品名、リンクの3つの列を設定してみました。
これらの列にそれぞれ、LINEから受け取ったデータを入力します。

ステップ2: Google Apps Scriptの設定

拡張機能タブからApps Scriptを開き、新しいプロジェクトを作成します。

doPost関数内に全コードをベタ書きしても良いですが、少しでも見栄えをよくするため関数を別で二つ作成して、doPost関数内で呼び出します。

doPost関数

まずはLINEからのメッセージを受け取って、textにするまでの処理

function doPost(e) {
  var events = JSON.parse(e.postData.contents).events;
  var text = events[0].message.text; // LINEから受け取ったメッセージ
}

splitText関数

次に上記のtextを引数として受け取った後、textを３分割にして、それぞれの変数に格納する関数を追加。

今回はメッセージとして”値段、商品名、リンク” を想定しているので、カンマでメッセージを分割する。

function doPost(e) {
  var events = JSON.parse(e.postData.contents).events;
  var text = events[0].message.text; // LINEから受け取ったメッセージ
}
  

function splitText(text) {
  var parts = text.split('、'); 
  if (parts.length !== 3) {
    return { error: 'メッセージの形式が正しくありません。' };
  }
  return {
    price: parts[0].trim(),
    product: parts[1].trim(),
    url: parts[2].trim()
  };
}

saveSpreadsheet関数

splitText関数からの結果を受け取りスプレッドシートに反映する関数

function doPost(e) {
  var events = JSON.parse(e.postData.contents).events;
  var text = events[0].message.text; // LINEから受け取ったメッセージ
}
  

function splitText(text) {
  var parts = text.split('、'); 
  if (parts.length !== 3) {
    return { error: 'メッセージの形式が正しくありません。' };
  }
  return {
    price: parts[0].trim(),
    product: parts[1].trim(),
    url: parts[2].trim()
  };
}

function saveSpreadsheet(price, product, url){
  var SHEET_ID = 'SPREADSHEET_ID' // スプレッドシートのIDを設定
  var SHEET_NAME = 'Sheet1' // シート名を入力
  var spreadsheet = SpreadsheetApp.openById(SHEET_ID); 
  var sheet = spreadsheet.getSheetByName(SHEET_NAME); ;

  // スプレッドシートにデータを追加
  sheet.appendRow([price, product, url]);
}

doPost関数内でsplitText関数とsaveSpreadsheet関数を呼び出す

doPost関数にそれぞれの関数を呼び出す2行を追加する

function doPost(e) {
  var events = JSON.parse(e.postData.contents).events;
  var text = events[0].message.text; // LINEから受け取ったメッセージ
  var result = splitText(text)
  saveSpreadsheet(result.price, result.product, result.url)
}
  

function splitText(text) {
  var parts = text.split('、'); 
  if (parts.length !== 3) {
    return { error: 'メッセージの形式が正しくありません。' };
  }
  return {
    price: parts[0].trim(),
    product: parts[1].trim(),
    url: parts[2].trim()
  };
}

function saveSpreadsheet(price, product, url){
  var SHEET_ID = 'SPREADSHEET_ID' // スプレッドシートのIDを設定
  var SHEET_NAME = 'Sheet1' // シート名を入力
  var spreadsheet = SpreadsheetApp.openById(SHEET_ID); 
  var sheet = spreadsheet.getSheetByName(SHEET_NAME); ;

  // スプレッドシートにデータを追加
  sheet.appendRow([price, product, url]);
}

ステップ3: 簡易テスト

わざわざデプロイして、LINEで確認しても良いですが、エラーがあるとめんどくさいので、
ここでできるテストはしてしまいましょう。

先程のコードの2,3行目はLINEからのメッセージを処理する部分なので、一旦コメントアウトして、
適当なメッセージをtextに代入します。

function doPost(e) {
  // var events = JSON.parse(e.postData.contents).events;
  // var text = events[0].message.text; // LINEから受け取ったメッセージ
  var text = "¥100、ペン、https://~~"
  var result = splitText(text)
  saveSpreadsheet(result.price, result.product, result.url)
}
  

function splitText(text) {
  var parts = text.split('、'); 
  if (parts.length !== 3) {
    return { error: 'メッセージの形式が正しくありません。' };
  }
  return {
    price: parts[0].trim(),
    product: parts[1].trim(),
    url: parts[2].trim()
  };
}

function saveSpreadsheet(price, product, url){
  var SHEET_ID = 'SPREADSHEET_ID' // スプレッドシートのIDを設定
  var SHEET_NAME = 'Sheet1' // シート名を入力
  var spreadsheet = SpreadsheetApp.openById(SHEET_ID); 
  var sheet = spreadsheet.getSheetByName(SHEET_NAME); ;

  // スプレッドシートにデータを追加
  sheet.appendRow([price, product, url]);
}

できたら、タブの実行ログボタンをアクティブにしてから、実行ボタンをクリック

正常に動作していれば、実行完了ログが見られます。

スプレッドシートを確認すると

無事に反映できていそうです！　

綺麗な表じゃない時

実務だともっと列がたくさんあって、この列のここに入れたいみたいな時があるかと思います。
例えば、下記のような感じ（めっちゃ横着しました。）

この状態で、先程のコードを実行するとこうなります。

入れたかったのはそこじゃない！！ってことが起こりうるので、状況に応じて対処できるようにしましょう。

今回はA,B,C列全部が空白になっている行の末尾を探して、そこにデータを挿入するコードを書きます　
変更箇所はsaveSpreadsheet関数のみです。

function saveSpreadsheet(price, product, url){
  var SHEET_ID = 'SPREADSHEET_ID' // スプレッドシートのIDを設定
  var SHEET_NAME = 'Sheet1' // シート名を入力
  var spreadsheet = SpreadsheetApp.openById(SHEET_ID); 
  var sheet = spreadsheet.getSheetByName(SHEET_NAME); ;

  var dataRange = sheet.getDataRange(); // シートのデータ範囲を取得
  var values = dataRange.getValues(); // データ範囲の値を取得

  // A列、B列、C列のいずれかが空の最初の行を探す
  var rowToEdit = null;
  for (var i = 0; i < values.length; i++) {
    // C列、D列、G列の値を確認
    var priceCell = values[i][0]; // A列
    var productCell = values[i][1]; // B列
    var urlCell = values[i][2]; // C列
    // すべての対象列が空であれば、その行番号を記録
    if (!priceCell && !productCell && !urlCell) {
      rowToEdit = i + 1; // 行番号は0ではなく1から始まるため、+1する
      break; // 最初に見つかった空の行でループを抜ける
    }
  }

  var lastRow = sheet.getLastRow(); // シートの最終行を取得
  var nextRow = lastRow + 1; // データを追加する行（最終行の次）

  // 空の行が見つかった場合はデータを挿入
  if (rowToEdit !== null) {
    sheet.getRange(rowToEdit, 1).setValue(price); // A列
    sheet.getRange(rowToEdit, 2).setValue(product); // B列
    sheet.getRange(rowToEdit, 3).setValue(url); // C列
  } else {
    // 空の行が見つからない場合は、シートの末尾に行を追加してデータを挿入
    sheet.appendRow([price, product, url]);
  }
}

かけたら実行しましょう！

　こんな感じで無事に入れたいところに入れることができました！！

ステップ4:デプロイして、LINEからメッセージを送る

~~書き疲れたので、~~前回の記事を参考にテストしてみてください。