I'm trying to post a Slack message for each new row of a spreadsheet using Google Scripts - but I'm only getting one post. What am I doing wrong?

Question

I'm trying to build a simple app to post a message to Slack for every new row in a Google Spreadsheet. I can get the info from the sheet and post it to the logger, and I can post to Slack, but I can only seem to get this to work with the first row in the loop (whereas the logger will receive all values for all rows). I'm probably just messing up the way the loops work (new to this) but having stared at it for a few hours now I can't see it. My intention is to set this to trigger on edit (it's populated from a Google Form), so I've added a column to mark when a Slack message has been sent, just to avoid a mad loop of endless messages for whatever reason.

I've tried logging the output and can get all values there. Putting the post to Slack part of the code in the same place only results in one post, though.

//Set up a variable to show when we've sent a message to Slack
var slackSentText = "Yes";
//This is hte Slack hook
var url = "https://hooks.slack.com/services/tokens!";

function getNewInfo() {
var sheetname = "Info"
var myspreadsheet = SpreadsheetApp.openById('secretID!'); 
  var sheet = myspreadsheet.getSheetByName(sheetname);

// set rows until work out how to do more elegantly...
  var startRow = 8; // First row of data to process
  var numRows = 5; // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 11);
  var data = dataRange.getValues();

  // iterate and make variables for Slack
  for (var i = 0; i < data.length; ++i) {
  var row = data[i];
  var idNumber = row[0]; 
  var projectType=row[6];
  var supplierName=row[2];
  var projectName=row[3];
  var projectDescription=row[4];
  var projectStatus=row[1];
  var projectDate=row[5];
  var slackSent = row[10];

  // check if column is marked with Slack sent note and if so ignore. If not send Slack  
  if (slackSent !== slackSentText) { 

  // Slack bit
  var slackMessage = {
    "channel": "ID-number-bot",
    "username": "ID-bot",
    "text": "\n :white_check_mark:" +
    "\n *New Project:* " + idNumber +
    "\n *Project Name:* "  + projectName +
    "\n *Supplier:* "  + supplierName +
    "\n *Project Status:* "  + "["+projectStatus+"]" +
    "\n *Project Description:* "  + "_"+projectDescription+"_" +
    "\n *Project Type:* "  + projectType +
    "\n *Estimated Delivery Date:* "  + projectDate +
    "\n"+ slackSent +
    "\n Job folder string: \n" + "RCM "+jobNumber+" "+projectName +
    "\n \n"
  };

  var options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(slackMessage)
  };

  return UrlFetchApp.fetch(url,options);

  sheet.getRange(startRow + i, 11).setValue(slackSentText);

Logger.log("jobNumber:"+jobNumber)
Logger.log("projectType:"+projectType)
Logger.log("supplierName:"+supplierName)
Logger.log("projectName:"+projectName)
Logger.log("projectDescription:"+projectDescription)
Logger.log("projectStatus:"+projectStatus)
Logger.log("projectDate:"+projectDate)
      }
    }

//   SpreadsheetApp.flush();


  }

Thanks very much everyone. Hopefully I've given enough info.

Answer 1

As @Tanaike proposed, your problem should be fixed by changing:

return UrlFetchApp.fetch(url,options);

To this:

UrlFetchApp.fetch(url,options)

The return keyword gets you out of the loop. The code finds return in the first iteration, so it doesn't go on with the following rows, and that's why you are only getting the first row sent.