google-sheetsgoogle-apps-scriptscriptingmacrostextjoin

Macro for replacing data and then collating it?


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.


Solution

  • 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);
    }