Home>

The script that notifies Chatwork of the edited content in GoogleSpreadSheet that succeeded from the predecessor does not work.
* Since the creator's Google account has been deleted, the spreadsheet and script editor were copied to a new file.

(1) Guard section (If it is not time to post, exit the program)
② Confirmation to the user by message box
③ Post a message to chatwork
④Post-processing

This file seems to have been created with the above four processing flows, but when the target SpreadSheet is opened, it jumps to the cell at the bottom again
Only movement has been realized.
Do I want to post if you enter all necessary information in the bottom row? The pop-up appears, but it was not particularly.

Because of the inexperienced program, it is a level that has reached the following error by executing "debug functions" using the net information as a clue.
Since there are unclear errors on how to correct each error, please let me know.
Thank you.

Error message

When I debug each function, I get the following error.

■ Error 1<Error of function getLastRow>
    Range not found
    at getLastRow (Notification: 21)
■ Error 2<function createMessage error>
Method getRange ((class), number, number, number) not found.
    at createMessage (Notification: 143)
■ Error 3<Error of function cronPostIncompleteQuestions>
ReferenceError: "spreadsheet" is not defined.
    at cronPostIncompleteQuestions (Notification: 158)

■ Error 4<function postChatworkMessage error>
The value of the specified attribute is invalid: Header: null
    at postChatworkMessage (Notification: 180)
■ Error 5<Error of function getChatworkMembersInfoByRoomId>
    The value of the specified attribute is invalid: Header: null
    at getChatworkMembersInfoByRoomId (Notification: 195)
Applicable source code
/// **
// * Processing when opening a file
// * Select the bottom row of the sheet.
// * (Because it is cumbersome to scroll to the bottom every time ...)
// * /
function onOpen () {
  if (SpreadsheetApp.getActiveSheet (). getSheetName ()! = SHEET_NAME_INQUIRY) {
    return;
  }
  SpreadsheetApp.getActiveSheet (). SetActiveSelection ("B" + getLastRow ("B"));
  SpreadsheetApp.getActiveSpreadsheet (). Toast ("The cursor has been moved to the bottom line");
}
//
/// **
// * Get bottom line
// * column: column name
// * /
function getLastRow (column) {
  var lastRow = SpreadsheetApp.getActiveSheet (). getMaxRows ();
  var values ​​= SpreadsheetApp.getActiveSheet (). getRange (column + "1:" + column + lastRow) .getValues ​​();
  for (;values ​​[lastRow-1] == ""&&lastRow>0;lastRow--) {}
  return lastRow;
}
/ **
* Event that operates when the value of a sheet changes
* (1) Guard section (If the submission is unnecessary, the program will be terminated without doing anything)* ② Confirmation to the user via message box (whether to post to chatwork/whether to post notification to chatwork)
* ③ Post a message to chatwork
* ④ Post-processing
* /
function onValueEdit (event) {
// function onEdit (event) {
Logger.log ("start");
  / ************************
  (1) Guard section (If it is not time to post, terminate the program)
  ************************ /
  // Get inquiry list sheet
  const inquiriesSheet = SpreadsheetApp.getActiveSpreadsheet (). getSheetByName (SHEET_NAME_INQUIRY);
  // Get the edited cell
  try {
    const editedRange = event.source.getActiveRange ();
  } catch (e) {
    // Occasionally an error [Please select a valid sheet first. ]
    // There is no problem but the error notification email is in the way
    Logger.log (e);
    return;
  }
  Logger.log ("check");
  const editedRow = editedRange.getRow ();
  const editedColumn = editedRange.getColumn ();
  // 1. If not the target sheet, finish
  if (editedRange.getSheet (). getSheetId ()! = inquiriesSheet.getSheetId ()) {return;}
  // 2. Exit if chatwork notification status of the entered line is [Completed]
  if (inquiriesSheet.getRange (editedRow, COLUMN_CHATWORK_POST_STATUS) .getValue () === "Done") {return;}
  // 3. If the entered cell row is not an inquiry field, exit
  if (editedRow<ROW_FIRST_DATA) {return;}
  // 4. If the entered cell column is not [Required inquiry field], end
  if (editedColumn<COLUMN_FIRST_REQUIRED || editedColumn>COLUMN_LAST_REQUIRED + 1) {return;}
  // 5. If all the [Mandatory inquiry field] columns in the entered cell row have not been entered, finish
  const requiredValues ​​= inquiriesSheet.getRange (editedRow, COLUMN_FIRST_REQUIRED, 1, COLUMN_LAST_REQUIRED-COLUMN_FIRST_REQUIRED + 1) .getValues ​​();
  for (var i = 0;i<requiredValues ​​[0] .length;i ++) {
    if (! requiredValues ​​[0] [i] .toString ()) {
      return;
    }
  }
  / ************************
  (2) Confirmation to the user using a message box
  ************************ /
  // Check if everyone is To
  var selection = Browser.msgBox ("[Confirm chatwork notification]", "Notify chatwork \\ n \\ nDo I want to post to all people? \\ nYes: To everyone Post a message by specifying To \\ n ・ No: Post only a message without specifying To \\ nCancel: Quit without posting ", Browser.Buttons.YES_NO_CANCEL);
  // If cansel, exit without notification
  if (selection === "cancel") {
    SpreadsheetApp.getActiveSpreadsheet (). Toast ("Did not notify");
    return;
  }
  // Decide whether or not to perform Chatwork To notification, depending on user selection
  var notifyAllMembers = selection == "yes"? true: false;
  / ************************
  ③ Post a message to chatwork
  ************************** /
  // Create a chatwork message
  var body = createMessage (notifyAllMembers, editedRow);
  // Get the required information from the chatwork notification settings sheet
  const configSheet = SpreadsheetApp.getActiveSpreadsheet (). getSheetByName (SHEET_NAME_CHATWORK_CONFIG);const API_TOKEN = configSheet.getRange (RANGE_API_TOKEN) .getValue ();
  const ROOM_ID = configSheet.getRange (RANGE_ROOM_ID) .getValue ();
  // Post a message
  postChatworkMessage (API_TOKEN, ROOM_ID, body);
  / ************************
  ④ Post-processing
  ************************ /
  // Display post completion message on screen
  SpreadsheetApp.getActiveSpreadsheet (). Toast ("Posted to Chatwork");
  // Put the posted flag on the sheet
  inquiriesSheet.getRange (editedRow, COLUMN_CHATWORK_POST_STATUS) .setValue ("Done");
}
/ **
* Post message creation
*
*-NotifyAllMembers: Whether to notify To members (true: Notify To/false: Do not notify To)
* ・ EditedRow: Row number where editing was performed
* /
function createMessage (notifyAllMembers, editedRow) {
  / ** ②Compose post message ** /
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet ();
  // Get inquiry list sheet
  const inquiriesSheet = spreadsheet.getSheetByName (SHEET_NAME_INQUIRY);
  // Get the required information from the chatwork notification settings sheet
  const configSheet = spreadsheet.getSheetByName (SHEET_NAME_CHATWORK_CONFIG);
  // Create post body
  var body = "";
  // When performing To notification, get AccountID of all members who belong to the corresponding room of chatwork and add To message
  if (notifyAllMembers) {
    const API_TOKEN = configSheet.getRange (RANGE_API_TOKEN) .getValue ();
    const ROOM_ID = configSheet.getRange (RANGE_ROOM_ID) .getValue ();
    // Get name/account ID of all members belonging to the room with chatwork API
    const membersInfo = getChatworkMembersInfoByRoomId (API_TOKEN, ROOM_ID);
    // Create message from API response
    var toMembers = "";
    for (var i = 0;i<membersInfo.length;i ++) {
      Logger.log (membersInfo [i] .name);
      toMembers + = "[To:" + membersInfo [i] .account_id + "]" + membersInfo [i] .name + "Ms \ n";
    }
    body + = toMembers + "Determine the person in charge and list the person in charge in the spreadsheet (bow)";
  }
  // Create message part (take template from sheet and replace)
  var message = configSheet.getRange (RANGE_MESSAGE_TEMPLATE) .getValue ();
  // get all data in the input row
  var lastColumn = inquiriesSheet.getLastColumn ();
  var targetRowValues ​​= inquiriesSheet.getRange (editedRow, 1, 1, lastColumn) .getValues ​​() [0];
  // Get one line above the first data input line as the replacement target string
  var replaceValues ​​= inquiriesSheet.getRange (ROW_FIRST_DATA -1, 1, 1, lastColumn) .getValues ​​() [0];
  // Replace all character strings in the message template with the input data
  for (var j = 0;j<lastColumn;j ++) {
    message = message.replace (new RegExp ("%" + replaceValues ​​[j] + "%", 'g'), targetRowValues ​​[j]);
  }
  // Add the replacement message to the post body
  body + = message;
  return body;
}
function cronPostIncompleteQuestions () {
  // Get inquiry list sheetconst inquiriesSheet = spreadsheet.getSheetByName (SHEET_NAME_INQUIRY);
  // Get the required information from the chatwork notification settings sheet
  const configSheet = spreadsheet.getSheetByName (SHEET_NAME_CHATWORK_CONFIG);

}
/ **
* Post message to chatwork
* ・ ApiToken: chatwork API Token
* ・ RoomId: chatwork Room number to be posted (part after rid of URL end #! Rid *********)
* ・ Body: Post message
* /
function postChatworkMessage (apiToken, roomId, body) {
  var params = {
    headers: {"X-ChatWorkToken": apiToken}
    , method: "post"
    , payload: {
      body: body
    }
  };
  var url = "https://api.chatwork.com/v2/rooms/" + roomId + "/ messages";
  UrlFetchApp.fetch (url, params);// Request to chat work API
}
/ **
* Get information about users belonging to the chatwork room
* ・ ApiToken: chatwork API Token
* ・ RoomId: chatwork Room number for which information is to be acquired (the part after rid of URL end #! Rid *********)
* /
function getChatworkMembersInfoByRoomId (apiToken, roomId) {
  var params = {
    headers: {"X-ChatWorkToken": apiToken}
    , method: "get"
  };
  const url = "https://api.chatwork.com/v2/rooms/" + roomId + "/ members";
  const response = UrlFetchApp.fetch (url, params);// Request to chat work API
  const result = JSON.parse (response.getContentText ());
  return JSON.parse (response.getContentText ());
}
// first query data line
var ROW_FIRST_DATA = 4;
// first mandatory query entry line
var COLUMN_FIRST_REQUIRED = 3;
// Last required query entry line
var COLUMN_LAST_REQUIRED = 10;
// chatwork post status column
var COLUMN_CHATWORK_POST_STATUS = 12;

/ ** Cell address in the setting sheet ** /
// API TOKEN description cell
var RANGE_API_TOKEN = "C2";
// RoomID description cell
var RANGE_ROOM_ID = "C3";
// Post message message cell
var RANGE_MESSAGE_TEMPLATE = "C4";
var SHEET_NAME_CHATWORK_CONFIG = "chatwork notification setting";
var SHEET_NAME_INQUIRY = "Inquiry List";
  • Answer # 1

    According to macaron_xxx's advice, by setting onValueEdit as a trigger,
    Now works properly. Thank you very much.

Related articles