How to copy notes according values in Google sheets?

Question

I have two Google sheets with data.

First sheet contains source data, second sheet contains data build via =QUERY Google sheet function;

Some cells from last column of source data contains notes I want to copy these notes according with values.

function setCommentsFromData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName('TARGET_SHEET')
  const sourceSs = SpreadsheetApp.openById('SOURCE_SPREADSHEET');
  const sourceSh = sourceSs.getSheetByName('SOURCE_SHEET');
  let arrCol = sh.getRange(1, sh.getLastColumn(), sh.getLastRow());
  let sourceArrCol = sourceSh.getRange(1, sourceSh.getLastColumn(), sourceSh.getLastRow());
  arrCol.clear({commentsOnly : true});
  for (let i = 1; i <= sourceSh.getLastRow(); i++){
    let sourceCell = sourceArrCol.getCell(i, 1);
    let sourceNote = sourceCell.getNote();
    let sourceVal = sourceCell.getValue();
    for (let k = 1; k <= sourceSh.getLastRow(); k++){
      let cell = arrCol.getCell(k, 1);
      let val = cell.getValue();
      if (val === sourceVal) {
        cell.setNote(sourceNote)
      }
    }
  }    
}

My script does not work. Where I'm wrong and how can I fix it?

roma roma · Accepted Answer · 2020-05-14T06:09:54

What exactly doesn't work for you?

You script works fine, I tried it almost w/o changes and it does the job https://docs.google.com/spreadsheets/d/1u8k85HpYp_U4KPPQW7MxVFu0nb1OlLmOge6ksHz2PqY/edit?usp=sharing

The only error you have is in

  for (let k = 1; k <= sourceSh.getLastRow(); k++){

should be

  for (let k = 1; k <= sh.getLastRow(); k++){

cause if the number of rows differ you might miss some rows in target sheet.

Also, if you're using data from =QUERY() you should be using .getDisplayValue intead of getValue() to get real data in cell. E.g.

let sourceVal = sourceCell.getDisplayValue();
let val = cell.getDisplayValue();

How to copy notes according values in Google sheets?

1 Answers