CODEWITHSUNDEEP
javascriptdatabasegoogle-apps-scriptgoogle-sheets
Amirhossein Mahdinejad
Amirhossein Mahdinejad

Reputation: 574

SpreadSheetApp append value to a specific cell using JS

I wanna create a function to append (or edit) a value in a specific cell of my spreadSheet; for example where the Name is "Amirhossein" and also the Month is "Jan", append value x into the existing cell of column Items.

First state:

before calling function

and after calling f("Amirhossein", "Jan", "x") should be like this:

after calling function

using the below code, a new row will be add in my sheet:

SpreadsheetApp.openById("myId").getSheetByName("mySheet").appendRow(["Amirhossein", "Jan", "x"]);

but I want to consider these two columns (Name and Month) as primary key of my table.

Upvotes: 0

Views: 2585

Answers (1)

Tanaike
Tanaike

Reputation: 201388

  • You want to add x at the column "C" of the row that the values of column "A" and "B" are Amirhossein and Jan by running f("Amirhossein", "Jan", "x").
    • If the value of the column "C" is a, b, you want to add x like a, b, x.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this sample script? Please think of this as just one of several answers.

Sample script:

When you use this script, please copy and paste it to the script editor of the container-bound script of Spreadsheet, and set the Spreadsheet ID and sheet name, then run run(). By this, x is added to the value of column "C" at the row that the values of column "A" and "B" are Amirhossein and Jan.

function f(v1, v2, v3) {
  var spreadsheetId = "myId"; // Please set Spreadsheet ID.
  var sheetName = "mySheet"; // Please set sheet name.

  var ss = SpreadsheetApp.openById(spreadsheetId)
  var sheet = ss.getSheetByName(sheetName);
  var values = sheet.getRange(2, 1, sheet.getLastRow() - 1, 3).getValues();
  var updatedValues = values.map(function(e) {return [e[0] == v1 && e[1] == v2 ? (e[2] ? e[2] + ", " + v3 : v3) : e[2]]});
  sheet.getRange(2, 3, updatedValues.length, updatedValues[0].length).setValues(updatedValues);
}

// Please run this.
function run() {
  f("Amirhossein", "Jan", "x");
}

Note:

  • From your script in your question, in the sample script, I used openById() and getSheetByName().

References:

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 2

Related Questions