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 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 runFunction() {
  var newSpreadsheet = createSheetFromTemplate();

  exportSheetsToXML(newSpreadsheet);
}

function createSheetFromTemplate() {
  var templateFileId = '19Zx6Tma-f3meZMxytzSKC2mCwhXR2o_ryYiZEn-N77M'; 
  var destinationFolderId = '1hX4b0A0LSgu0I19vpTdiE63wXoBiozV8'; 
  
  var templateFile = DriveApp.getFileById(templateFileId);
  var destinationFolder = DriveApp.getFolderById(destinationFolderId);
  
  var sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  SpreadsheetApp.getActiveSpreadsheet().toast('Copying Results Template...', 'Progress', 5);

  var newFile = templateFile.makeCopy(sourceSpreadsheet.getName(), destinationFolder); 
  
  var newSpreadsheet = SpreadsheetApp.open(newFile);
  var templateSpreadsheet = SpreadsheetApp.open(templateFile);
  
  var templateSheet = templateSpreadsheet.getSheetByName('Results Template'); 
  
  var sourceSheets = sourceSpreadsheet.getSheets();
  
  for (var i = 0; i < sourceSheets.length; i++) {
    var newSheet = templateSheet.copyTo(newSpreadsheet);
    newSheet.setName(sourceSheets[i].getName());
    newSpreadsheet.setActiveSheet(newSheet);
    newSpreadsheet.moveActiveSheet(i + 1);
    
    SpreadsheetApp.getActiveSpreadsheet().toast('Extracting Results and Importing in new Sheet...', 'Progress', 5);

    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);
    
    SpreadsheetApp.getActiveSpreadsheet().toast('Copying formulae to populated rows...', 'Progress', 5);

    copyFormulaToPopulatedRows(newSheet);
    
    SpreadsheetApp.getActiveSpreadsheet().toast('Processed sheet ' + (i + 1) + ' of ' + sourceSheets.length, 'Progress', 5);
  }

  var templateSheetInNewSpreadsheet = newSpreadsheet.getSheetByName('Results Template');
  newSpreadsheet.deleteSheet(templateSheetInNewSpreadsheet);
  
  Logger.log('Created new sheet: ' + newFile.getUrl());
  
  SpreadsheetApp.getActiveSpreadsheet().toast('Finished processing all sheets.', 'Progress', 5);

  return newSpreadsheet;
}

function copyFormulaToPopulatedRows(sheet) {
  var range = sheet.getRange('A4:A' + sheet.getMaxRows());
  var values = range.getValues();
  
  var formula = sheet.getRange('F3').getFormula(); 

  for (var i = 0; i < values.length; i++) {
    if (values[i][0] != '') {
      sheet.getRange(i + 4, 6).setFormula(formula); // i + 4 because we started from A4
    }
  }
}

function exportSheetsToXML(newSpreadsheet) {
  var sheets = newSpreadsheet.getSheets();

  SpreadsheetApp.getActiveSpreadsheet().toast('Creating XML Export for League Tables Wordpress Plugin', 'Progress', 5);

  var xml = '<?xml version="1.0" encoding="UTF-8"?>\n';
  xml += '<root>\n';
  
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var data = sheet.getDataRange().getValues();

    SpreadsheetApp.getActiveSpreadsheet().toast('Creating table for ' + newSpreadsheet.getName() + ' (' + sheet.getName() + ')', 'Progress', 5);
    xml += '    <table>\n';
    xml += '        <id>' + i+1 + '</id>\n';
    xml += '        <name>' + newSpreadsheet.getName() + ' (' + sheet.getName() + ')</name>\n';
    xml += '        <description>Match results for ' + newSpreadsheet.getName() + '\'s ' + sheet.getName() + ' Division.</description>\n';
    xml += '        <rows>' + sheet.getLastRow() + '</rows>\n';
    xml += '        <columns>' + sheet.getLastColumn() + '</columns>\n';
    xml += '        <show_position>0</show_position>\n';
    xml += '        <position_side>left</position_side>\n';
    xml += '        <order_by_1>1</order_by_1>\n';
    xml += '        <order_desc_asc_1>0</order_desc_asc_1>\n';
    xml += '        <order_data_type_1>auto</order_data_type_1>\n';
    xml += '        <order_date_format_1>ddmmyyyy</order_date_format_1>\n';
    xml += '        <order_by_2>1</order_by_2>\n';
    xml += '        <order_desc_asc_2>0</order_desc_asc_2>\n';
    xml += '        <order_data_type_2>auto</order_data_type_2>\n';
    xml += '        <order_date_format_2>ddmmyyyy</order_date_format_2>\n';
    xml += '        <order_by_3>1</order_by_3>\n';
    xml += '        <order_desc_asc_3>0</order_desc_asc_3>\n';
    xml += '        <order_data_type_3>auto</order_data_type_3>\n';
    xml += '        <order_date_format_3>ddmmyyyy</order_date_format_3>\n';
    xml += '        <order_by_4>1</order_by_4>\n';
    xml += '        <order_desc_asc_4>0</order_desc_asc_4>\n';
    xml += '        <order_data_type_4>auto</order_data_type_4>\n';
    xml += '        <order_date_format_4>ddmmyyyy</order_date_format_4>\n';
    xml += '        <order_by_5>1</order_by_5>\n';
    xml += '        <order_desc_asc_5>0</order_desc_asc_5>\n';
    xml += '        <order_data_type_5>auto</order_data_type_5>\n';
    xml += '        <order_date_format_5>ddmmyyyy</order_date_format_5>\n';
    xml += '        <table_layout>0</table_layout>\n';
    xml += '        <table_width>0</table_width>\n';
    xml += '        <table_width_value>400</table_width_value>\n';
    xml += '        <table_minimum_width>0</table_minimum_width>\n';
    xml += '        <column_width>0</column_width>\n';
    xml += '        <column_width_value>100</column_width_value>\n';
    xml += '        <table_margin_top>20</table_margin_top>\n';
    xml += '        <table_margin_bottom>20</table_margin_bottom>\n';
    xml += '        <enable_container>0</enable_container>\n';
    xml += '        <container_width>400</container_width>\n';
    xml += '        <container_height>400</container_height>\n';
    xml += '        <header_background_color>#C3512F</header_background_color>\n';
    xml += '        <header_font_color>#FFFFFF</header_font_color>\n';
    xml += '        <header_link_color>#FFFFFF</header_link_color>\n';
    xml += '        <even_rows_background_color>#FFFFFF</even_rows_background_color>\n';
    xml += '        <even_rows_font_color>#666666</even_rows_font_color>\n';
    xml += '        <even_rows_link_color>#C3512F</even_rows_link_color>\n';
    xml += '        <odd_rows_background_color>#FCFCFC</odd_rows_background_color>\n';
    xml += '        <odd_rows_font_color>#666666</odd_rows_font_color>\n';
    xml += '        <odd_rows_link_color>#C3512F</odd_rows_link_color>\n';
    xml += '        <header_border_color>#B34A2A</header_border_color>\n';
    xml += '        <header_position_alignment>center</header_position_alignment>\n';
    xml += '        <rows_border_color>#E1E1E1</rows_border_color>\n';
    xml += '        <phone_breakpoint>479</phone_breakpoint>\n';
    xml += '        <tablet_breakpoint>989</tablet_breakpoint>\n';
    xml += '        <position_label>#</position_label>\n';
    xml += '        <number_format>0</number_format>\n';
    xml += '        <enable_sorting>0</enable_sorting>\n';
    xml += '        <enable_manual_sorting>0</enable_manual_sorting>\n';
    xml += '        <show_header>1</show_header>\n';
    xml += '        <sticky_header>0</sticky_header>\n';
    xml += '        <header_font_size>11</header_font_size>\n';
    xml += '        <header_font_family>\'Open Sans\', Helvetica, Arial, sans-serif</header_font_family>\n';
    xml += '        <header_font_weight>400</header_font_weight>\n';
    xml += '        <header_font_style>normal</header_font_style>\n';
    xml += '        <body_font_size>11</body_font_size>\n';
    xml += '        <body_font_family>\'Open Sans\', Helvetica, Arial, sans-serif</body_font_family>\n';
    xml += '        <body_font_weight>400</body_font_weight>\n';
    xml += '        <body_font_style>normal</body_font_style>\n';
    xml += '        <caption_show_caption>1</caption_show_caption>\n';
    xml += '        <caption_caption_side>1</caption_caption_side>\n';
    xml += '        <caption_text_align>1</caption_text_align>\n';
    xml += '        <caption_font_size>11</caption_font_size>\n';
    xml += '        <caption_font_family>\'Open Sans\', Helvetica, Arial, sans-serif</caption_font_family>\n';
    xml += '        <caption_font_weight>400</caption_font_weight>\n';
    xml += '        <caption_font_style>normal</caption_font_style>\n';
    xml += '        <caption_font_color>#666666</caption_font_color>\n';
    xml += '        <autocolors_priority>rows</autocolors_priority>\n';
    xml += '        <autocolors_affected_rows_1 />\n';
    xml += '        <autocolors_rows_background_color_1>#FFFFFF</autocolors_rows_background_color_1>\n';
    xml += '        <autocolors_rows_font_color_1>#666666</autocolors_rows_font_color_1>\n';
    xml += '        <autocolors_affected_rows_2 />\n';
    xml += '        <autocolors_rows_background_color_2>#FFFFFF</autocolors_rows_background_color_2>\n';
    xml += '        <autocolors_rows_font_color_2>#666666</autocolors_rows_font_color_2>\n';
    xml += '        <autocolors_affected_rows_3 />\n';
    xml += '        <autocolors_rows_background_color_3>#FFFFFF</autocolors_rows_background_color_3>\n';
    xml += '        <autocolors_rows_font_color_3>#666666</autocolors_rows_font_color_3>\n';
    xml += '        <autocolors_affected_rows_4 />\n';
    xml += '        <autocolors_rows_background_color_4>#FFFFFF</autocolors_rows_background_color_4>\n';
    xml += '        <autocolors_rows_font_color_4>#666666</autocolors_rows_font_color_4>\n';
    xml += '        <autocolors_affected_rows_5 />\n';
    xml += '        <autocolors_rows_background_color_5>#FFFFFF</autocolors_rows_background_color_5>\n';
    xml += '        <autocolors_rows_font_color_5>#666666</autocolors_rows_font_color_5>\n';
    xml += '        <autocolors_affected_columns_1 />\n';
    xml += '        <autocolors_columns_background_color_1>#FFFFFF</autocolors_columns_background_color_1>\n';
    xml += '        <autocolors_columns_font_color_1>#666666</autocolors_columns_font_color_1>\n';
    xml += '        <autocolors_affected_columns_2 />\n';
    xml += '        <autocolors_columns_background_color_2>#FFFFFF</autocolors_columns_background_color_2>\n';
    xml += '        <autocolors_columns_font_color_2>#666666</autocolors_columns_font_color_2>\n';
    xml += '        <autocolors_affected_columns_3 />\n';
    xml += '        <autocolors_columns_background_color_3>#FFFFFF</autocolors_columns_background_color_3>\n';
    xml += '        <autocolors_columns_font_color_3>#666666</autocolors_columns_font_color_3>\n';
    xml += '        <autocolors_affected_columns_4 />\n';
    xml += '        <autocolors_columns_background_color_4>#FFFFFF</autocolors_columns_background_color_4>\n';
    xml += '        <autocolors_columns_font_color_4>#666666</autocolors_columns_font_color_4>\n';
    xml += '        <autocolors_affected_columns_5 />\n';
    xml += '        <autocolors_columns_background_color_5>#FFFFFF</autocolors_columns_background_color_5>\n';
    xml += '        <autocolors_columns_font_color_5>#666666</autocolors_columns_font_color_5>\n';
    xml += '        <autoalignment_priority>rows</autoalignment_priority>\n';
    xml += '        <autoalignment_affected_rows_left />\n';
    xml += '        <autoalignment_affected_rows_center />\n';
    xml += '        <autoalignment_affected_rows_right />\n';
    xml += '        <autoalignment_affected_columns_left />\n';
    xml += '        <autoalignment_affected_columns_center />\n';
    xml += '        <autoalignment_affected_columns_right />\n';
    xml += '        <hide_tablet_list />\n';
    xml += '        <hide_phone_list />\n';
    xml += '        <phone_header_font_size>11</phone_header_font_size>\n';
    xml += '        <phone_body_font_size>11</phone_body_font_size>\n';
    xml += '        <phone_caption_font_size>11</phone_caption_font_size>\n';
    xml += '        <phone_hide_images>0</phone_hide_images>\n';
    xml += '        <tablet_header_font_size>11</tablet_header_font_size>\n';
    xml += '        <tablet_body_font_size>11</tablet_body_font_size>\n';
    xml += '        <tablet_caption_font_size>11</tablet_caption_font_size>\n';
    xml += '        <tablet_hide_images>0</tablet_hide_images>\n';
    xml += '        <enable_cell_properties>1</enable_cell_properties>\n';
    xml += '        <formula_average_decimals>2</formula_average_decimals>\n';
    xml += '        <formula_average_round>1</formula_average_round>\n';
    xml += '        <data>\n';
    
    for (var j = 1; j < data.length; j++) {
      xml += '            <record>\n';
      xml += '                <id>' + j + '</id>\n';
      xml += '                <table_id>1</table_id>\n';
      xml += '                <row_index>' + j + '</row_index>\n';
      xml += '                <content>[';
      for (var k = 0; k < data[j].length; k++) {
        xml += '"' + data[j][k] + '"';
        if (k < data[j].length - 1) {
          xml += ',';
        }
      }
      xml += ']</content>\n';
      xml += '            </record>\n';
    }
    
    xml += '        </data>\n';
    xml += '    </table>\n';    
  }

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

  SpreadsheetApp.getActiveSpreadsheet().toast('XML Export Finished', 'Progress', 5);
    
  // Convert the XML string to a Blob object
  var xmlBlob = Utilities.newBlob(xml, 'application/xml', newSpreadsheet.getName() + '_LTImport.xml');

  var destinationFolderId = '1hKD4M1JwM2WmqGNNyicz3qvk40TZ7Wz5';

  var destinationFolder = DriveApp.getFolderById(destinationFolderId);

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

  SpreadsheetApp.getActiveSpreadsheet().toast('XML Export File Created', 'Progress', 5);
}