CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsappend
Alexander
Alexander

Reputation: 3

Google Sheets add a note by appending column based on matching value

The goal is to add a note or several notes over time to a google sheet based on the value chosen.

The script will allow for a button to be added in the sheet and run the script function to append to the next available column.

For example, the name Tim is chosen. A note is written, the button is pressed to run the function and it will add it to Column D (since it is the next available column for Tim).

Another example, the name Jeff is chosen. A note is written. Since there are no more columns the append column should automatically create a new column and allow for the note to be written in "Jeff's" row, which would be Column G

Not sure if this is at all possible but hope to get some suggestions or ideas.

A B C D E F
Mark B+ completed assignment partly but needed extra time since.. 89433 other
Tim A checked
Jeff C n/a assignment # 4 done other
Steve A completed get file received
Elon B- three out of four check email

Upvotes: 0

Views: 166

Answers (1)

Sourabh Choraria
Sourabh Choraria

Reputation: 2331

Here's what I think you're trying to achieve -

add-note

And, this code should help you get started -

function main() {
  const name = "Steve";
  const note = "HELLO"
  addNote(name, note);
}

function addNote(name, note) {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = ss.getDataRange().getValues();
  for (var rowIndex = 0; rowIndex < values.length; rowIndex++) {
    let row = values[rowIndex];
    let nameColumn = row[0];
    if (nameColumn == name) {
      let rowValues = ss.getRange(`${rowIndex+1}:${rowIndex+1}`).getValues()[0]
      .filter(value => value !== "")
      let newColIndex = rowValues.length;
      ss.getRange(rowIndex+1, newColIndex+1).setValue(note);
    }
  }
}

Although, I'd highly recommend choosing something else as a unique identifier as opposed to a name because if there are 2 rows with the same name, then the code would actually add notes against both (incorrectly).

Upvotes: 2

Related Questions