Update Google Sheets Script - Add cell notes to heat map table

Question

I have a Google Sheets Script process that copies the required row, column and DBH data to a heat map table. See first screenshot and the partial answer, Copy data to a heat map table with Cell notes. How would this process be updated to dynamically add the ID, DBH and other potential data fields to the related cell notes per the second screenshot cell note example?

I have updated the TheWizEd's answer to include variable substitution and usage comments. Great job!

function test1() {
  try {
    //  Required data sheet column order: Row, Column,  DBH, ID, OptionalColumn1, etc
    let MY_LAST_COL = 4;  // Enter your last data sheet column number
    let MY_ROWS_COLUMNS_TEXT = 6; // Enter your heat map column number that you want to contain the text: "Rows & Columns"
    let MY_ROWS_COLUMNS_NBR = 7; // Enter your heat map column number that will start the set of column numbers
    let spread = SpreadsheetApp.getActiveSpreadsheet(); 
    let sheet = spread.getSheetByName("mysitenotes"); // Enter the name of your target worksheet
    //let values = sheet.getRange(2,1,sheet.getLastRow()-1,4).getValues();  // exclude header
      let values = sheet.getRange(2,1,sheet.getLastRow()-1,MY_LAST_COL).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 = [];
    let notes = []; 
    for( let i=0; i<maxRow; i++ ) {
      dbh.push(Array(maxColumn).fill(""));
      notes.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];
        //Update the lines below with your formatted cell note column names and N-1 column numbers:
        notes[i][j-1] =
        "ID: "+row[3]+
        "\nDBH: "+row[2];  // End cell note formatting      
      }
    );
    console.log(notes);
    // Add headers
    dbh.unshift(Array(maxColumn).fill(0));
    dbh[0].forEach( (cell,index) => dbh[0][index] = index+1 );
    dbh[0].unshift("Rows & Columns");
    sheet.getRange(1,MY_ROWS_COLUMNS_TEXT,dbh.length,dbh[0].length).setValues(dbh);
    sheet.getRange(2,MY_ROWS_COLUMNS_NBR,notes.length,notes[0].length).setNotes(notes); 
  }
  catch(err) {
    console.log("Error in test: "+err)
  }
}

Answer 1

I have taken the original code and editted it to place the notes for each ID.

Notice I still use my sheet named "Test".

function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Test");
    let values = sheet.getRange(2,1,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 = [];
    let notes = [];  // ***** added *****
    for( let i=0; i<maxRow; i++ ) {
      dbh.push(Array(maxColumn).fill(""));
      notes.push(Array(maxColumn).fill(""));  // ***** added *****
      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];
        notes[i][j-1] = "ID: "+row[3]+"\n DBH: "+row[2];  // ***** added *****
      }
    );
    console.log(notes);
    // Add headers
    dbh.unshift(Array(maxColumn).fill(0));
    dbh[0].forEach( (cell,index) => dbh[0][index] = index+1 );
    dbh[0].unshift("Rows & Columns");
    sheet.getRange(1,5,dbh.length,dbh[0].length).setValues(dbh);
    sheet.getRange(2,6,notes.length,notes[0].length).setNotes(notes);  // ***** added *****
  }
  catch(err) {
    console.log("Error in test: "+err)
  }
}