1222.js

const getAddress = id => {
  const ss = SpreadsheetApp.openById(id)
  const list = ss.getSheetByName('list').getDataRange().getValues()
  const contents = ss.getSheetByName('contents').getDataRange().getValues()

  return {
    content: contents.at(0),
    users: list.slice(1)
  }
}
const f2 = () => {
  const id = '1zk_P7pAP3_cjKBnkJdK8v2zc6x6obyOZ_mfznoM0iXI'
  const { content, users } = getAddress(id)

  users.forEach(([mail, ...name]) => {
    const subject = 'Test Mail'
    const message = `${name.join(' ')}様\n${content}`
    console.log(message)
    // GmailApp.sendEmail(mail,content,subject)
  })
}

const getInboxThreads = (start, max) =>
  GmailApp.getInboxThreads(start, max).map(thread => [
    thread.getId(),
    thread.getLastMessageDate(),
    thread.getFirstMessageSubject()
    thread.getMe
  ])

const searchThreads = (query, start = 0, max = 10) =>
  GmailApp.search(query, start, max).map(thread => [
    thread.getId(),
    thread.getLastMessageDate(),
    thread.getFirstMessageSubject()
  ])

const writeInfoToSpread = (id, sheetName) => {
  /*
    演算子
    https://support.google.com/mail/answer/7190?hl=ja
  */
  const query = 'subject:Alert: Suspicious login'
  const sheet = SpreadsheetApp.openById(id).getSheetByName(sheetName)
  sheet.clear()
  const matrix = searchThreads(query, 0, 50)
  const start = [1, 1]
  const rc = [matrix.length, Math.max(...matrix.map(v => v.length))]
  sheet.getRange(...start, ...rc).setValues(matrix)
}

const f1 = () => {
  const id = '1zk_P7pAP3_cjKBnkJdK8v2zc6x6obyOZ_mfznoM0iXI'
  const sheetName = 'mail'
  writeInfoToSpread(id, sheetName)
}

[["Notification from Google\r\n\r\n\r\n\r\nGoogle Workspace Image\r\n\r\n\r\n\r\nThis Suspicious login alert is to inform you that Google has detected a  \r\nsuspicious login in your domain. Google considers login activity suspicious  \r\nif we notice a sign in attempt that doesn't match a user's normal behavior,  \r\nsuch as a sign in from an unusual location, or because we think an  \r\nunauthorized person attempted to access a user's account.\r\n\r\nThe alert details include:\r\n\r\n\r\nUser: [email protected]\r\n\r\n\r\nAttempted Login IP: 57.129.34.255\r\n\r\n\r\nActivity Date: 木曜日, 2023/12/14 14:58:40 (UTC)\r\n\r\n\r\nPlease view the alert center for additional details, investigation options,  \r\nand remediation recommendations.\r\n\r\n\r\n\r\nGo to Alert Center\r\n\r\n\r\n\r\nYou have received this important update about your Google Workspace account  \r\nbecause you are the designated admin recipient for this alert type. You can  \r\nturn off these alerts or change the email recipients in the System defined  \r\nrules section of the Admin Console.\r\n\r\nGoogle LLC 1600 Amphitheatre Parkway Mountain View, CA 94043\r\n\r\n\r\n\r\n\r\n","Notification from Google\r\n\r\n\r\n\r\nGoogle Workspace Image\r\n\r\n\r\n\r\nThis Suspicious login alert is to inform you that Google has detected a  \r\nsuspicious login in your domain. Google considers login activity suspicious  \r\nif we notice a sign in attempt that doesn't match a user's normal behavior,  \r\nsuch as a sign in from an unusual location, or because we think an  \r\nunauthorized person attempted to access a user's account.\r\n\r\nThe alert details include:\r\n\r\n\r\nUser: [email protected]\r\n\r\n\r\nAttempted Login IP: 106.146.189.249\r\n\r\n\r\nActivity Date: 金曜日, 2023/12/15 0:18:11 (UTC)\r\n\r\n\r\nPlease view the alert center for additional details, investigation options,  \r\nand remediation recommendations.\r\n\r\n\r\n\r\nGo to Alert Center\r\n\r\n\r\n\r\nYou have received this important update about your Google Workspace account  \r\nbecause you are the designated admin recipient for this alert type. You can  \r\nturn off these alerts or change the email recipients in the System defined  \r\nrules section of the Admin Console.\r\n\r\nGoogle LLC 1600 Amphitheatre Parkway Mountain View, CA 94043\r\n\r\n\r\n\r\n\r\n"],["Notification from Google\r\n\r\n\r\n\r\nGoogle Workspace Image\r\n\r\n\r\n\r\nThis Suspicious login alert is to inform you that Google has detected a  \r\nsuspicious login in your domain. Google considers login activity suspicious  \r\nif we notice a sign in attempt that doesn't match a user's normal behavior,  \r\nsuch as a sign in from an unusual location, or because we think an  \r\nunauthorized person attempted to access a user's account.\r\n\r\nThe alert details include:\r\n\r\n\r\nUser: [email protected]\r\n\r\n\r\nAttempted Login IP: 165.76.190.61\r\n\r\n\r\nActivity Date: 日曜日, 2023/11/26 1:54:08 (UTC)\r\n\r\n\r\nPlease view the alert center for additional details, investigation options,  \r\nand remediation recommendations.\r\n\r\n\r\n\r\nGo to Alert Center\r\n\r\n\r\n\r\nYou have received this important update about your Google Workspace account  \r\nbecause you are the designated admin recipient for this alert type. You can  \r\nturn off these alerts or change the email recipients in the System defined  \r\nrules section of the Admin Console.\r\n\r\nGoogle LLC 1600 Amphitheatre Parkway Mountain View, CA 94043\r\n\r\n\r\n\r\n\r\n"],["Notification from Google\r\n\r\n\r\n\r\nGoogle Workspace Image\r\n\r\n\r\n\r\nThis Suspicious login alert is to inform you that Google has detected a  \r\nsuspicious login in your domain. Google considers login activity suspicious  \r\nif we notice a sign in attempt that doesn't match a user's normal behavior,  \r\nsuch as a sign in from an unusual location, or because we think an  \r\nunauthorized person attempted to access a user's account.\r\n\r\nThe alert details include:\r\n\r\n\r\nUser: [email protected]\r\n\r\n\r\nAttempted Login IP: 2a01:4f8:c17:58b8:0:0:0:1\r\n\r\n\r\nActivity Date: 木曜日, 2023/11/23 19:21:48 (UTC)\r\n\r\n\r\nPlease view the alert center for additional details, investigation options,  \r\nand remediation recommendations.\r\n\r\n\r\n\r\nGo to Alert Center\r\n\r\n\r\n\r\nYou have received this important update about your Google Workspace account  \r\nbecause you are the designated admin recipient for this alert type. You can  \r\nturn off these alerts or change the email recipients in the System defined  \r\nrules section of the Admin Console.\r\n\r\nGoogle LLC 1600 Amphitheatre Parkway Mountain View, CA 94043\r\n\r\n\r\n\r\n\r\n"]]