Pulling all data from a column

Question

tl:dr - I have a code that can work but want to make it better by pulling everything from column E into 1 message (including header)

---

I am trying to get the output of a singular column to post to Discord - Here I have the script creating a message in a blank column and looking at the next empty line

function postFromSpreadsheet(){
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName ("Responses")

  const messageCell = ws.getRange(ws.getMaxRows(),152).getNextDataCell(SpreadsheetApp.Direction.UP).offset(1,-147)

  const currentMessage = messageCell.getValue()

  if(currentMessage == "") return

  postToDiscord(currentMessage)

  messageCell.offset(0,147).setValue("posted")

I could do this but would need to loop it

Or

I am trying to take all the data from column 5 (E) and posting it as one message

I have the discord message posting current message and a if statement to stop once the column I am currently using says posted

Answer 1

To combine all messages into one long message, use Range.getDisplayValues() and Array.join(). To weed out blanks, use Array.filter(String). To set new statuses, use Array.forEach().

Alternatively, to post each message separately, put postToDiscord() inside the forEach() loop.

Finally, to save the new statuses, use Range.setValues(), like this:

function postFromSpreadsheet() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('Responses');
  const messages = sheet.getRange('E1:E').getDisplayValues().flat();
  const statusCells = sheet.getRange('EV1:EV');
  const statuses = statusCells.getValues();

  // post one long message
  const longMessage = messages
    .filter(String)
    .join('\n');
  postToDiscord(longMessage);
  messages.forEach((message, index) => {
    if (message) {
      statuses[index] = ['posted'];
    }
  });
  statusCells.setValues(statuses);

  // another option: post each non-blank message separately
  messages.forEach((message, index) => {
    if (message) {
      postToDiscord(message);
      statuses[index] = ['posted'];
    }
  });
  statusCells.setValues(statuses);
}