Skip to content

Instantly share code, notes, and snippets.

@trash-anger

trash-anger/main.gs

Created June 20, 2024 14:15
Show Gist options
  • Save trash-anger/9a328050a6243d5cb13b5e8d04702725 to your computer and use it in GitHub Desktop.
Save trash-anger/9a328050a6243d5cb13b5e8d04702725 to your computer and use it in GitHub Desktop.
Download ZIP
App script google pour formulaire dynamique
Raw
main.gs
function closeFormWithMessage() {
var form = FormApp.openById("1DXYIZ7OP9Q1xk5csFmoAjWceH8VFD5XUrYnz20Kp5AY"); // Replace with your form ID
form.setAcceptingResponses(false);
form.setCustomClosedFormMessage("Le formulaire est fermé car il n'y a aucune place de disponible dans une voiture pour l'instant. Si tu as une voiture, propose ton trajet ici: https://docs.google.com/forms/d/e/1FAIpQLSeelCozp12yrXP0xhF5wfCuDl-nLC8bcejCaOWEk7YNIBGcGw/viewform?usp=sf_link");
}
function openForm() {
var form = FormApp.openById("1DXYIZ7OP9Q1xk5csFmoAjWceH8VFD5XUrYnz20Kp5AY"); // Replace with your form ID
form.setAcceptingResponses(true);
}
function addPassengersToSecondSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName('CharsFromForm'); // Change to the actual name of your first sheet
var targetSheet = ss.getSheetByName('Passagers'); // Change to the actual name of your second sheet
var sourceData = sourceSheet.getDataRange().getValues();
var sourceHeaders = sourceData[0]; // Get the headers
var timestampIndex = sourceHeaders.indexOf("Timestamp");
var driverIndex = sourceHeaders.indexOf("C'est quoi ton nom?");
var courrielIndex = sourceHeaders.indexOf("C'est quoi ton courriel?");
var carNameIndex = sourceHeaders.indexOf("Le nom funky de ton char?");
var passengersIndex = sourceHeaders.indexOf("Est-ce que tu prends des passagers?");
var departureLocationIndex = sourceHeaders.indexOf("Lieu de départ? (facultatif)");
var departureTimeIndex = sourceHeaders.indexOf("Jour et heure de départ? (facultatif)");
var returnTimeIndex = sourceHeaders.indexOf("Jour et heure de retour? (facultatif)");
var targetHeaders = ["Timestamp", "C'est quoi ton nom?", "C'est quoi ton courriel?", "Je connais le nom de mon char funky!", "Voiture", "Lieu de départ?", "Jour et heure de départ?", "Jour et heure de retour?"];
// Append headers only if target sheet is empty
if (targetSheet.getLastRow() === 0) {
targetSheet.appendRow(targetHeaders);
}
// Get the existing data in the target sheet
var targetData = targetSheet.getDataRange().getValues();
var existingEntries = {};
// Create a map of existing entries based on timestamp and passenger name
for (var i = 1; i < targetData.length; i++) {
var timestamp = targetData[i][0];
var passengerName = targetData[i][1];
if (!existingEntries[timestamp]) {
existingEntries[timestamp] = {};
}
existingEntries[timestamp][passengerName] = true;
}
for (var i = 1; i < sourceData.length; i++) {
var timestamp = sourceData[i][timestampIndex];
var driver = sourceData[i][driverIndex];
var courriel = sourceData[i][courrielIndex];
var carName = sourceData[i][carNameIndex];
var departureLocation = sourceData[i][departureLocationIndex];
var departureTime = sourceData[i][departureTimeIndex];
var returnTime = sourceData[i][returnTimeIndex];
// Add driver as a passenger if not already present
if (!existingEntries[timestamp] || !existingEntries[timestamp][driver]) {
targetSheet.appendRow([timestamp, driver, courriel, carName, carName, departureLocation, departureTime, returnTime]);
if (!existingEntries[timestamp]) {
existingEntries[timestamp] = {};
}
existingEntries[timestamp][driver] = true;
}
var passengers = sourceData[i][passengersIndex];
if (passengers) {
var passengerList = passengers.split(',');
for (var j = 0; j < passengerList.length; j++) {
var passenger = passengerList[j].trim();
if (!existingEntries[timestamp][passenger]) {
targetSheet.appendRow([timestamp, passenger, courriel, carName, carName, departureLocation, departureTime, returnTime]);
existingEntries[timestamp][passenger] = true;
}
}
}
}
}
function putClearCarName() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Passagers');
// Get the header row
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
// Find the index of the column with the header "Je connais le nom de mon char funky!"
var headerIndex = headers.indexOf("Je connais le nom de mon char funky!");
if (headerIndex == -1) {
Logger.log('Header not found');
return;
}
// Get values from the column dynamically based on the header index
var dataE = sheet.getRange(2, headerIndex + 1, sheet.getLastRow() - 1).getValues();
// Iterate through each row in the column
for (var i = 0; i < dataE.length; i++) {
if (dataE[i][0]) {
var clearCarName = dataE[i][0].split(',')[0].trim(); // Get the first element split by comma and trim any extra spaces
sheet.getRange(i + 2, headerIndex + 2).setValue(clearCarName); // Place the clearCarName in the next column
}
}
}
function updateDropdown() {
var form = FormApp.openById('1DXYIZ7OP9Q1xk5csFmoAjWceH8VFD5XUrYnz20Kp5AY'); // Replace with your form ID
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CharsFromPassengers');
// Ensure the sheet is not empty
var lastRow = sheet.getLastRow();
if (lastRow < 2) return; // Exit if there are no data rows
// Get values from columns A, D, and other relevant columns
var dataA = sheet.getRange('A2:A' + lastRow).getValues();
var dataD = sheet.getRange('D2:D' + lastRow).getValues();
var dataE = sheet.getRange('E2:E' + lastRow).getValues();
var dataF = sheet.getRange('F2:F' + lastRow).getValues();
// Filter and format values from column A and D
var formattedValues = [];
for (var i = 0; i < dataA.length; i++) {
if (dataD[i][0] > 0) {
var places = dataD[i][0] > 1 ? "places restantes" : "place restante";
var date = Utilities.formatDate(new Date(dataE[i][0]), "America/Toronto", "E d");
formattedValues.push(dataA[i][0] + ", pilotée par " + dataF[i][0] + ", départ le " + date + ", " + dataD[i][0] + " " + places);
}
}
if (formattedValues.length === 0) {
closeFormWithMessage();
} else {
openForm();
// Flatten the formatted values and filter out empty strings
var items = formattedValues.flat().filter(String);
// Get the dropdown question in the form
var formItems = form.getItems(FormApp.ItemType.LIST);
var listItem = formItems[0].asListItem();
// Update the dropdown options
listItem.setChoiceValues(items);
}
}
function main() {
addPassengersToSecondSheet();
putClearCarName();
updateDropdown();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment