Created
July 11, 2023 10:56
-
-
Save phillypb/e0f5f194a552c0b170329435972700d5 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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."); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment