It continues like this all day long with these two above errors.
The problem is, that the sheet our office uses to get an overview of which trucks are registered, they are now duplicated, and actually sometimes 3 times.
Like mentioned in the beginning, I have 4 scripts running, but I guess this is the one that is the problem, right?
// Copy all columns from range B2 to H from 'Responses' sheet, and insert into 'Filter' sheet only in column B to G
// A trigger is created in App Script menu under 'Triggers' to start the below script each time a new form-respons is submitted.
function copyDataToFilter(e) {
var sourceSheet = e.source;
// Get sheet "Responses"
var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses");
// Get sheet "Filter"
var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Filter");
// If sheet doesn't exist, create it
if (targetSheet == null) {
targetSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("Filter");
}
// Get data from "Responses" sheet
var range = sourceSheet.getRange("B2:G");
var values = range.getValues();
// Get last row number in target sheet
var lastRow = targetSheet.getLastRow();
// Process data and store result in a new array
var result = [];
for (var i = 0; i < values.length; i++) {
var row = values[i];
if (row[0] == "") {
continue;
}
// Add row to the result array with current timestamp row[1] = Language column, row [2] = Truck column etc.
var newRow = [new Date(), row[1], row[2], row[4], row[5], row[6]];
result.push(newRow);
}
// Insert data from result array into Filter sheet starting from next row after the last row
if (result.length > 0) {
var newRowIndex = lastRow + 1;
var newValues = result[result.length - 1];
targetSheet.getRange(newRowIndex, 1, 1, newValues.length).setValues([newValues]);
}
// Set numberformat of column A to 'Day, Month, Year Hour & minute'
targetSheet.getRange(lastRow + 1, 1, result.length, 1).setNumberFormat("dd. MMM yyyy HH:mm");
// Set format in column B, C to text and numbers
targetSheet.getRange(lastRow + 1, 2, result.length, 2).setNumberFormat("@");
// Set numberformat of column D and E to "HH:mm"
targetSheet.getRange(lastRow + 1, 4, result.length, 2).setNumberFormat("HH:mm");
// Align data in column A to the left
targetSheet.getRange(lastRow + 1, 1, result.length, 1).setHorizontalAlignment("Left");
// Center all data in column B to F
targetSheet.getRange(lastRow + 1, 2, result.length, 5).setHorizontalAlignment("Center");
//------------------------------------------------------------------------------------------------//
// This will add hours to the 'Valid Until' field
// It adds 48 hours on Fridays from 17:30 to Saturday morning 08:00
// All other days 24 hours is added
// Get active sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Filter");
// Get range of data
var data = sheet.getDataRange().getValues();
var lastRow = sheet.getLastRow();
var startRow = lastRow-1; // Only process the last row
// Loop through the range and set date & time format
for (var i = startRow; i < lastRow; i++) {
var originalDateTime = new Date(data[i][0]);
var day = Utilities.formatDate(originalDateTime, "Europe/Copenhagen", "EEEE");
var hour = originalDateTime.getHours();
var minute = originalDateTime.getMinutes();
// Check if day is Friday 22:30 to Saturday 08:00 and add 48 hours, else add 24 hours
if (day === "Friday" && (hour > 22 || (hour === 22 && minute >= 30)) || day === "Saturday" && hour < ๐ {
originalDateTime.setTime(originalDateTime.getTime() + 2*24*60*60*1000); // adding 2 days
} else {
originalDateTime.setTime(originalDateTime.getTime() + 24*60*60*1000); // adding 1 day
}
sheet.getRange(i+1, 6).setValue(originalDateTime);
sheet.getRange(i+1, 6).setNumberFormat("dd. MMM yyyy HH:mm");
sheet.getRange(i+1, 6).setHorizontalAlignment("Center");
}
// Call dataForDoc function after copying data is complete
dataForDoc();
}
LinkedIn
Twitter
