x.x Google Sheets Automation Tutorial

x.x.1 Introduction - This is a quick tutorial using the automated results generator by Aryan Alipour. This is a Google Sheets app script which generates other formatted sheets. The generator takes a direct copy/paste from prastiscore's html results (not dynamic results).

x.x.2 Limitations - The app script is attached to a Google Sheets template which is found in the Google Drive Statistics Shared Drive. The script can only be utilised in the shared drive and the folders must be maintained in order for the script to work. Please make sure that the shared drive is maintained and the template is not altered.

x.x.3 Tutorial - The way the app script works is for a copy of the template to be made with the name of the match as the name of the Google Sheet. The sheet tabs should reflect each division of the match (not including Combined) with a copy/paste of each divisions html results from Practiscore. The app script is then selected first to authorise the use of it, then a second time to actually run the script. Once the script is finished, the other folders in the shared drive will have populated with the match results.

x.x.4 The App Scripts and Templates - Currently the automated scripts work based on the folders that are in place with the Shared Drives. DO NOT CHANGE THE DIRECTORIES OTHERWISE THE SCRIPT MAY NOT WORK. To help with maintenance, here is a copy of the app script with comments into what it does.

// Function to execute when the Google Sheets document is opened.
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Creates a new menu in the Google Sheets UI.
  ui.createMenu('Automated Scripts')
      .addItem('Run Results Generator', 'runFunction') 
      .addToUi();
}

// Function to generate results and export them.
function runFunction() {
  var newSpreadsheet = createSheetFromTemplate();

  exportSheetsToXML(newSpreadsheet);
}

// Function to create a new spreadsheet based on a template.
function createSheetFromTemplate() {
  // Identifiers for the template file and the destination folder.
  var templateFileId = '19Zx6Tma-f3meZMxytzSKC2mCwhXR2o_ryYiZEn-N77M'; 
  var destinationFolderId = '1hX4b0A0LSgu0I19vpTdiE63wXoBiozV8'; 
  
  // Retrieving the template file and the destination folder.
  var templateFile = DriveApp.getFileById(templateFileId);
  var destinationFolder = DriveApp.getFolderById(destinationFolderId);
  
  var sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // Displaying a message to the user indicating the start of the copying process.
  SpreadsheetApp.getActiveSpreadsheet().toast('Copying Results Template...', 'Progress', 5);

  // Creating a copy of the template file in the destination folder.
  var newFile = templateFile.makeCopy(sourceSpreadsheet.getName(), destinationFolder); 
  
  // Opening the new spreadsheet created from the template.
  var newSpreadsheet = SpreadsheetApp.open(newFile);
  var templateSpreadsheet = SpreadsheetApp.open(templateFile);
  
  // Retrieving the template sheet from the template spreadsheet.
  var templateSheet = templateSpreadsheet.getSheetByName('Results Template'); 
  
  // Getting all sheets from the source spreadsheet.
  var sourceSheets = sourceSpreadsheet.getSheets();
  
  // Loop through each sheet in the source spreadsheet.
  for (var i = 0; i < sourceSheets.length; i++) {
    // Copy the template sheet to the new spreadsheet and rename it.
    var newSheet = templateSheet.copyTo(newSpreadsheet);
    newSheet.setName(sourceSheets[i].getName());
    newSpreadsheet.setActiveSheet(newSheet);
    newSpreadsheet.moveActiveSheet(i + 1);
    
    // Notify user of the progress in processing each sheet.
    SpreadsheetApp.getActiveSpreadsheet().toast('Extracting Results and Importing in new Sheet...', 'Progress', 5);

    // Extracting specific ranges of data from the source sheet and setting them in the new sheet.
    var sourceData1 = sourceSheets[i].getRange('A1:B1000').getValues(); 
    var sourceData2 = sourceSheets[i].getRange('G1:I1000').getValues(); 

    newSheet.getRange('A1:B1000').setValues(sourceData1);
    newSheet.getRange('C1:E1000').setValues(sourceData2);
    
    // Notify user of progress in copying formulae.
    SpreadsheetApp.getActiveSpreadsheet().toast('Copying formulae to populated rows...', 'Progress', 5);

    // Copying formulas to populated rows in the new sheet.
    copyFormulaToPopulatedRows(newSheet);
    
    // Notify user of the completed processing of the current sheet.
    SpreadsheetApp.getActiveSpreadsheet().toast('Processed sheet ' + (i + 1) + ' of ' + sourceSheets.length, 'Progress', 5);
  }

  // Deleting the template sheet from the new spreadsheet.
  var templateSheetInNewSpreadsheet = newSpreadsheet.getSheetByName('Results Template');
  newSpreadsheet.deleteSheet(templateSheetInNewSpreadsheet);
  
  // Logging the URL of the new sheet for debugging purposes.
  Logger.log('Created new sheet: ' + newFile.getUrl());
  
  // Notify user that all sheets have been processed.
  SpreadsheetApp.getActiveSpreadsheet().toast('Finished processing all sheets.', 'Progress', 5);

  // Return the new spreadsheet object.
  return newSpreadsheet;
}

// Function to copy formulas to all populated rows in a sheet.
function copyFormulaToPopulatedRows(sheet) {
  // Selecting the range starting from A4 to the end of the spreadsheet.
  var range = sheet.getRange('A4:A' + sheet.getMaxRows());
  var values = range.getValues();
  
  // Retrieving the formula from cell F3.
  var formula = sheet.getRange('F3').getFormula(); 

  // Looping through each cell in the column starting from A4.
  for (var i = 0; i < values.length; i++) {
    // If the cell is not empty, set the formula in the corresponding cell in column F.
    if (values[i][0] != '') {
      sheet.getRange(i + 4, 6).setFormula(formula); // i + 4 because the range started from A4.
    }
  }
}

// Function to export the sheets to XML format.
function exportSheetsToXML(newSpreadsheet) {
  var sheets = newSpreadsheet.getSheets();

  // Notify user of the start of the XML export process.
  SpreadsheetApp.getActiveSpreadsheet().toast('Creating XML Export for League Tables Wordpress Plugin', 'Progress', 5);

  // Starting the XML string with the XML declaration and root element.
  var xml = '<?xml version="1.0" encoding="UTF-8"?>\n';
  xml += '<root>\n';
  
  // Looping through each sheet in the spreadsheet.
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var data = sheet.getDataRange().getValues();

    // Notify user of the creation of the XML table for each sheet.
    SpreadsheetApp.getActiveSpreadsheet().toast('Creating table for ' + newSpreadsheet.getName() + ' (' + sheet.getName() + ')', 'Progress', 5);
    
    // Adding XML elements for each table, including table details and data.
    xml += '    <table>\n';
    // ... (XML content generation for each table continues here)
    
    // Loop through each row in the sheet to add its data to the XML.
    for (var j = 1; j < data.length; j++) {
      xml += '            <record>\n';
      // ... (XML content generation for each record continues here)
      xml += '            </record>\n';
    }
    
    xml += '        </data>\n';
    xml += '    </table>\n';    
  }

  xml += '</root>\n';

  // Notify user that XML export is finished.
  SpreadsheetApp.getActiveSpreadsheet().toast('XML Export Finished', 'Progress', 5);
    
  // Convert the XML string to a Blob object for file creation.
  var xmlBlob = Utilities.newBlob(xml, 'application/xml', newSpreadsheet.getName() + '_LTImport.xml');

  // Identifier for the destination folder.
  var destinationFolderId = '1hKD4M1JwM2WmqGNNyicz3qvk40TZ7Wz5';

  var destinationFolder = DriveApp.getFolderById(destinationFolderId);

  // Create the XML file in the destination folder.
  destinationFolder.createFile(xmlBlob);

  // Notify user that the XML export file has been created.
  SpreadsheetApp.getActiveSpreadsheet().toast('XML Export File Created', 'Progress', 5);
}