Unpivot_AppsScript.js

function unpivotData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("historical_emissions_GHG_1990_2022"); // Change if your sheet has a different name
  var data = sourceSheet.getDataRange().getValues();
  
  // Get headers
  var headers = data[0];
  var metadataHeaders = headers.slice(0, 6); // A-F columns
  var yearHeaders = headers.slice(6); // G onwards
  
  // Create output array
  var output = [];
  output.push(metadataHeaders.concat(["Year", "Value"])); // New headers
  
  // Process each row
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var metadata = row.slice(0, 6);
    var values = row.slice(6);
    
    // Create a row for each year
    for (var j = 0; j < values.length; j++) {
      if (values[j] !== "") { // Skip empty values
        output.push(metadata.concat([yearHeaders[j], values[j]]));
      }
    }
  }
  
  // Create new sheet or clear existing one
  var outputSheet = ss.getSheetByName("Unpivoted");
  if (outputSheet) {
    outputSheet.clear();
  } else {
    outputSheet = ss.insertSheet("Unpivoted");
  }
  
  // Write output
  outputSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
  
  SpreadsheetApp.getUi().alert("Done! Check the 'Unpivoted' sheet.");
}