Google Scripts message to discord. How to make it more efficient

Xiael 0
Viewed 58

I've got this code to send the message, but I know that there has to be a more efficient way to do so, rather than a new message for every section. Trying it that way is overloading the webhook and causing me to be rate limited.

I've edited to include the full code, minus the webhook URLs. I'm fairly new to coding, so I'm sure it's a mess, but I know it was at least working up to a point because I was testing as I went. It's mainly the sending back part that isn't working how I intended.

function Insert_Checks() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  var ws = ss.getActiveSheet()
  var range = ws.getRange(2,2,ws.getLastRow() - 1)
  var rangec = range.offset(0,1)
  rangec.insertCheckboxes()
}
function Supply_Order_Complete() {
    const ss = SpreadsheetApp.getActiveSpreadsheet()
    var ws = ss.getActiveSheet()
    var range = ws.getRange(2,1,ws.getLastRow())
    num_rows = Logger.log(range.getNumRows())
    const rows = ws.getDataRange().getValues()
    var headers = rows.shift();
    var ordered = []
    var subs = []
    var notes = []

    rows.forEach(function(row) {
      i = 0
      if(row[2] || row[4] != "") {
        ordered.push(row[1].getValue)
        subs.push(row[3].getValue)
        notes.push(row[4].getValue)
      }
      while (i <= ordered.length) {
        var currentMessage = []
        currentMessage.push("'ordered[i]' - 'subs[i]' - 'notes[i]'\n")
        i = i + 1
    }
    Send_Order(currentMessage)
    });
}
function Send_Order(currentMessage) {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  var ws = ss.getActiveSheet()
  var wsn = ws.getName()
  if (wsn === "9th") {
    url = 
  }
  if (wsn === "Apartments") {
    url = 
  }
  if (wsn === "Billings") {
    url = 
  }
  if (wsn === "ElmA") {
    url = 
  }
  if (wsn === "ElmB") {
    url = 
  }
  if (wsn === "Knollview") {
    url = 
  }
  if (wsn === "Centennial") {
    url = '
  }
  if (wsn === "Michigan") {
    url = 
  }
  if (wsn === "Scammon") {
    url = 
  }
  if (wsn === "Adams") {
    url = 
  }
  if (wsn === "Test") {
    url = 
  }
  const message = {
    content: currentMessage
  }
  const options = {
    'method' : 'post',
    'payload' : message
  }
  const res = UrlFetchApp.fetch(url,options)
}```

What I am getting on Discord is this: 
[Ljava.lang.Object;@2eced131
[Ljava.lang.Object;@5b8e5a90
[Ljava.lang.Object;@1b09921c
[Ljava.lang.Object;@4037c438
[Ljava.lang.Object;@24727d3e

Each one being a separate message rather than one message.
[![enter image description here][1]][1]


  [1]: https://i.stack.imgur.com/FmWJH.png
google-apps-script
2 Answers
const ss = SpreadsheetApp.getActive()
  var sh = ss.getActiveSheet()
  var vs = sh.getRange(3, 1, sh.getLastRow() - 2).getValues().map((r, i) => {
    if (!r[2] || !r[4]) {
      return [r[1], r[3], r[4]]
    }
  }).filter(e => e[0]);
user30hu0

Modification points:

  • In your showing script, it seems that you want to use the WebHook URL by the active sheet. And, it seems that you want to send the values like "column B" - "column D" - "column E" when the values of columns "B" or "E" are existing. I understood your goal like this.
  • When I saw your script, I thought that the script of rows.forEach(function(row) {,,,}) is required to be used. For example, I think that row[#].getValue is undefined. And, currentMessage is [ '\'ordered[i]\' - \'subs[i]\' - \'notes[i]\'\n' ]. I think that this is the reason for [Ljava.lang.Object;@###.
  • And, in order to avoid the error of You are being rate limited., how about using "embeds"? Ref

When these points are reflected in your script, how about the following modification?

Modified script:

Please set your WebHook URLs for each key of obj.

function sample() {
  // Please set URLs to obj.
  const obj = {
    "9th": "###url###",
    "Apartments": "###url###",
    "Billings": "###url###",
    "ElmA": "###url###",
    "ElmB": "###url###",
    "Knollview": "###url###",
    "Centennial": "###url###",
    "Michigan": "###url###",
    "Scammon": "###url###",
    "Adams": "###url###",
    "Test": "###url###"
  };

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getActiveSheet();
  const rows = ws.getDataRange().getDisplayValues();
  const fields = rows.reduce((ar, row, i) => {
    if (row[2] || row[4]) {
      ar.push({ name: i + 1, value: `${row[1]} - ${row[3]} - ${row[4]}\n` });
    }
    return ar;
  }, []);
  const max = 25;
  const splitted = [...Array(Math.ceil(fields.length / max))].map(_ => fields.splice(0, max));
  const url = obj[ws.getSheetName()];
  if (url) {
    splitted.forEach(fields => {
      var res = UrlFetchApp.fetch(url, { payload: JSON.stringify({ content: "", embeds: [{ fields }] }), contentType: "application/json" });
      Utilities.sleep(5000); // Please adjust this wait time.
    });
    return;
  }
  console.log(`There is no URL of '${ws.getSheetName()}'.`);
}
  • The values of name and value of { name: i + 1, value: ${row[1]} - ${row[3]} - ${row[4]}\n } are the sample values. So, please modify this for your actual situation.

Note:

  • In this case, 25 items are the maximum number of items for embeds. So, in this sample script, the values are sent every 25 rows. And, a wait time of Utilities.sleep(5000) is used for every request.
    • Please adjust the wait time of 5000 for your actual situation.

References:

Tanaike0
Related