header ads

スプレッドシートの複数の列を１つの列にまとめたい

10月 14, 2023

実現したいこと

ここに実現したいことを箇条書きで書いてください。

  • Googleフォームで集計したシート（フォームの回答１）のF・G・H列のデータを同一シート内（フォームの回答１）のE列に転記したい。

(例：F4→E4へ、G2→E4へ）
・転記できた場合→上記コード最上部にあるupdateFormで var total = values[4]のような形で値を取得できるのか？

前提

◎ここに質問の内容を詳しく書いてください◎
イベント参加をGoogleフォームで受付をし、スプレッドシートで纏めています。
F・G・H(時間別になっています)はそれぞれ時間帯別の参加人数（数字）となっていて、入力できる参加人数はF・G・Hのどこかになります。両方または３つ全てに数字が来ることはありません。必ずどこか１つの列に「１・２・３・４・５」の数字が入ります。

var total = values[7];//E・F・Gの人数を集めたデータ列（フォームの回答）
イメージ説明

シート＝（来場者シート③)
イメージ説明

発生している問題・エラーメッセージ

とくにありません。

該当のソースコード

ソースコード：Google App Script

function form(e) {//メイン関数
var values = e.values;//フォームの回答を取得
//var stamp = values[0];//タイムスタンプを取得
var mail = values[1];//アドレス
var date = values[2];//名前
var name = values[3];//日にち
** var total = values[4];F・G・Hの人数をまとめたい **
var participant = values[5];//10時の人数
var participant11 = values[6];//11時の人数
var participant13 = values[7];//13時の人数
var agree = values[8];//イベント参加の同意について
var up = 'FALSE';
var brank = '';
var ss = SpreadsheetApp.getActiveSpreadsheet();//スプレッドシートを有効にする
var sht = ss.getSheetByName('XX③');
sht.appendRow([brank, mail, name, participant, up, date]);

var today = Utilities.formatDate(new Date(), "JST", "yyyy/MM/dd");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sht = ss.getSheetByName("来場者シート③");
var lastrow = sht.getLastRow(); // 最終行を取得
var lastcol = sht.getLastColumn(); // 最終列を取得
var range = sht.getRange(1, 1, lastrow, lastcol);
var values = range.getValues(); // 情報をオンメモリに保持

/*来場者シートのデータを全部取得し、1行ずつチェック。
A列が空欄の場合はランダムな文字列を記入。
I列にQRコードの数式を設定=7列目
*/
for (var i = 1; i < lastrow; i++) {
data = values[i][0];
if (data == "") {
values[i][0] = getRndStr();
}
values[i][8] = false;
var qrc1 = '=image("https://chart.apis.google.com/chart?chs=250x250&cht=qr&chl=\"&A' + (i + 1) + ')';
values[i][7] = qrc1;
}
range.setValues(values); //スプレッドシートに書き戻し

/回答の名前と一致する行の１列目をidとして取得する/
for (let row = 1; row <= lastrow; row++) {
if (sht.getRange(row, 3).getValue() == name) {
var id = sht.getRange(row, 1).getValue();
}
}

//------------------------------------------
// 日程シートから該当日のデータを取得
// フォームの送信内容を取得
const reserveDate = date; // dateをそのまま使う

// 予約処理
const reservationCount = parseInt(total); // 追加 予約人数
const result = updateDatesSheet(reserveDate, reservationCount ); // 予約人数も渡す。

// 追加：reserveSheet変数の定義
const reserveSheet = ss.getSheetByName("予約B");

if (result === "success") {
reserveSheet.appendRow([mail, reserveDate]); // 予約シートに追記
updateForm(); // フォームの「空き状況」を更新
}

// 追加。メール送信時にid, name, totalを渡す。
sendEmail(mail, date, result, id, name, total);
}

/**

  • 引数に指定した予約日の予約人数が、定員オーバーでないか確認する。
  • オーバーしていなければ、日程シートの現在の予約数に予約人数を加算し、文字列"success"を返す。
  • オーバーしている場合は、文字列"error"を返す。
  • 引数：reserveDate フォームに入力された予約日
  • reservationCount フォームの回答から計算した予約人数

*/
// function updateDatesSheet(reserveDate) {
function updateDatesSheet(reserveDate, reservationCount ) { // 修正：予約人数を受け取る。
const ss1 = SpreadsheetApp.openById("スプレのURL");
const datesSheet = ss1.getSheetByName("XX①");
const table = datesSheet.getDataRange().getValues();
const index = table.findIndex(row => row[0] === reserveDate);

// 追加：フォームで回答された日付がスプレッドシート中になかった場合の処理。
if (index === -1) return "error";

const target = {
rowNum: index + 1,
date: table[index][0],
cap: table[index][1],
reserved: table[index][3], // 予約済人数：
}

// 定員超過しなければ「予約済」に加算する
//if (target.reserved < target.cap) { // 下記のように修正
// [更新前予約人数＋フォームで回答された予約人数]と定員とを比較する。+ reservationCount
if (target.reserved < target.cap) {
// datesSheet.getRange(target.rowNum, 3); // 下記のように修正
// 更新後予約済人数を日程シートの予約済列に書き込む。+ reservationCount
datesSheet.getRange(target.rowNum, 3).setValue(target.reserved );
return "success"
} else {
return "error"
}
}

//function sendEmail(mail, preferredDate, result) {
function sendEmail(mail, preferredDate, result, id, name, total) { // id,name,totalを受け取るように修正
const mailTitle = "予約結果";
const imageurl = 'https://chart.apis.google.com/chart?chs=250x250&cht=qr&chl=' + id;
const response = UrlFetchApp.fetch(imageurl); // option削除
const blob = response.getBlob().getAs(MimeType.JPEG);

if (result === "success") {
// QRコード付きのメール
var mailBody = "この度はお申込みをいただきありがとうございます。下記の内容でご予約を承りました。\n 当日、このメールに添付されているQRコードを受付でご提示ください。 \n\n"

  • 【予約日】：${preferredDate}\n

  • '【氏 名】' + name + '\n'

  • '【来場者ID】' + id + '\n'

  • '【お申込み人数】' + total + '名\n'

  • "【費用】XXXX円 \n"

  • "-------------------------------\n"

  • "「当日の流れ」\n"

  • "-------------------------------\n"

  • "※注意事項※\n"

  • "ご予約をキャンセルされる方は、下記のURLよりキャンセル手続きをお願い致します。\n"

+"https://XXXXXXXXXX"

  • "ご質問がある方は「◯◯◯◯」よりお問い合わせください。\n\n"
  • "-------------------------------\n"
  • "団体名\n"

+"HP：https://XXXXXXX"

  • "-------------------------------\n"

var option = {
method: "get",
"attachments": blob,
'name': 'XXXXX'
}
} else {
// QRコードなしのメール
var mailBody = "ご希望の日程は満席のため予約できませんでした。下記のフォームから再度申請してください"
"https://XXXXXXXXXXX";
var option = {
'name': 'XXX'
}
}

GmailApp.sendEmail(mail, mailTitle, mailBody, option);
}

function getRndStr() {
var str = "abcdefghijklmnopqrstuvwxyz0123456789";
var len = 8;
var result = "";
for (var i = 0; i < len; i++) {
result += str.charAt(Math.floor(Math.random() * str.length));
}
return result;
}

function updateForm() {
console.log("ダミーのupdateForm()が呼ばれました。")
}

試したこと

スプレッドシート内に関数を入れて作動させてみたものの、重くなることや万が一消してしまって気づかずうちにスプレッドシートを閉じてしまうこともあり得るのでGASで解決させたいです。
よろしくお願いいたします。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

hsmw

hsmw

コメントを投稿

0 コメント