Wondering if someone could help create a macro that will both replace information and collate data from a column into a certain format. I'm aware I could use replace and cocat function respectively but the Google spreadsheet is already heavy and I am still learning how to use Apps Script.
Date is generated like so:
Department | NameĀ | Duration | Pay Code | Request Date |
---|---|---|---|---|
Maintenance | Doe, John | 4 hours | PTO | 5/30/24 |
I would like to be able to turn the data into this worksheet (with the specific punctuation):
Subject | Date |
---|---|
Maintenance: Doe, John (4 hours, PTO) | 5/30/24 |
I have tried it with the concat
function but unable to get the punctuations just right, so I have to still go in an edit the sheet.
try:
function processSheetData() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const lastRow = sheet.getLastRow();
for (let i = 1; i <= lastRow; i++) {
const a = sheet.getRange(i, 1).getValue();
const b = sheet.getRange(i, 2).getValue();
const c = sheet.getRange(i, 3).getValue();
const d = sheet.getRange(i, 4).getValue();
const e = sheet.getRange(i, 5).getValue();
const g = `${a}: ${b}, (${c}, ${d})`;
const h = e;
sheet.getRange(i, 7).setValue(g);
sheet.getRange(i, 8).setValue(h);
}
}
or something smarter:
var INPUT_RANGE = 'Sheet1!A2:E10';
var OUTPUT_RANGE = 'Sheet2!B3:C';
function processSheetData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var inputRange = sheet.getRange(INPUT_RANGE);
var inputData = inputRange.getValues();
var outputData = inputData.map(function(row) {
var colG = row[0] + ': ' + row[1] + ', (' + row[2] + ', ' + row[3] + ')';
var colH = row[4];
return [colG, colH];
});
var outputRange = sheet.getRange(OUTPUT_RANGE);
outputRange.offset(0, 0, outputData.length, outputData[0].length).setValues(outputData);
}