Google Apps Script: Joining two or more columns with different column names-CodePudding

I work with various groups at work. Each group tracks the same thing so I want to create a central tracker to gather better data for senior leadership. However, each group names the columns in their trackers something different and have the columns in a different order.

Is there a way in Google apps script where I can combine these same columns together?

An example: One group will call a column "project name" in A1 cell, then another would label the column "Initiative Name" in cell B1. But in a new sheet I want to combined them under 1 column name that I would label.

Thanks for any help!

I'm new to learning Google apps script so at this point I'm confused and lost.

CodePudding user response：

Yes, it is possible to combine columns with different names and different orders in Google Sheets using Google Apps Script. You can use the getRange and getValues methods to read the data from the different columns in the different sheets, and then use the setValues method to write the combined data to a new sheet.

Here is an example of how you can use Google Apps Script to combine columns with different names and orders:

// Get the data from the first sheet
var sheet1 = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var data1 = sheet1.getRange(1, 1, sheet1.getLastRow(), 
sheet1.getLastColumn()).getValues();

// Get the data from the second sheet
var sheet2 = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var data2 = sheet2.getRange(1, 1, sheet2.getLastRow(), 
sheet2.getLastColumn()).getValues();

// Combine the data from the two sheets
var combinedData = [];
for (var i = 0; i < data1.length; i  ) {
  var row = data1[i];
 combinedData.push([row[0], row[1], data2[i][1]]);
}

// Write the combined data to a new sheet
var newSheet = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("New Sheet"); 
newSheet.getRange(1, 1, combinedData.length, 
combinedData[0].length).setValues(combinedData);

In this example, we are combining data from two sheets named "Sheet1" and "Sheet2". We use the getRange and getValues methods to read the data from each sheet, and then we use a loop to combine the data from the two sheets into a single array of values. Finally, we use the setValues method to write the combined data to a new sheet named "New Sheet".

You can modify this code to match the specific column names and orders in your sheets, and to use the column names and order that you want in the new combined sheet.