i have this code on my google sheet app script which supposed to do a "give a condition with set default value for a cell whenever there is a new data row inside the sheet":

function onSelectionChange(e) {

  var row = e.range.getRow(); 
  var cellAbove = e.range.getRow()-1; 
  var upperCell = e.source.getActiveSheet().getRange(cellAbove,4).getValue(); 

if (upperCell === "Chris"){
    e.source.getActiveSheet().getRange(row,4).setValue("Genki");
    GmailApp.sendEmail("[email protected]", "form submit", "Message for Genki");
  } else {
  e.source.getActiveSheet().getRange(row,4).setValue("Chris");
  GmailApp.sendEmail("[email protected]", "form submit", "Message for Chris");
}
}

I put the trigger setting "on Change". The problem is, my function is not working on the trigger. my condition are the form is working and I have a sheet where whenever someone enter a form on my site, it will add the new data on my google sheets (using another script). The new data will be the first 3 column: timestamp, nama and email. Whereas the Agent column will be filled automaticaly with my script. Although the trigger "On change" is not working, when i change the trigger with "on Edit" and i type a new data on any cell, the script is working. All i want to achieve is whenever a new data row is created, the "Agent" column will be filled according to the condition.

my sheet

Is there any solution to this? Any help is appreciated. Thank you.

------------------------ update: this is my full script:

var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow()   1
   

    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
    

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}



function onSelectionChange(e) {

  var row = e.range.getRow(); 
  var cellAbove = e.range.getRow()-1; 
  var upperCell = e.source.getActiveSheet().getRange(cellAbove,4).getValue(); 

if (upperCell === "Chris"){
    e.source.getActiveSheet().getRange(row,4).setValue("Genki");
    //GmailApp.sendEmail("my_email", "subject", "message");
  } else {
  e.source.getActiveSheet().getRange(row,4).setValue("Chris");
  //GmailApp.sendEmail("another_mail", "subject", "message");
}
}

CodePudding user response：

I believe your goal is as follows.

• When a new row is added, you want to check the one above row using upperCell === "Chris".

In this case, how about modifying your doPost function as follows?

Modified script:

From:

var newRow = headers.map(function(header) {
  return header === 'timestamp' ? new Date() : e.parameter[header]
})

sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

To:

var newRow = headers.map(function (header) {
  return header === 'timestamp' ? new Date() : e.parameter[header]
})

var upperCell = sheet.getRange(nextRow - 1, 4).getValue();
if (upperCell === "Chris") {
  newRow[3] = "Genki";
  //GmailApp.sendEmail("my_email", "subject", "message"); // You will use this line?
} else {
  newRow[3] = "Chris";
  //GmailApp.sendEmail("another_mail", "subject", "message"); // You will use this line?
}

sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

• In this modification, when doPost is run, the last row of the current sheet is checked. By this, the array of newRow is updated.
• And, in this case, your onSelectionChange is not required to be used.

Note:

• When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
• You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".
• My proposed script is a simple script. So please modify it for your actual situation.