Google Sheets Script - Copy data to a heat map table with Cell notes

Question

I want to copy the row/column/DBH data (on the left) to a heat map table (on the right) with additional data inserted into cell notes using a dynamic Google Apps Script. While the row, column and DBH fields must be in every heat map, the other fields (ID and DBH) used for the cell notes may vary. Below are the approximate flow steps using the partial screenshot as an example:

• Update a blue heat map header row with bold "Column" text in cell "G1 with the applicable "N" columns (2 in this case).
• Populate the second heat map blue header row with "N" numbered columns (1 and 2 in this case)
• Update the grey heat map cell with the bold text "Row" in cell "F2"
• Populate the grey heat map row column values below cell "F2"
• Copy the DBH values from the data table to the applicable heat map cells
• Then copy the ID and DBH data to the related cell notes per the screenshot example.
• The completed table is now ready for the user to configure the heat map

Answer 1

I believe what you want to do is transform the list in columns A:D to a matrix in starting in column G.

Here is my test spreadsheet with the results shown in column G.

This could probably be done with a pivot table but I'm not very good at that.

Code.gs

function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Test");
    let values = sheet.getRange(2,2,sheet.getLastRow()-1,4).getValues();  // exclude header
    let rows = values.map( row => row[0] );  // get the row numbers
    rows = [...new Set(rows)];
    let maxRow = 0;
    rows.forEach( row => { maxRow = row > maxRow ? row : maxRow } );
    let columns = values.map( row => row[1] ); // get the column numbers
    columns = [...new Set(columns)]
    let maxColumn = 0;
    columns.forEach( column => { maxColumn = column > maxColumn ? column : maxColumn } );
    let dbh = [];
    for( let i=0; i<maxRow; i++ ) {
      dbh.push(Array(maxColumn).fill(""));
      dbh[i].unshift(i+1);  // add row number
    }
    values.forEach( row => {
        let i = row[0]-1;
        let j = row[1];
        dbh[i][j] = row[2];
      }
    );
    // Add headers
    dbh.unshift(Array(maxColumn).fill(0));
    dbh[0].forEach( (cell,index) => dbh[0][index] = index+1 );
    dbh[0].unshift("Row");
    sheet.getRange(2,6,dbh.length,dbh[0].length).setValues(dbh);
  }
  catch(err) {
    console.log("Error in test: "+err)
  }
}

Reference

• SpreadsheetApp
• Spreadsheet.getSheetByName()
• Sheet.getRange()
• Range.getValues()
• Range.setValues()
• Array.map()
• Set
• Array.forEach()
• Array.push()
• Array.unshift()
• Array.fill()
• Arrow function =>