用于根據單元格顏色和名稱發送電子郵件的Google表格腳本-有解無憂

我有一個電子表格，里面有兩張像這樣的作業表：

用于根據單元格顏色和名稱發送電子郵件的 Google 表格腳本

我正在嘗試撰寫一個執行以下操作的腳本：

在日期列中查找與今天日期匹配的值
如果找到匹配項，請在該行中的不同名稱下查找具有白色背景 (#ffffff) 的空白單元格
如果找到匹配項，請向具有該特定名稱的經理發送電子郵件（例如，在 B2 中，Jack 有一個白色單元格，因此向 Jack 的經理發送電子郵件（[email protected]；從“聯系人”表中拉出） )

目前我有以下代碼：

function sendEmail3() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Main');
  const data = sheet.getRange(2,1,27,7).getValues();

// console.log(data);

 data.forEach(row => {
const sysDate = new Date();
  sysDate.setHours(0);
  sysDate.setMinutes(0);
  sysDate.setSeconds(0); 

const sheetDate = new Date(row[0])

// format both system date and sheet date so that they are identical strings
const sysDateFormatted = Utilities.formatDate(sysDate, "GMT-0700", "MM/dd/yyyy");
const sheetDateFormatted = Utilities.formatDate(sheetDate, "GMT-0700", "MM/dd/yyyy");

// a simple if statement with a console log. This needs to be replaced
// if the dates match, then look in that row for blank cells with white backgrounds (#ffffff)
 if (sysDateFormatted == sheetDateFormatted) {
  console.log("They are equal");
} else {
  console.log("they are not equal");
}

// I tried the following and was able to get the backgrounds for each cell in this range
const specificData = sheet.getRange(2,2,999,6) // this range doesn't include the headers
const dataColor = specificData.getBackgrounds();
console.log(dataColor);

})
}

我似乎無法將所有部分放在一起。如果有人能幫我理清這個邏輯，我將不勝感激！謝謝！

uj5u.com熱心網友回復：

不要在回圈中包含不變的變數
得到的日期getValue/getValues已經是date物件
formatDate與MM/dd/yyyy是不是指時間所以沒必要setHours/ setMinutes/setSeconds

function sendEmail3() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Main');
  const range = sheet.getDataRange();
  const data = range.getValues();
  const dataColor = range.getBackgrounds();

  const contact = ss.getSheetByName('Contact').getDataRange().getValues();
  const manager = {};
  for (const row of contact) {
    manager[row[0]] = {
      name: row[1],
      email: row[2]
    };
  }

  const sysDate = new Date();
  const sysDateFormatted = Utilities.formatDate(sysDate, "GMT-0700", "MM/dd/yyyy");

  for (let i = 1; i < data.length; i  ) {
    const sheetDate = data[i][0];
    const sheetDateFormatted = Utilities.formatDate(sheetDate, "GMT-0700", "MM/dd/yyyy");

    if (sysDateFormatted === sheetDateFormatted) {
      for (let j = 1; j < data[0].length; j  ) {
        if (dataColor[i][j] === '#ffffff') {
          const name = data[0][j];
          sendMail(name);
        }
      }
      break;
    }
  }

  function sendMail(name) {
    const subject = '...';
    const body = `...${name}...`;
    GmailApp.sendEmail(manager[name].email, subject, body);
  }
}

轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/384485.html

標籤：谷歌应用程序脚本谷歌表格

上一篇：GoogleAppsScript-如何從Sheet2獲取電子郵件地址并發送電子郵件

下一篇：可能回圈的資料驗證