https://www.pbainbridge.co.uk/2023/10/format-and-tidy-google-sheet-report.html

Format and tidy a Google Sheet report.gs

/**
 * Script to take a Microsoft Excel file.
 * Convert to a Google Sheet, remove unnecessary data/columns/rows.
 * Add some data/columns/rows.
 * Insert formula to concatenate email addresses.
 */



// enter the ID of the Excel file below
var excelFileID = "YOUR FILE ID HERE";

// enter the ID of the destination Drive folder below
var destinationFolderID = "YOUR FOLDER ID HERE";



function cleanUpReport() {


  Logger.log("Script has started.");


  // get Excel file
  var excelFile = DriveApp.getFileById(excelFileID);

  // get file name
  var fileName = excelFile.getName();
  Logger.log("fileName is: " + fileName);

  // get file blob
  var blob = excelFile.getBlob();


  // create Drive API parameters
  var newFile = {
    title: fileName,
    parents: [
      { id: destinationFolderID }
    ]
  };

  // create Drive optional parameters: convert to Sheets and Shared drive support
  var optionalArgs = {
    convert: true,
    supportsAllDrives: true
  };

  // convert and create new file via Drive API
  var sheetFile = Drive.Files.insert(newFile, blob, optionalArgs);


  // get new file ID
  var newFileID = sheetFile.id
  Logger.log("newFileID is: " + newFileID);

  // get the spreadsheet
  var ss = SpreadsheetApp.openById(newFileID);

  // get active sheet
  var sheet = ss.getActiveSheet();

  // get last Column
  var lastCol = sheet.getLastColumn();
  Logger.log("lastCol is: " + lastCol);


  // remove first 3 rows
  sheet.deleteRows(1, 3);
  Logger.log("First 3 row deleted.");

  // remove date columns
  sheet.deleteColumns(7, lastCol - 6);

  // insert some extra columns
  sheet.insertColumnAfter(6);

  // insert an empty row at the top
  sheet.insertRows(1);


  // get Header row range
  var headerRow = sheet.getRange(1, 1, 1, 7);

  // add Header values
  headerRow.setValues([['New Starter', 'First Name', 'Username', 'Role', 'Department', 'Faculty', 'Email Address']]);

  // set font size
  headerRow.setFontSize(14);

  // set font colour
  headerRow.setFontColor('white');

  // set font bold
  headerRow.setFontWeight('bold');

  // set font horizontal alignment
  headerRow.setHorizontalAlignment('center');

  // set font vertical alignment
  headerRow.setVerticalAlignment('middle');

  // set row background colour
  headerRow.setBackground('black');


  // set row height
  sheet.setRowHeight(1, 34);

  // set column widths
  sheet.setColumnWidths(1, 7, 208);

  Logger.log("Header row inserted and formatted.");



  // insert email address formula into first row
  var firstEmail = sheet.getRange(2, 7);
  var domain = "@example.com";
  var emailFormula = '=CONCATENATE(C2,"' + domain + '")';
  firstEmail.setFormula(emailFormula);

  // get last Row
  var lastRow = sheet.getLastRow();

  // autofill formula to rest of rows
  var destinationRange = sheet.getRange(2, 7, lastRow - 1, 1);
  firstEmail.autoFill(destinationRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

  Logger.log("Concatenate formula for email addresses inserted.");


  Logger.log("Script has completed.");


}