0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@MaximumTAKASI

スプシ サンプル

0
Last updated at Posted at 2026-04-05

準備

  1. 取り込み先のスプシ作成
  2. 以下のスクリプトを追加
  3. スクリプトの設定項目を更新
  4. sourceSpreadsheetUrl
  5. sourceSheetName
  6. formResponseUrl
  7. entries
const CONFIG = {
  timezone: 'Asia/Tokyo',

  import: {
    sourceSpreadsheetUrl:
      '取り込み元スプシリンク',
    sourceSheetName: 'シート1',
    workSheetName: 'work',
    sentAtHeader: '送信日時',
    lookbackRows: 300,
  },

  source: {
    dateHeader: '日付',
    phoneHeader: '電話番号',
    nameHeader: '代表名',
    urlHeaders: ['HP', '媒体1', '媒体2', '媒体3'],
  },

  submit: {
    browserViewFormUrl:
      'Form/viewfrm',

    holidayCalendarId: 'ja.japanese#holiday@group.v.calendar.google.com',

    targetBusinessDays: {
      min: 5,
      max: 6,
    },

    workerEmail: 'emailaddress',

    entries: {
      name: 'entry.278032670',
      url: 'entry.291011523',
      phone: 'entry.925677709',
    },
  },
};

let holidayCalendarCache_ = null;
const holidayResultCache_ = {};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('作業')
    .addItem('元データを取り込む', 'importSourceSheet')
    .addToUi();

  SpreadsheetApp.getUi()
    .createMenu('送信')
    .addItem('条件で事前入力リンクを書き込む', 'writePrefillLinksByCondition')
    .addItem('選択範囲に事前入力リンクを書き込む', 'writePrefillLinksBySelection')
    .addToUi();
}

function importSourceSheet() {
  const sourceSpreadsheetId = getSpreadsheetIdFromUrl_(CONFIG.import.sourceSpreadsheetUrl);
  const sourceSheet = SpreadsheetApp
    .openById(sourceSpreadsheetId)
    .getSheetByName(CONFIG.import.sourceSheetName);

  if (!sourceSheet) {
    throw new Error('元シートが見つかりません。');
  }

  const lastRow = sourceSheet.getLastRow();
  const lastColumn = sourceSheet.getLastColumn();

  if (lastRow < 2) {
    throw new Error('元シートにデータがありません。');
  }

  const headerRow = sourceSheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  const headerMap = buildHeaderMap_(headerRow);
  validateRequiredHeaders_(headerMap);

  const dataRowCount = lastRow - 1;
  const rowsToRead = Math.min(CONFIG.import.lookbackRows, dataRowCount);
  const startRow = lastRow - rowsToRead + 1;

  const targetRows = sourceSheet.getRange(startRow, 1, rowsToRead, lastColumn).getValues();
  const today = normalizeDateToJst_(new Date());

  const filteredRows = targetRows.filter(row => {
    const sourceDate = row[headerMap[CONFIG.source.dateHeader]];
    if (!(sourceDate instanceof Date)) return false;

    const elapsedBusinessDays = countElapsedBusinessDays_(sourceDate, today);
    return (
      elapsedBusinessDays !== null &&
      elapsedBusinessDays >= CONFIG.submit.targetBusinessDays.min &&
      elapsedBusinessDays <= CONFIG.submit.targetBusinessDays.max
    );
  });

  const workSheet = getOrCreateSheet_(CONFIG.import.workSheetName);
  resetSheet_(workSheet);

  const workValues = [
    [CONFIG.import.sentAtHeader, ...headerRow],
    ...filteredRows.map(row => ['', ...row]),
  ];

  workSheet
    .getRange(1, 1, workValues.length, workValues[0].length)
    .setValues(workValues);

  SpreadsheetApp.getUi().alert(
    `元データを取り込みました。\n検索対象: 下${rowsToRead}件\n取り込み件数: ${filteredRows.length}件`
  );
}

function writePrefillLinksByCondition() {
  const rows = collectSubmissionTargets_();
  writePrefillLinks_(rows, '条件一致の行');
}

function writePrefillLinksBySelection() {
  const rows = collectSelectedRangeTargets_();
  writePrefillLinks_(rows, '選択範囲の行');
}

function writePrefillLinks_(rows, label) {
  if (rows.length === 0) {
    SpreadsheetApp.getUi().alert(`${label}に対象はありません。`);
    return;
  }

  const sheet = getWorkSheet_();
  const workHeaderMap = getWorkHeaderMap_(sheet);
  const sentAtColumn = workHeaderMap[CONFIG.import.sentAtHeader] + 1;

  let written = 0;
  let skipped = 0;
  const messages = [];

  rows.forEach(row => {
    const errors = validateSubmissionRow_(row);
    if (errors.length > 0) {
      skipped++;
      messages.push(`${row.rowNumber}行目: ${errors.join(', ')}`);
      return;
    }

    const url = buildPrefillUrl_(row);
    sheet.getRange(row.rowNumber, sentAtColumn).setValue(url);
    written++;
  });

  const alertLines = [
    `${label}に事前入力リンクを書き込みました。`,
    `書き込み: ${written}件`,
    `スキップ: ${skipped}件`,
  ];

  if (messages.length > 0) {
    alertLines.push('');
    alertLines.push('スキップ理由:');
    alertLines.push(messages.slice(0, 10).join('\n'));
    if (messages.length > 10) {
      alertLines.push(`...他 ${messages.length - 10} 件`);
    }
  }

  SpreadsheetApp.getUi().alert(alertLines.join('\n'));
}

function buildPrefillUrl_(row) {
  const params = [
    'usp=pp_url',
    `${encodeURIComponent(CONFIG.submit.entries.name)}=${encodeURIComponent(row.name || '')}`,
    `${encodeURIComponent(CONFIG.submit.entries.url)}=${encodeURIComponent(row.url || '')}`,
    `${encodeURIComponent(CONFIG.submit.entries.phone)}=${encodeURIComponent(row.phone || '')}`,
    `emailAddress=${encodeURIComponent(CONFIG.submit.workerEmail || '')}`,
  ];

  return `${CONFIG.submit.browserViewFormUrl}?${params.join('&')}`;
}

function getWorkSheet_() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.import.workSheetName);
  if (!sheet) {
    throw new Error('work シートが見つかりません。先に「元データを取り込む」を実行してください。');
  }
  return sheet;
}

function collectSubmissionTargets_() {
  const sheet = getWorkSheet_();
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return [];

  const workHeaderMap = getWorkHeaderMapFromValues_(values[0]);
  const today = normalizeDateToJst_(new Date());

  return values
    .slice(1)
    .map((row, index) => buildWorkRow_(row, index + 2, workHeaderMap, today))
    .filter(row => !row.sentAt)
    .filter(row => row.name || row.phone || row.url)
    .filter(row => row.sourceDate instanceof Date)
    .filter(row => row.elapsedBusinessDays !== null)
    .filter(
      row =>
        row.elapsedBusinessDays >= CONFIG.submit.targetBusinessDays.min &&
        row.elapsedBusinessDays <= CONFIG.submit.targetBusinessDays.max
    );
}

function collectSelectedRangeTargets_() {
  const rowNumbers = getSelectedRowNumbers_();
  return getRowsByRowNumbers_(rowNumbers).filter(row => !row.sentAt);
}

function getSelectedRowNumbers_() {
  const sheet = getWorkSheet_();
  const rangeList = sheet.getActiveRangeList();
  const rowSet = new Set();

  if (rangeList) {
    rangeList.getRanges().forEach(range => {
      const startRow = range.getRow();
      const endRow = range.getLastRow();

      for (let row = startRow; row <= endRow; row++) {
        if (row >= 2) rowSet.add(row);
      }
    });
  } else {
    const range = sheet.getActiveRange();
    if (!range) return [];

    const startRow = range.getRow();
    const endRow = range.getLastRow();

    for (let row = startRow; row <= endRow; row++) {
      if (row >= 2) rowSet.add(row);
    }
  }

  return Array.from(rowSet).sort((a, b) => a - b);
}

function getRowsByRowNumbers_(rowNumbers) {
  const sheet = getWorkSheet_();
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return [];

  const workHeaderMap = getWorkHeaderMapFromValues_(values[0]);
  const today = normalizeDateToJst_(new Date());

  return rowNumbers
    .filter(rowNumber => rowNumber >= 2 && rowNumber <= values.length)
    .map(rowNumber => buildWorkRow_(values[rowNumber - 1], rowNumber, workHeaderMap, today))
    .filter(row => row.name || row.phone || row.url);
}

function buildWorkRow_(row, rowNumber, workHeaderMap, today) {
  const sourceDate = row[workHeaderMap[CONFIG.source.dateHeader]];
  const elapsedBusinessDays =
    sourceDate instanceof Date ? countElapsedBusinessDays_(sourceDate, today) : null;

  const url = pickFirstHttpUrl_(row, workHeaderMap);

  return {
    rowNumber,
    sourceDate,
    name: row[workHeaderMap[CONFIG.source.nameHeader]],
    phone: row[workHeaderMap[CONFIG.source.phoneHeader]],
    url,
    sentAt: row[workHeaderMap[CONFIG.import.sentAtHeader]],
    elapsedBusinessDays,
  };
}

function pickFirstHttpUrl_(row, headerMap) {
  for (const header of CONFIG.source.urlHeaders) {
    const index = headerMap[header];
    if (index === undefined) continue;

    const value = String(row[index] || '').trim();
    if (/^https?:\/\//i.test(value)) {
      return value;
    }
  }
  return '';
}

function getWorkHeaderMap_(sheet) {
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  return getWorkHeaderMapFromValues_(headers);
}

function getWorkHeaderMapFromValues_(headers) {
  const headerMap = buildHeaderMap_(headers);
  validateRequiredWorkHeaders_(headerMap);
  return headerMap;
}

function buildHeaderMap_(headers) {
  const map = {};
  headers.forEach((header, index) => {
    map[String(header).trim()] = index;
  });
  return map;
}

function validateRequiredHeaders_(headerMap) {
  const requiredHeaders = [
    CONFIG.source.dateHeader,
    CONFIG.source.phoneHeader,
    CONFIG.source.nameHeader,
    ...CONFIG.source.urlHeaders,
  ];

  requiredHeaders.forEach(header => {
    if (!(header in headerMap)) {
      throw new Error(`取り込み元に必要なヘッダーがありません: ${header}`);
    }
  });
}

function validateRequiredWorkHeaders_(headerMap) {
  const requiredHeaders = [
    CONFIG.import.sentAtHeader,
    CONFIG.source.dateHeader,
    CONFIG.source.phoneHeader,
    CONFIG.source.nameHeader,
    ...CONFIG.source.urlHeaders,
  ];

  requiredHeaders.forEach(header => {
    if (!(header in headerMap)) {
      throw new Error(`work シートに必要なヘッダーがありません: ${header}`);
    }
  });
}

function validateSubmissionRow_(row) {
  const errors = [];
  if (!row.name) errors.push('代表名が空です');
  if (!row.phone) errors.push('電話番号が空です');
  if (!row.url) errors.push('URL候補が空です');
  return errors;
}

function countElapsedBusinessDays_(fromDate, toDate) {
  const start = normalizeDateToJst_(fromDate);
  const end = normalizeDateToJst_(toDate);

  if (start > end) return null;

  let count = 0;
  const current = new Date(start);

  while (current < end) {
    current.setDate(current.getDate() + 1);
    if (isBusinessDay_(current)) {
      count++;
    }
  }

  return count;
}

function isBusinessDay_(date) {
  const day = Number(Utilities.formatDate(date, CONFIG.timezone, 'u'));
  if (day === 6 || day === 7) return false;
  if (isJapaneseHoliday_(date)) return false;
  return true;
}

function isJapaneseHoliday_(date) {
  const key = toDateKey_(date);

  if (key in holidayResultCache_) {
    return holidayResultCache_[key];
  }

  const calendar = getJapaneseHolidayCalendar_();
  const events = calendar ? calendar.getEventsForDay(normalizeDateToJst_(date)) : [];
  const isHoliday = events.length > 0;

  holidayResultCache_[key] = isHoliday;
  return isHoliday;
}

function getJapaneseHolidayCalendar_() {
  if (holidayCalendarCache_) {
    return holidayCalendarCache_;
  }

  let calendar = CalendarApp.getCalendarById(CONFIG.submit.holidayCalendarId);

  if (!calendar) {
    calendar = CalendarApp.subscribeToCalendar(CONFIG.submit.holidayCalendarId);
  }

  holidayCalendarCache_ = calendar;
  return holidayCalendarCache_;
}

function getOrCreateSheet_(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  return ss.getSheetByName(sheetName) || ss.insertSheet(sheetName);
}

function resetSheet_(sheet) {
  sheet.clearContents();
  sheet.clearFormats();
}

function getSpreadsheetIdFromUrl_(url) {
  const match = String(url).match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/);
  if (!match) {
    throw new Error('スプレッドシートURLからIDを取得できませんでした。');
  }
  return match[1];
}

function normalizeDateToJst_(date) {
  const ymd = Utilities.formatDate(new Date(date), CONFIG.timezone, 'yyyy/MM/dd');
  return new Date(`${ymd} 00:00:00 +0900`);
}

function toDateKey_(date) {
  return Utilities.formatDate(normalizeDateToJst_(date), CONFIG.timezone, 'yyyy-MM-dd');
}

const CONFIG = {
  timezone: 'Asia/Tokyo',

  work: {
    sheetName: 'work',
    linkHeader: '送信日時',
  },

  source: {
    phoneHeader: '電話番号',
    nameHeader: '代表名',
    urlHeaders: ['HP', '媒体1', '媒体2', '媒体3'],
  },

  submit: {
    browserViewFormUrl: 'Form/viewfrm',

    workerEmail: 'emailaddress',

    entries: {
      name: 'entry.278032670',
      url: 'entry.291011523',
      phone: 'entry.925677709',
    },
  },
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('送信')
    .addItem('未記入行に事前入力リンクを書き込む', 'writePrefillLinksAll')
    .addItem('選択範囲に事前入力リンクを書き込む', 'writePrefillLinksBySelection')
    .addToUi();
}

function writePrefillLinksAll() {
  const rows = collectAllTargets_();
  writePrefillLinks_(rows, '未記入行');
}

function writePrefillLinksBySelection() {
  const rows = collectSelectedRangeTargets_();
  writePrefillLinks_(rows, '選択範囲の行');
}

function writePrefillLinks_(rows, label) {
  if (rows.length === 0) {
    SpreadsheetApp.getUi().alert(`${label}に対象はありません。`);
    return;
  }

  const sheet = getWorkSheet_();
  const workHeaderMap = getWorkHeaderMap_(sheet);
  const linkColumn = workHeaderMap[CONFIG.work.linkHeader] + 1;

  let written = 0;
  let skipped = 0;
  const messages = [];

  rows.forEach(row => {
    const errors = validateSubmissionRow_(row);
    if (errors.length > 0) {
      skipped++;
      messages.push(`${row.rowNumber}行目: ${errors.join(', ')}`);
      return;
    }

    const url = buildPrefillUrl_(row);
    sheet.getRange(row.rowNumber, linkColumn).setValue(url);
    written++;
  });

  const alertLines = [
    `${label}に事前入力リンクを書き込みました。`,
    `書き込み: ${written}件`,
    `スキップ: ${skipped}件`,
  ];

  if (messages.length > 0) {
    alertLines.push('');
    alertLines.push('スキップ理由:');
    alertLines.push(messages.slice(0, 10).join('\n'));
    if (messages.length > 10) {
      alertLines.push(`...他 ${messages.length - 10} 件`);
    }
  }

  SpreadsheetApp.getUi().alert(alertLines.join('\n'));
}

function buildPrefillUrl_(row) {
  const params = [
    'usp=pp_url',
    `${encodeURIComponent(CONFIG.submit.entries.name)}=${encodeURIComponent(row.name || '')}`,
    `${encodeURIComponent(CONFIG.submit.entries.url)}=${encodeURIComponent(row.url || '')}`,
    `${encodeURIComponent(CONFIG.submit.entries.phone)}=${encodeURIComponent(row.phone || '')}`,
    `emailAddress=${encodeURIComponent(CONFIG.submit.workerEmail || '')}`,
  ];

  return `${CONFIG.submit.browserViewFormUrl}?${params.join('&')}`;
}

function getWorkSheet_() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.work.sheetName);
  if (!sheet) {
    throw new Error(`"${CONFIG.work.sheetName}" シートが見つかりません。`);
  }
  return sheet;
}

function collectAllTargets_() {
  const sheet = getWorkSheet_();
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return [];

  const workHeaderMap = getWorkHeaderMapFromValues_(values[0]);

  return values
    .slice(1)
    .map((row, index) => buildWorkRow_(row, index + 2, workHeaderMap))
    .filter(row => !row.sentAt)
    .filter(row => row.name || row.phone || row.url);
}

function collectSelectedRangeTargets_() {
  const rowNumbers = getSelectedRowNumbers_();
  return getRowsByRowNumbers_(rowNumbers).filter(row => !row.sentAt);
}

function getSelectedRowNumbers_() {
  const sheet = getWorkSheet_();
  const rangeList = sheet.getActiveRangeList();
  const rowSet = new Set();

  if (rangeList) {
    rangeList.getRanges().forEach(range => {
      const startRow = range.getRow();
      const endRow = range.getLastRow();

      for (let row = startRow; row <= endRow; row++) {
        if (row >= 2) rowSet.add(row);
      }
    });
  } else {
    const range = sheet.getActiveRange();
    if (!range) return [];

    const startRow = range.getRow();
    const endRow = range.getLastRow();

    for (let row = startRow; row <= endRow; row++) {
      if (row >= 2) rowSet.add(row);
    }
  }

  return Array.from(rowSet).sort((a, b) => a - b);
}

function getRowsByRowNumbers_(rowNumbers) {
  const sheet = getWorkSheet_();
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return [];

  const workHeaderMap = getWorkHeaderMapFromValues_(values[0]);

  return rowNumbers
    .filter(rowNumber => rowNumber >= 2 && rowNumber <= values.length)
    .map(rowNumber => buildWorkRow_(values[rowNumber - 1], rowNumber, workHeaderMap))
    .filter(row => row.name || row.phone || row.url);
}

function buildWorkRow_(row, rowNumber, workHeaderMap) {
  const url = pickFirstHttpUrl_(row, workHeaderMap);

  return {
    rowNumber,
    name: row[workHeaderMap[CONFIG.source.nameHeader]],
    phone: row[workHeaderMap[CONFIG.source.phoneHeader]],
    url,
    sentAt: row[workHeaderMap[CONFIG.work.linkHeader]],
  };
}

function pickFirstHttpUrl_(row, headerMap) {
  for (const header of CONFIG.source.urlHeaders) {
    const index = headerMap[header];
    if (index === undefined) continue;

    const value = String(row[index] || '').trim();
    if (/^https?:\/\//i.test(value)) {
      return value;
    }
  }
  return '';
}

function getWorkHeaderMap_(sheet) {
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  return getWorkHeaderMapFromValues_(headers);
}

function getWorkHeaderMapFromValues_(headers) {
  const headerMap = buildHeaderMap_(headers);
  validateRequiredWorkHeaders_(headerMap);
  return headerMap;
}

function buildHeaderMap_(headers) {
  const map = {};
  headers.forEach((header, index) => {
    map[String(header).trim()] = index;
  });
  return map;
}

function validateRequiredWorkHeaders_(headerMap) {
  const requiredHeaders = [
    CONFIG.work.linkHeader,
    CONFIG.source.phoneHeader,
    CONFIG.source.nameHeader,
    ...CONFIG.source.urlHeaders,
  ];

  requiredHeaders.forEach(header => {
    if (!(header in headerMap)) {
      throw new Error(`work シートに必要なヘッダーがありません: ${header}`);
    }
  });
}

function validateSubmissionRow_(row) {
  const errors = [];
  if (!row.name) errors.push('代表名が空です');
  if (!row.phone) errors.push('電話番号が空です');
  if (!row.url) errors.push('URL候補が空です');
  return errors;
}
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?