Google Apps Script - Transpose row to column with foreach

Question

I'm trying to grab the data for my spreadsheet for the rows and headers based on a unique identifier. I can grab the data easily enough, put it into an array, but I cannot figure out how to transpose the data into a column. tried to sanitize so forgive if i missed something.

test sheet:

https://docs.google.com/spreadsheets/d/195YJwCj5KUljVnIldooQ-1DMEvw1b4vPj6-9Hc1xgJA/edit?usp=sharing

function myFunction() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var uid = "2" //will eventually be dynamic, testing
var allData = sheet.getDataRange().getValues();
var temp = [];
var dataTemp = []

var uidList = allData.getRange(1, 2, allData.getLastRow(), 1).getValues().map(function (r) { return r[0] })
var uidIndex = uidList.indexOf(uid)
var rowData = []
var headers = []

allData[0].forEach(function (r) { headers.push(r) })
allData[0].forEach(function (x, i) {
rowData.push(allData[uidIndex][i])
})
var combined = []
combined.push(headers)
combined.push(rowData)
combined[0].forEach((x, i) => { //grab headers
temp.push(combined[i][0])
});
combined[1].forEach((x, i) => { //grab data row
dataTemp.push(combined[i][1])
});
// sheet.getRange(3,1,combined[0].length,combined.length).setValues(temp)
}

In your situation, is this thread useful? https://stackoverflow.com/q/17428587 — Tanaike, Jul 12 '22 at 07:24

score 0 · Answer 1 · answered Jul 12 '22 at 07:42

You can use this function, you have to pass your sheet for parametres and this function will transpose all google sheets.

function transposeSheet(sheet){
     var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
      //Defining a blank array that can hold the result
      trans = [];
      //transpose the data stored in range variable
      for(var column = 0; column < range[0].length; column++){
        trans[column] = [];
        for(var row = 0; row < range.length; row++){
          trans[column][row] = range[row][column];
        }
      }
      
      //Remove current data
      sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).clear();
      //printing values stored into trans variable on spreadsheet range
      sheet.getRange(1, 1, trans.length, trans[0].length).setValues(trans);
}

Google Apps Script - Transpose row to column with foreach

1 Answers1