// Replace with your Sheet ID and API Key
const SHEET_ID = '1kVNyvR4GrYyx1AiMralMdYKOKUPB6Xtg8AjenJpuagY';
const API_KEY = 'https://script.googleapis.com/v1/scripts/AKfycbyqShcMuCHwoMoLIYphhjyAyqwj6VEqTh6bEG0IGQxI2u77ad2tOshvI4BWHDkKk5805g:run';
// Load the Google Sheets API and then fetch the data
function loadSheetsApi() {
gapi.load('client', fetchDataFromSheet);
}
// Fetch the data from the Google Sheet
function fetchDataFromSheet() {
gapi.client.init({
apiKey: API_KEY,
}).then(() => {
gapi.client.sheets.spreadsheets.values.get({
spreadsheetId: SHEET_ID,
range: 'A1:Z', // Adjust the range as needed
}).then((response) => {
const data = response.result.values;
buildForm(data);
});
});
}
function generateArray(data, column) {
const result = [];
const today = new Date(); // Get current date
data.forEach(function(row) {
const date = row[0]; // Date is in first column (column A)
const value = row[column-1];
// Check if date is on or after today
if (date instanceof Date && !isNaN(date) && date >= today && value !== "") {
result.push(value);
}
});
return result;
}
// Build the form based on data from the Google Sheet
function buildForm(data) {
const form = document.createElement('form');
form.id = 'my-form';
// Add a multiple-choice question based on the choices generated from the data
const choices = generateArray(data, 6); // Customize the column number as needed
const multipleChoiceQuestionLabel = document.createElement('label');
multipleChoiceQuestionLabel.textContent = '参加したい説明会はどちらですか？';
form.appendChild(multipleChoiceQuestionLabel);
form.appendChild(document.createElement('br'));
choices.forEach((choice, index) => {
const input = document.createElement('input');
input.type = 'radio';
input.name = 'multipleChoice';
input.value = choice;
input.required = true;
input.id = `choice-${index}`;
form.appendChild(input);
const label = document.createElement('label');
label.htmlFor = `choice-${index}`;
label.textContent = choice;
form.appendChild(label);
form.appendChild(document.createElement('br'));
});
const submitButton = document.createElement('input');
submitButton.type = 'submit';
submitButton.value = 'Submit';
form.appendChild(submitButton);
// Add the form to the container on the page
document.getElementById('my-form-container').appendChild(form);
// Attach a submit event listener to the form
form.addEventListener('submit', (e) => {
e.preventDefault();
const formData = new FormData(e.target);
const data = {};
for (const [key, value] of formData.entries()) {
data[key] = value;
}
// Send the form data to the Google Apps Script web app
fetch('https://script.google.com/macros/s/AKfycbyqShcMuCHwoMoLIYphhjyAyqwj6VEqTh6bEG0IGQxI2u77ad2tOshvI4BWHDkKk5805g/exec', {
method: 'POST',
mode: 'no-cors',
body: JSON.stringify(data),
headers: {
'Content-Type': 'application/json',
},
}).then(() => {
window.location.href = 'YOUR_REDIRECT_URL';
}).catch((error) => {
alert('An error occurred. Please try again.');
});
});
}
// Call the loadSheetsApi function to start fetching the data and building the form
loadSheetsApi();
