retrieves all the values in a row in a particular column

[nagastar]

i want to fetch all data in row column B and try to send it to chatwork chat application.

but I can't fetch all the data at once to send. I want the data like the one in the picture to be combined, all the data in the column into 1 message to be sent.

for example the expected results when displayed in the chat chatwork application.
this is task today ;
- cleaning room
- cleaning toilets
- cleaning garden

this is my code:

  function sendAllRowsToChatworkExceptLastRow() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = spreadsheet.getSheetByName('GAS');

  var lastRow = sheet.getLastRow();

  for (var i = lastRow; i > 1; i--) {

    var content = sheet.getRange(i, 2).getValue();

    // Create a message to send to ChatWork.

    var message = "[info][title]this is task today ; [/title]" +

      "Content: " + content + "\n" +

      "[/info]";

    // Send the message to ChatWork.

    var client = ChatWorkClient.factory({token: "your token"});

    client.sendMessage({room_id: "room_id", body: message});

  }

}

this is my data in google sheet:

[Emerson Maia]

It will be something like this

function sendAllRowsToChatworkExceptLastRow() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('GAS');

  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();

  for (let i = lastRow; i > 1; i--) {

    const rowValues = sheet.getRange(i, 1, 1, lastColumn).getValues()[0];

    // Create a message to send to ChatWork.

    const message = `[info][title]this is task today ; [/title]
      Content: ${rowValues.join(", ")}

      [/info]`;

    // Send the message to ChatWork.

    const client = ChatWorkClient.factory({token: "your token"});

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b200b6a6-ccb0-4f5d-820f-983aceedc45en%40googlegroups.com.

[nagastar]

thankyou for your answered sir..

but the message display is still not merged from all rows of columns when I run your script. this is screenshot:

I want the three lines of data to be combined into one message.

So like this in one message only:

thisis task today:

 cleaning room

cleaning toilets
cleaning garden

[nagastar]

thankyou for your answered sir..

but the message display is still not merged from all rows of columns when I run your script. this is screenshot:

I want the three lines of data to be combined into one message.

So like this in one message only:

thisis task today:

 cleaning room,

cleaning toilets,
cleaning garden,

On Sunday, July 30, 2023 at 7:51:51 AM UTC+9 maiaeme...@gmail.com wrote:

[Ed Sambuco]

It really looks as though you are overdoing this.

You need to build the message to chat differently-

- Start the message with the header before you loop through the rows.

-  In the loop, you are only interested in column B, so no getLastColumen, the range is (i,2)

- Concatenate the contents of column B into the message for each row:  message ++ (contents of column B in given row)

- Take the sendMessage OUT of the loop and run it once at the end of the script.

I would recommend not using const, but rather var or let.  Just  preference, but const is for truly constant values.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/89ae9bff-21d4-40af-91bf-92a05a647a16n%40googlegroups.com.