CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user3245228
user3245228

Reputation: 85

setValue with getNotes

function onChange(e) {
  var ss = SpreadsheetApp.getActiveSheet();
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current");
  var r = ss.getActiveCell();
      
  if(e.changeType == 'FORMAT' && ss.getName() == "Current" && r.getBackground() ==  "#b7b7b7") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
    
    targetsheet.getrange(targetSheet.getLastRow() + 1, 2).setValue(getNotes(getrange(targetSheet.getLastRow() + 1, 16)))
    
    s.deleteRow(row);
  }
};

function getNotes(cell)
{
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var range = ss.getRange(cell)
   return range.getNotes();   
}

I'm using a third party add-on, onChange for my needs.

This function copies a row from one sheet to another when I color the row with #b7b7b7 and deletes it from the original sheet. However,

targetsheet.getrange(targetSheet.getLastRow() + 1, 2).setValue(getNotes(getrange(targetSheet.getLastRow() + 1, 16)))

does not seem to work. It should take notes from the second cell in the row and copy it to the 16th cell of the same row in the copied sheet. Help would be appreciated.

Upvotes: 2

Views: 101

Answers (1)

NightEye
NightEye

Reputation: 11194

I have modified some of your code below.

function onChange(e) {
  var ss = SpreadsheetApp.getActiveSheet();
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current");
  var r = ss.getActiveCell();
      
  if(e.changeType == 'FORMAT' && ss.getName() == "Current" && r.getBackground() ==  "#b7b7b7") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
    
    // Formatted note output to replicate sample data
    // You noted that it should be in column 16, feel free to update the column value `(4)` below
    targetSheet.getRange(targetSheet.getLastRow(), 4)
      .setValue(s.getRange(row, 2).getNote()) // get selected row's ID cell (row, 2) note
      .setBackground('#b7b7b7')               // gray
      .setHorizontalAlignment("center");      // center
    
    s.deleteRow(row);
  }
}

I'd like to add is that, your condition is not that safe to use. Everywhere you change a cell to gray, it will trigger the condition block. If I were you, I'd add some condition that will check if the formatted cell is within the data range (1 < getRow < getLastRow and 1 < getColumn < 3). Although, with the current condition, it will still work.

Another thing is that, I removed the second function getNotes and just integrated it with setValue since you don't really need to separate it if it just gets the note of the formatted row.

Output:

sample output

formatting new row without note:

sample output2

Upvotes: 1

Related Questions