-1

I have a google sheet that I would like to set up a script for, which looks for specific criteria such as if a cell in a range contains something that is also in another column, or contains something specific. My example sheet shows what I would like to happen: Example sheet & screenshot

Anything in the name column (A) that contains the city name (B) as part of it would get a note ("Are you sure this is right?"). Ie if Row 1 has "Las Vegas Smith" as the name and "Las Vegas" as the city, it would get the note. If Row 5 has "Charlotte Applegate" as the name and "Applegate" as the city, it would also get the note.

I also need to add a note to other cells in particular columns that meet criteria, but they are only looking at one column. Ie anything that contains a hyphen in Column A would get a note like "Hyphen in the name?"

I have been able to highlight the name/city items using conditional formatting, using a custom formula =REGEXMATCH (LOWER($A2),LOWER($B2)). And just using 'text contains' works for the rest for conditional formatting. But I'd like to also add a note in addition to making them a different color.

I have found something to add notes,

function addNote() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Testing');
  var targetCell = sheet.getRange("A2:A5");
  var sourceCell = sheet.getRange("E2:E3");

  var noteText = sourceCell.getValue();

  targetCell.setNote(noteText);

}

(pointing to the second tab, Testing, which is just a copy of the first w/formatting removed.) This results in the text from E2 as a note to every cell in the A2:A5 range. I'm unsure how to add in that it needs to look for criteria and add the appropriate note based on that, and additionally that it needs to look for cells that contain that, not that match it exactly.

tsaria
  • 1
  • 2
  • Hi and welcome. Thank you for providing a link to your sheet BUT your question should also stand on its own - that means you have to explain EXECTLY what you are trying to do _in_ the question. Please don't expect that a user must open your spreadsheet to understand your question. In your case, as well as your spreadsheet, a screen snapshot or a data table might be enough. – Tedinoz Feb 04 '23 at 22:32
  • Does this answer your question? [How to check whether a string contains a substring in JavaScript?](https://stackoverflow.com/questions/1789945/how-to-check-whether-a-string-contains-a-substring-in-javascript) – Tedinoz Feb 04 '23 at 22:40
  • _but have not seen anything on how to check for particular characters/strings within a cell_ This is not an uncommon question. Refer [Check if a string includes substring](https://stackoverflow.com/q/54715390/1330560) (which has 6 answers) or [How to check whether a string contains a substring in JavaScript?](https://stackoverflow.com/q/1789945/1330560) which has a more basic javascript approach. – Tedinoz Feb 04 '23 at 22:43
  • Related question: [Add note based on value of another cell](https://stackoverflow.com/a/75289226/1330560) – Tedinoz Feb 04 '23 at 22:45
  • [Please do not upload images of code/data/errors.](//meta.stackoverflow.com/q/285551) – David Makogon Feb 05 '23 at 21:26

1 Answers1

0

The questions linked in the comments didn't address my needs, but I did get help elsewhere creating code that would work as intended. Additionally, it ignores any blanks rows.

function addNoteToCells() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numRows = sheet.getLastRow();
  var columnARange = sheet.getRange("A2:A" + numRows);
  var columnBRange = sheet.getRange("B2:B" + numRows);
  var columnAValues = columnARange.getValues();
  var columnBValues = columnBRange.getValues();

  for (var i = 0; i < columnAValues.length; i++) {
    if (columnAValues[i][0].length > 0 && columnBValues[i][0].length > 0) {
      if (columnAValues[i][0].indexOf(columnBValues[i][0]) != -1) {
        var cell = columnARange.getCell(i + 1, 1);
        cell.setNote("Are you sure this is right?");
      }
    }
  }
}
tsaria
  • 1
  • 2