Googleフォームのアンケート結果をBacklogに自動で課題登録する

Googleアンケートで収集した情報を（例えば）backlogに自動登録して，課題作成の手間を削減すると言った運用って意外と多いんじゃないでしょうか。私はそれなりに使っているので，とても簡単ですが備忘録代わりに紹介したいと思います。

GASで使用するSpreadsheetを指定する

Googleフォームの使用方法は割愛します。
フォームの回答で自動生成されるSpreadsheetからスクリプトエディタを使ってコードを書いていきます。

// 使用するSpreadsheetの指定 
const targetSpreadSheet = SpreadsheetApp.openById("生成されたSpreadsheetのID");
// Spreadsheet中のシートの指定 
const targetSheet = targetSpreadSheet.getSheetByName("フォームの回答 1");
// 初期値の取得  
const targetValues = targetSheet.getDataRange().getValues();
// 行数も一応とっておく  
let lastRow = targetSheet.getLastRow();
// タイムスタンプをセットして更新フラグとしたいので，変数を宣言しておく
const myTimeStamp_updated = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');

Spreadsheetidはここを取得します。

https://docs.google.com/spreadsheets/d/"ここに書いてあるのがidです。"/edit?resourcekey=undefined#gid=999999

Googleフォームで質問追加したときも対応可能なようにindexを取得しておく

結構質問ってあとから「これも欲しかったかも」ってなるので，一応index取得して対応できるようにしておく。
Googleフォームで質問を追加したとき，Spreadsheet上では，おそらくカラムごとのidが保管されていて，最終カラムに列を追加して新しい質問カラムとしているっぽい。なので，index指定しておかないと質問追加する度にいちいちメンテナンスしなければならなくなる。

  let colNo = {
    timestamp:targetValues[0].indexOf('タイムスタンプ') + 1, 
    q1:targetValues[0].indexOf('参加されますか？') + 1, 
    q2:targetValues[0].indexOf('参加者の名前をご記入ください。') + 1, 
    q3:targetValues[0].indexOf('このイベントのことを、どのようにしてお知りになりましたか。') + 1, 
    q4:targetValues[0].indexOf('コメントまたはご質問') + 1, 
    toBacklog:targetValues[0].indexOf('tobacklog_at') + 1, 
  };

あとはrow分以下のように回すだけ

  for (let i = 2;  i　<= lastRow; i++) {

    let lentext = targetSheet.getRange(i,p["toBacklog"]).getValue()
    if (lentext.length === 0 ) {

      let myUrl = targetSpreadSheet.getUrl();
      let mySid = targetSheet.getSheetId();
      let myUrlid = myUrl + "#gid=" + mySid + "range=A";
      let title = targetSheet.getRange(i,p["q4"]).getValue()
      let body = 
        '## 申し込みがありました。\n\n'
        + '日付:' + Utilities.formatDate(new targetSheet.getRange(i,p["timestamp"]).getValue(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss')　+ '\n'
        + '参加の有無:' + targetSheet.getRange(i,p["qi"]).getValue() + '\n'
        + 'お名前:' + targetSheet.getRange(i,p["q2"]).getValue() + '\n'
        + '媒体:' + targetSheet.getRange(i,p["q3"]).getValue() + '\n'
        + 'コメント・質問:' + targetSheet.getRange(i,p["q4"]).getValue() + '\n'
        + 'スプレッドシートURL:' + myUrlid + i + '\n\n'

        targetSheet.getRange(i,p["toBacklog"]).setValue(myTimeStamp_updated)
          
          GmailApp.sendEmail(address,title,body,{
            name:'アンケート自動登録',
            from:'no_reply@example.com'
          })

    }

  };

一応タイトルではBacklogとの連携を謳ったので，，，

backlogで以下の設定部分にメール連携があるので，APIを使わなくても簡単な自動登録が構築できます。

backlog：プロジェクト設定>インテグレーション>メールによる課題登録

backlog：プロジェクト設定>インテグレーション>メールによる課題登録（登録用メールアドレス取得後）

実行

実行。うまくできてそうです。

実行後回答スプレッドシート

実行後backlogで課題登録されているか確認

全文はこちら

function toBacklogIssues() {
  consttargetSpreadSheet = SpreadsheetApp.openById("ここにspreadsheetid");
  const targetSheet = targetSpreadSheet.getSheetByName("フォームの回答 1");
  const targetValues = targetSheet.getDataRange().getValues();
  const targetAddress = "ここに送りたいメールアドレス";
  let lastRow = targetSheet.getLastRow();
  const myTimeStamp_updated = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');


  let colNo = {
    timestamp:targetValues[0].indexOf('タイムスタンプ') + 1, 
    q1:targetValues[0].indexOf('参加されますか？') + 1, 
    q2:targetValues[0].indexOf('参加者の名前をご記入ください。') + 1, 
    q3:targetValues[0].indexOf('このイベントのことを、どのようにしてお知りになりましたか。') + 1, 
    q4:targetValues[0].indexOf('コメントまたはご質問') + 1, 
    toBacklog:targetValues[0].indexOf('tobacklog_at') + 1, 
  };

  for (let i = 2;  i　<= lastRow; i++) {

    let lentext = targetSheet.getRange(i,colNo["toBacklog"]).getValue()
    if (lentext.length === 0 ) {

      let myUrl = targetSpreadSheet.getUrl();
      let mySid = targetSheet.getSheetId();
      let myUrlid = myUrl + "#gid=" + mySid + "range=A";
      let title = targetSheet.getRange(i,colNo["q4"]).getValue()
      let body = 
        '## 申し込みがありました。\n\n'
        + '日付:' + Utilities.formatDate(new targetSheet.getRange(i,colNo["timestamp"]).getValue(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss')　+ '\n'
        + '参加の有無:' + targetSheet.getRange(i,colNo["q1"]).getValue() + '\n'
        + 'お名前:' + targetSheet.getRange(i,colNo["q2"]).getValue() + '\n'
        + '媒体:' + targetSheet.getRange(i,colNo["q3"]).getValue() + '\n'
        + 'コメント・質問:' + targetSheet.getRange(i,colNo["q4"]).getValue() + '\n'
        + 'スプレッドシートURL:' + myUrlid + i + '\n\n'

        targetSheet.getRange(i,colNo["toBacklog"]).setValue(myTimeStamp_updated)
          
          GmailApp.sendEmail(targetAddress,title,body,{
            name:'アンケート自動登録',
            from:'ここに送信元メールアドレス'
          })

    }

  };

};

難しいものではないですが，意外と実務で使うのかなと思って備忘録として載せておきました。
ありがとうございました。