https://www.pbainbridge.co.uk/2025/06/collate-and-organise-large-sheet-of-data.html

Collate and organise a large Sheet of data.gs

/**
 * This script creates the menu item that can easily be selected to run the Function.
 */


function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('Admin')
    .addItem('Collate and Create', 'collateData')
    .addToUi();

};


/**
 * Main Function to collate Google Sheet data into JavaScript Object for later iterating through.
 */


function collateData() {


  // get Spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();


  // create Toast notification
  ss.toast("Starting Collate Data process ...");


  // get relevant Sheet
  var allScriptsSheet = ss.getSheetByName("Data");

  // get all Data
  var allData = allScriptsSheet.getDataRange().getValues();

  // remove Header row
  allData.shift();
  var allDataLength = allData.length;


  // create empty JavaScript Object
  var obj = {};


  // loop through each row ***********************************************************
  for (var i = 0; i < allDataLength; i++) {


    // get Script Url
    var scriptUrl = allData[i][0];

    // get Last Update Time
    var lastUpdateTime = allData[i][1];

    // get Creator Email
    var creatorEmail = allData[i][2];

    // get Owner Email
    var ownerEmail = allData[i][3];


    // check if Owner Email is a Shared/Team drive to switch with Creator Email
    if ((ownerEmail == "In Team Drive") && (creatorEmail != "CREATOR NO LONGER EXISTS")) {

      // set Owner as the Creator instead
      ownerEmail = creatorEmail;

    } else if ((ownerEmail == "In Team Drive") || ((ownerEmail == "CREATOR NO LONGER EXISTS")) && (creatorEmail == "CREATOR NO LONGER EXISTS")) {

      // set Owner as a default email address
      ownerEmail = "[email protected]";

    } else {
      // proceed as normal with existing Owner Email
    };


    // see if the Owner Email already exists in the JavaScript Object ******************
    if (obj[ownerEmail] == null) {

      // the Object has not seen this yet, create an array with other details.
      obj[ownerEmail] = [{ scriptUrl: scriptUrl, lastUpdateTime: lastUpdateTime }];

    } else {

      // the Object knows about this, add details to existing array.
      obj[ownerEmail].push({ scriptUrl: scriptUrl, lastUpdateTime: lastUpdateTime });

    };
    // see if the Owner Email already exists in the JavaScript Object ******************


  };
  // loop through each row ***********************************************************


  // run Function to create individual Sheets for each Owner Email
  createSheets(ss, obj);


  ss.toast('Process complete.');

};


/**
 * Function to take JavaScript Object of data, make copies of a template Google Sheet and organise into individual Owner files.
 */


function createSheets(ss, obj) {

  // create Toast notification
  ss.toast("Starting Create Sheets process ...");


  // get destination folder for new files
  var destinationFolder = DriveApp.getFolderById("YOUR FOLDER ID HERE");

  // get template Sheet for copying
  var templateSheet = DriveApp.getFileById("YOUR FILE ID HERE");


  // get JavaScript Object as an array for iterating through Owner Emails
  var arrayToIterate = Object.entries(obj);


  // loop through array ***************************************************
  for (var [ownerEmail, detailObjs] of arrayToIterate) {


    // create empty array for pushing details into
    var detailsArray = [];


    // loop through detail Objects for each Owner Email *****************
    for (detailObj of detailObjs) {

      // extract Script Url
      var scriptUrl = detailObj.scriptUrl;

      // extract Last Update Time
      var lastUpdateTime = detailObj.lastUpdateTime;

      // push details into array
      detailsArray.push([scriptUrl, lastUpdateTime, ownerEmail]);

    };
    // loop through detail Objects for each Owner Email *****************



    // make copy of template Sheet
    var newFile = templateSheet.makeCopy(destinationFolder);

    // get template Id
    var newFileId = newFile.getId();

    // open new Sheet
    var openNewFile = SpreadsheetApp.openById(newFileId);

    // get relevant Sheet
    var getSheet = openNewFile.getSheetByName("Sheet1");

    // rename Sheet
    var newFileName = ownerEmail + " Collated URLs";
    DriveApp.getFileById(newFileId).setName(newFileName);



    // now append details to new Sheet in one go
    var lastRow = getSheet.getLastRow() + 1;
    var arrayLength = detailsArray.length;
    var arrayWidth = detailsArray[0].length;
    getSheet.getRange(lastRow, 1, arrayLength, arrayWidth).setValues(detailsArray);


    // share file with Owner Email
    try {

      openNewFile.addEditor(ownerEmail);

    } catch (error) {

      logEvent(error.stack);

    };


    // create Toast notification
    ss.toast("Completed Sheet for '" + ownerEmail + "'");



  };
  // loop through array ***************************************************



};


/**
 * Function to output messages to the 'Log' sheet.
 * Can be called anywhere else in script.
 */


function logEvent(action) {

  // get spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get Log Sheet
  var logSheet = ss.getSheetByName("Log");

  // get the user running the script
  var theUser = Session.getActiveUser().getEmail();

  // create and format a timestamp
  var dateTime = new Date();
  var timeZone = ss.getSpreadsheetTimeZone();
  var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yyyy HH:mm:ss");

  // create array of data for pasting into log sheet
  var logData = [niceDateTime, theUser, action];

  // append details into next row of log sheet
  logSheet.appendRow(logData);

};