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



I could do this but would need to loop it


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

  • This question has been bumped by Community user three times. I will remove discord because this question doesn't require any knowledge or skill specific to Discord. On the other hand, I'm adding javascript because the question is about writing a function using JavaScript.
    – Rubén
    Oct 28 at 14:57

1 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
  messages.forEach((message, index) => {
    if (message) {
      statuses[index] = ['posted'];

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.