Home>

I want to implement a function to notify the chat work when the Google form is answered
thinking about.
Answer sheets of multiple forms are combined into one spreadsheet,
When I get an answer (when a row is added) I want to pick up some information and drop it in the notification room.

Another worker assembled another spread sheet that tells the same room with exactly the same seat configuration.
I copied and created the code, but it didn't notify me well and I don't know what caused it.

Applicable source code
/*
Application notification from the dedicated form
*/
function autoreply() {
  /*
  newInquireNotice13();
  newInquireNotice14();
  */
}

  /*
Application notification from the dedicated form
*/
 function newInquireNotice13() {
  // Get sheet information
  //Specify a spreadsheet to work on
  var ss = SpreadsheetApp.openById("********");
  var sheet = ss.getSheetByName('sheet1');
  // Function to search and return the label column
  function colSearch(label) {
    for (i = 1;i<= sheet.getLastColumn();i++) {
      if (sheet.getRange(1, i).getValue() == label) (
        return i;
      }
    }
  }
  // number of columns for company, name, noticed
  var companyCol = colSearch("company");
  var nameCol = colSearch("name");
  var noticedCol = colSearch("noticed");
  var supplypointCol = colSearch("supplypoint");
  var urlCol= colSearch("url");
  var dayCol= colSearch("day");
  // If the latest line noticed is not true, send a notification and make it true
  function newNotice() {
    lastRow = sheet.getRange("B:B").getValues().filter(String).length;// last row of column B
    var thisNoticed = sheet.getRange(lastRow, noticedCol);
If (thisNoticed.getValue() !== true) {
    var name = sheet.getRange(lastRow, nameCol).getValue();
    var company = sheet.getRange(lastRow, companyCol).getValue();
    var supplypoint = sheet.getRange(lastRow, supplypointCol).getValue();
    var pcpurl = sheet.getRange(lastRow, pcpurlCol).getValue();
    var saitenday = sheet.getRange(lastRow, saitendayCol).getValue();
    var message = "[info][title] form application [/title]"
      + "" + company + "" + name + "we received a request.\n" + "・Location: "+" "+supplypoint +"\n"
      + "・URL :"+" "+url +"\n"
      + "・Scheduled date: "+" "+ Utilities.formatDate(day,'JST','yyyy/MM/dd') + "[/info]"
     // set notified to true
   sheet.getRange(lastRow, noticedCol).setValue(true);
    // Post by specifying the room to post
  var room_ID = room ID;//https://www.chatwork.com/#!rid*****
  var token ='API token substitution';//your token
  var params = {
    headers :{"X-ChatWorkToken" :token}, // API token
    method :"post",
    payload :{
      body: message, // task content
      to_ids: Your account ID
    }
  };
  var url = "https://api.chatwork.com/v2/rooms/" + room_ID + "/messages";
  UrlFetchApp.fetch(url, params);// Request to chat work API
   }
  }
   newNotice();
   }
What I tried

Set up API connection to chat work so that you can use your own account
I included it in the code, but it didn't drop into the chat.

  • Answer # 1

    If you specify "from form-when sending form" in the project trigger, you can get the value sent from the argument of the specified function.
    It can be unconfirmed, but it may have not been written to the spreadsheet yet (or written asynchronously) when this trigger is executed.

    ↓ ↓ A sample that sends the contents of the reply to your email address

    /**
     * Process started from "From form-When sending form" trigger
     * @param {GoogleAppsScript.Events.FormsOnFormSubmit} e Event information
     */
    function onSend(e) {
      const responses = {};
      e.response.getItemResponses().forEach(item =>{
        const title = item.getItem().getTitle();
        const res = item.getResponse();
        responses[title] = res;
      });
      const body = JSON.stringify(responses, null, 4);
      GmailApp.sendEmail("(your email address)", "test form", body);
    }


    ↓↓ Sample email sent

    {
        "Description": "aaaaaaa",
        "Radio Button": "Option 3",
        "Checkbox": [
            "Option 1",
            "Option 2"
        ],
        "Pulldown": "Option 1"
    }

    reference:
    Google Forms events
    https://developers.google.com/apps-script/guides/triggers/events?hl=en#google_forms_events

    Class FormResponse
    https://developers.google.com/apps-script/reference/forms/form-response


    (Addition about confirmation of response)

    What kind of operation should be performed to confirm the return value (response) of UrlFetchApp.fetch?

    const response = UrlFetchApp.fetch( .... );
    if(response.getResponseCode() !== 200) {
      throw new Error(response.getContentText());
    }