Can my appscript code pick data from most recent updated row?

Question

I am not a programmer. Through some online help I have been able to write a code that can send data from sheet into Slack channel but the problem right now is that it picks first cell from the range only. However, I want it to pick data from the most recent row

Sheet Context: A form is attached to this sheet and gets submissions, the purpose is that whenever someone submits form, a message gets posted on slack for the team to be notified

So, i want my code to pick data from most recent form submission. P.S my entire column is pre-filled with formula so need a way like VLOOKUP where it can see if one cell is blank then picks data from another column same row

CODE is here to view

 
function buildReport() {
  const ss = SpreadsheetApp.getActive();
let data = ss.getSheetByName('Sheet1').getRange("AG9:AH999");
  let payload = buildAlert(data);
  sendAlert (payload);
}

function buildAlert(data) { 
    for (i=0 ; i<20 ; i++) {
      newOrder = data .getvalue(i,1);
    };
  let payload = {
    "blocks": [
        {
            "type": "section",
            "text": {
                "type": "mrkdwn",
                "text": ":bell: *New Order Alert* :bell:"
            }
        },
        {
            "type": "divider"
        },
        {
            "type": "section",
            "text": {
                "type": "mrkdwn",
                "text": newOrder
  }
    },
    ]
  };
  return payload;
}
function sendAlert(payload) {
  const webHook = "https://hooks.slack.com/services/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";// 
  var options = { 
    "method": "post",
    "contentType" : "application/Json" ,
    "muteHttpExceptions" : true ,
    "payload" : JSON.stringify(payload)
  };
  try { 
    UrlFetchApp.fetch(webHook,options);
  } catch(e) { 
    Logger.log(e);
  }
}

user_14606052 · Answer

If the Google Form is attached to your Google Sheet, you can use the Form Submit Trigger to get the latest response.

Form Submit Trigger will automatically run a function whenever a user submitted a response in the form. It comes along with an event object that contains information about the context that caused the trigger to fire.

Here I created an example on how to use Form Submit Trigger. Whenever a user submitted a response, it will print the value of the response.

Form:

Code:

function onFormSubmit(e){
  Logger.log(e.values);
}

Note: e.values will return an array with values in the same order as they appear in the spreadsheet. You can also use e.namedValues, it will return an object containing the question names and values from the form submission.

To setup Form Submit Trigger:

In your Apps Script, hover to the left menu and click Triggers.
Click Add Trigger.
Setup your trigger just like the image below.
Click Save.

Trigger Setup:

To view the logs, go the the Executions tab below the Triggers tab. Each response will create an entry in the Executions tab.

Output:

To apply it in your code, change this:

function buildReport() {
  const ss = SpreadsheetApp.getActive();
let data = ss.getSheetByName('Sheet1').getRange("AG9:AH999");
  let payload = buildAlert(data);
  sendAlert (payload);
}

To:

function buildReport(e) {
  let response = e.values;
  let data = response[1]; //this will get the column b of the latest response.
  let payload = buildAlert(data);
  sendAlert (payload);
}

And the trigger setup should look like this:

For 3rd Party Forms (like jotForms), you can use onChange Trigger.

Try this:

Code:

function buildReport(e) {
  if(e.changeType == "EDIT"){
    var sh = SpreadsheetApp.getActiveSpreadsheet();
    var ss = sh.getSheetByName("Insert Sheet name here");
    var lastRow = ss.getLastRow();
    var range = ss.getRange(lastRow, 1);
    var data = range.getValue();
    let payload = buildAlert(data);
    sendAlert (payload);
  }
}

Trigger Setup:

Note: Make sure to avoid manually editing the response sheet as it will also run the function.

Can my appscript code pick data from most recent updated row?

1 Answers

References