Google Sheets - Reorganise this values in "Normal" table

Question

I work in the marketing department of a Real Estate company and I'm in charge of Styling the comercial Blueprints of the Apartments that we sell. I usually recieve the Revit project with the diferent tipologies of Apartment and a Table of surfaces of each Apartment where there is the surface of each room.

So, an architect firm that works for my company always sends the data of the surfaces of the projects organised like this. The table is in spanish, sorry about that.

As you can see, They send the different table stacked on top of each other.

Most of the architect firms send a table where the columns are the number of apartment, The typology, and then the different rooms of each one. And the rows are all the apartments of the building. See example below Green table.

How can a reorganized this fast?

Why do i need this? Because i style the blueprints in illustrator and make the different versions with a CSV and Variables.

Answer 1

Data is received from a certain Architect in a "vertical" layout. However all other Architects, and your in-house template, use a "horizontal" layout. You want a method to convert the "vertical" layout to the "horizontal" layout.

At face-value, one might suggest to simply transpose the data from "vertical" to "horizontal". However there are a number of additional issues with the "vertical" format to be considered:

• "Planta" is included once only.

• internally inconsistencies

  • "Vestidor" is not included unless a given apartment includes this room.
  • the "Horizontal" format allows for four bedrooms, but the "vertical" format includes only as many bedrooms as a given apartment may have.

• Unique fields.

  • "Distribuidor" and a square foot "total"

• some merged cells are included in the data

---

Consider this answer.

• The script loops through each row of data.
• Variables are used to identify "Vivenda" and "Tipologia" and update a temporary array with their values
• each item name (Column A) is compared (using indexOf) to an array of "Output" heading names
• where a match is found, the item value is added to the temporary array
• where a match is not found (such as "Distribuidor") the item and value are highlighted in red and the user can decide what action should be taken.
• a blank cell denotes the end of data for one apartment and the beginning of the next apartment
  • the temporary array is pasted to the "horizontal format",
  • the temporary array and certain variables are reset and the loop continues.

---

function buildApartmentData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Sheet1")

  // an array to use with indexOf
  var outputHeads = ["Planta", "Vivenda", "Tipologia", "Bano 1", "Bano 2", "Dormo 1", "Dormo 2", "Dormo 3", "Dormo 4", "Salon", "Vest", "Terraza"]
  // a sample blank data array
  var outputArray = ["", "", "", "", "", "", "", "", "", "", "", ""]

  // get the architect data
  var archDataRange = sheet.getRange(1,1,sheet.getLastRow(),2)
  //Logger.log("DEBUG: the data range = "+archDataRange.getA1Notation())
  var archData = archDataRange.getValues()

  // create utility variables
  var dataStartRow = 5 // index for first row of data
  var aprtmentheaders = 0 // counter to include Vivenda and Tipologia ONLY once
  var outputRow = 9 // row number for pasting formatted output

  // get Planta
  var planta = archData[3][0].split(" ")[1]
  // Logger.log("DEBUG: planta = "+planta)

  

  // loop through the rest of the data
  for (var i=dataStartRow;i<archData.length;i++){

    if (archData[i][0] !== ""){
      if (aprtmentheaders==0){
        // create the array to update output fields       
        formattedArray = outputArray.slice()
        Logger.log(formattedArray)
        // update planta once only per loop
        formattedArray[0] = planta
        // get Vivenda
        formattedArray[1] = archData[i][0]
        aprtmentheaders = 1 // increment counter
        continue // next i
      }
      if (aprtmentheaders==1){
        // get Tipologia
        formattedArray[2] = archData[i][0]
        // Logger.log("DEBUG: vivenda = "+formattedArray[1]+", tipologia = "+formattedArray[2])
        aprtmentheaders = 2 // increment counter
        continue // next i
      }
      
      // get apartment data
      // find index of work name; if index = -1, then no match
      var index = outputHeads.indexOf(archData[i][0])
      if (index !== -1){
        // update array value
        formattedArray[index] = archData[i][1]
        // Logger.log("DEBUG: MATCH: i="+i+" "+archData[i][0]+" = "+archData[i][1])
      }
      else{
        // Logger.log("DEBUG: NO MATCH: i="+i+" "+archData[i][0]+" = "+archData[i][1])
        // unmatched value - set background colour = red
        var bgRange = sheet.getRange((+i+1),1,1,2)
        bgRange.setBackground('red')
      }
    }
    else{
      // loop has found a blank cell
      // Logger.log("DEBUG: Blank Cell = i"+i+", Row# = "+(i+1))

      // convert the attay to 2D
      formattedArray = [formattedArray]
      // Logger.log(formattedArray)
      // set the range to paste the array values
      var outputRange = sheet.getRange(outputRow,4,1,formattedArray[0].length)
      // Logger.log("DEBUG: the output range = "+outputRange.getA1Notation()+", formatted array length = "+formattedArray[0].length)

      //Logger.log(formattedArray) // DEBUG
      outputRange.setValues(formattedArray)
      // increment the outPut row for the next apartment
      outputRow= outputRow+1

      // the apartment counter back to zero to pickup Vivenda and Tiplogia
      aprtmentheaders=0     
    }
  }
}

---

SAMPLE DATA - Before

---

SAMPLE DATA - After