Google Sheet App Script is randomly doubling entries during wide to long format conversion.

[Rodney Trice]

Hello,

I'm attempting to convert wide format data collected in a Google Sheet tab "Entries (All)" to long data format in a different tab "Entries (Long Format All)" All seems to work except that when the data is converted to long format, the data entries are doubled. Meaning, if I make two entries in the "Entries (All)" tab, the script then populates four entries in the "Entries (Long Format All)" tab. This appears to happen randomly. Sometimes the code works perfectly and other times the entries a doubled. I would appreciate another set of eyes on the script.

function wideToLongFormatIncremental() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const wideSheet = ss.getSheetByName("Entries (All)");

const longSheet = ss.getSheetByName("Entries (Long Format All)") || ss.insertSheet("District IWT Entries (Long Format All)");

// Fetch headers from the wide format sheet for the data columns

const headers = wideSheet.getRange(1, 13, 1, wideSheet.getLastColumn() - 12).getValues()[0]; // Assuming data starts at column M

const lastProcessedRowCell = longSheet.getRange("AJ1"); // Tracking last processed row in AJ1

const lastProcessedRow = parseInt(lastProcessedRowCell.getValue()) || 1;

const totalRows = wideSheet.getLastRow();

if (lastProcessedRow >= totalRows) {

console.log("No new rows to process.");

return;

}

// Define the quarter dates

const quarters = [

{ name: 'Quarter 1', start: new Date('August 26, 2024'), end: new Date('October 31, 2024') },

{ name: 'Quarter 2', start: new Date('November 1, 2024'), end: new Date('January 23, 2025') },

{ name: 'Quarter 3', start: new Date('January 24, 2025'), end: new Date('March 27, 2025') },

{ name: 'Quarter 4', start: new Date('March 28, 2025'), end: new Date('June 12, 2025') },

];

// Only process new data rows since the last processed one

const range = wideSheet.getRange(lastProcessedRow + 1, 1, totalRows - lastProcessedRow, wideSheet.getLastColumn());

const data = range.getValues();

const newRows = [];

let entriesCount = 0; // Counter for non-empty entries

data.forEach((row, rowIndex) => {

const identifiers = row.slice(0, 12); // First 12 columns as identifiers

const dateValue = row[1]; // Column B (Date)

let quarter = '';

if (dateValue) { // Check if date is not empty

const date = new Date(dateValue);

for (let q of quarters) {

if (date >= q.start && date <= q.end) {

quarter = q.name;

break;

}

}

wideSheet.getRange(lastProcessedRow + rowIndex + 1, 38).setValue(quarter); // Update Column AL

} else {

wideSheet.getRange(lastProcessedRow + rowIndex + 1, 38).setValue(''); // Clear Column AL if date is empty

}

const dataEntries = row.slice(12, 35); // Data entries from M to AI (before Time, School Year, Quarter)

const time = row[35]; // Column AJ

const schoolYear = row[36]; // Column AK

dataEntries.forEach((value, i) => {

if (value !== "") { // Only process non-empty entries

const lookFor = headers[i];

const lookForRating = value;

const score = (lookForRating === "Observed") ? 100 : (lookForRating === "Not Observed") ? 0 : "";

const outputRow = [...identifiers, lookFor, lookForRating, score, time, schoolYear, quarter];

newRows.push(outputRow);

entriesCount++; // Increment the counter for each non-empty entry

}

});

});

// Debugging: Log new rows to verify data before insertion

console.log("New rows to insert:", newRows);

if (newRows.length > 0) {

const startRow = longSheet.getLastRow() + 1;

longSheet.getRange(startRow, 1, newRows.length, newRows[0].length).setValues(newRows);

}

// Update the last processed row number

lastProcessedRowCell.setValue(totalRows);

// Log the count of entries processed

console.log("Entries processed:", entriesCount);

console.log("Entries inserted into long format:", newRows.length);

}

// Trigger to update quarters when there is a change

function onChange(e) {

onChangeassignQuarters();

}

function onChangeassignQuarters() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheetAll = ss.getSheetByName("District IWT Entries (All)");

const quarters = [

{ name: 'Quarter 1', start: new Date('August 26, 2024'), end: new Date('October 31, 2024') },

{ name: 'Quarter 2', start: new Date('November 1, 2024'), end: new Date('January 23, 2025') },

{ name: 'Quarter 3', start: new Date('January 24, 2025'), end: new Date('March 27, 2025') },

{ name: 'Quarter 4', start: new Date('March 28, 2025'), end: new Date('June 12, 2025') },

];

const rangeAll = sheetAll.getDataRange();

const valuesAll = rangeAll.getValues();

for (let i = 1; i < valuesAll.length; i++) {

const dateValue = valuesAll[i][1];

if (dateValue) {

const date = new Date(dateValue);

let quarter = '';

for (let q of quarters) {

if (date >= q.start && date <= q.end) {

quarter = q.name;

break;

}

}

sheetAll.getRange(i + 1, 38).setValue(quarter);

} else {

sheetAll.getRange(i + 1, 38).setValue('');

}

}

}