google-apps-script - 在使用 Apps 脚本在 Google 表格中进行 API 调用后，在检测数据变化时标记单元格

Question

我正在创建一个项目，我必须调用第 3 方 API，该 API 提供有关某个地方不同酒店的不同详细信息并将数据存储在 Google 表格中。其中，不断变化的数据字段有：

• 一个房间的价格
• 房间的可用性
• 租期长度等

基于此，我必须执行以下任务：

1. 创建一个每天自动运行数据获取功能的触发器（这很可能可以通过创建触发元素的函数来完成）
2. 将更新的数据存储在相应的工作表中
3. 将新数据与工作表中的现有数据进行比较
4. 标记数据已更改的单元格（可能通过为单元格或行着色）例如，如果价格列中的一个单元格值从 2 更改为 3，我将该单元格着色为绿色。另外，下次我再次调用该函数时，如果再次没有检测到变化，颜色应该会变回白色。
5. 还有一列change_timestamp将更新最后一次更改
6. 最后，向某人发送电子邮件，说明特定工作表和特定行中的值已更改

我正在添加用于从特定酒店管理组的 API 获取数据的代码片段

const unitedss=SpreadsheetApp.getActive();
const uniteSheet=unitedss.getSheetByName("Unite_API Test"); //there are multiple sheets in the same spreadsheet which I access individually through the given function
uniteSheet.activate();

const cities=["aberdeen","bath","bedford","birmingham","bournemouth","bristol","cardiff","Coventry","Durham","Exeter","Edinburgh","Glasgow","leeds","Leicester","london","liverpool","Loughborough","Manchester","Medway","Newcastle","Nottingham","Oxford","Portsmouth","Reading","Sheffield","Southampton","Wolverhampton"];

//the header row 
let uniteHeader=[["property","property_id","room","room_id","total_rooms","tenancy","tenancy_id","academic_year","check_in","min_check_in","max_check_in","check_out","min_check_out","max_check_out","duration","duration_unit","price","price_duration_unit","deposit","currency","availability","data_added_timestamp","change","change_timestamp","amber_url"]];
uniteSheet.appendRow(uniteHeader);

//function to add header
function addUniteHeader(){
  let range=uniteSheet.getRange(1,1,1,uniteHeader[0].length);
  range.setValues(uniteHeader);
  range.setFontWeight("bold");
  uniteSheet.setFrozenRows(1);
}


function getAllUniteData() {
  let range=uniteSheet.getRange(2,1,uniteSheet.getLastRow()-1,uniteHeader[0].length);
  range.clear();   //clears the previous data that existed in the sheet except the header row
  
  //create the matrix
  for(let city=0;city<cities.length;city++){
    let array=[]; //a 2-D array which will store all the data
    let reqCity=cities[city].toLowerCase();
    let data=JSON.parse(UrlFetchApp.fetch(`https://myApiEndPoint/cities/${reqCity}`).getContentText());
    let properties=data.PropertyData;   //[it is an array of objects]
    if(properties===undefined)continue;

    for (let prop=0;prop<properties.length;prop++){
      let propName=properties[prop].PropertyName;  //name of the property
      let propID=properties[prop].PropertySerial;  //id of the property
      let x=encodeURIComponent(propName);
      let propData=JSON.parse(UrlFetchApp.fetch(`https://myApiEndPoint/cities/${cities[city]}/properties/${x}`).getContentText());   //[returns an object]
    if(propData===undefined)continue;
      let roomInfo=propData.RoomData; //[an array of objects]
    if(roomInfo===undefined)continue;

    for(let r=0;r<roomInfo.length;r++){
        let typeGroup=roomInfo[r].TypeGroupName; //name of roomType to which this object pertains
        let details=roomInfo[r].RoomTypeAndClasses; //an array of objects 
    if(details===undefined)continue;
        for(let i=0;i<details.length;i++){

            let classOfRoom=details[i].RoomClassName;
            let roomType=details[i].RoomType; 
            let finalRoom=classOfRoom+" "+roomType; //the room type
            let roomID=createRoomID(propID,i+1);  //id of room
            let tenancies=details[i].TenancyTypes; //an array of tenancy objects applicable to this room type and class
            if(tenancies===undefined)continue;

            for(let ten=0;ten<tenancies.length;ten++){
                //tenancies[ten] is an object
                let tenancyName=tenancies[ten].Name;
                let tenancyID="";  
                let tenancyAvail=tenancies[ten].isSoldOut?"NO":"YES"; 
                let availBands=tenancies[ten].AvailabilityBands;  //an array of objects
                if(availBands===undefined)continue;

                for(let j=0;j<availBands.length;j++){

                    let availability=availBands[j]; //an object which gives availability details
                    let acadYr=availability.AcademicYear;
                    let min_check_in=(new Date((availability.StartBand.S)*24*60*60*1000)).toDateString();
                    let checkin=((new Date((availability.StartBand.D)*24*60*60*1000))).toDateString();  
                    let max_check_in=((new Date((availability.StartBand.E)*24*60*60*1000))).toDateString();
                    let min_check_out=((new Date((availability.EndBand.S)*24*60*60*1000))).toDateString();
                    let checkout=((new Date((availability.EndBand.D)*24*60*60*1000))).toDateString();
                    let max_checkout=((new Date((availability.EndBand.E)*24*60*60*1000))).toDateString();
                    let price=availability.PricePerWeek; 
                    let price_duration="week";
                    let deposit_amnt=0; 
                    let duration=availability.Weeks;     
                    let durationUnit="weeks";
                    let totalRooms=availability.TotalRooms; 
                    let dateAdded=new Date().toLocaleString('en-GB',{timeZone: 'Asia/Kolkata'});

                    let dataRow=[propName,propID,finalRoom,roomID,totalRooms,tenancyName,tenancyID,acadYr,checkin,min_check_in,max_check_in,checkout,min_check_out,max_checkout,duration,durationUnit,price,price_duration,deposit_amnt,"Pounds",tenancyAvail,dateAdded,"NO","",""];
                    array.push(dataRow);
                    //in the above row fields like price, deposit_amnt, tenancyAvail change regularly 
                }
            }
        }
      }

    }
  if(array===undefined||array[0]===undefined)continue;
  uniteSheet.getRange(uniteSheet.getLastRow() + 1, 1, array.length, array[0].length).setValues(array); //populate the sheet with values of the 2-D array
  }

}


//A trigger which can run the function everyday 
function setUpTrigger(){
    ScriptApp.newTrigger("getAllUniteData")
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .inTimeZone('India/Kolkata')
    .create();
}


//deletes all the triggers
function deleteTrigger() {
  
    // Loop over all triggers and delete them
    var allTriggers = ScriptApp.getProjectTriggers();
    
    for (var i = 0; i < allTriggers.length; i++) {
      ScriptApp.deleteTrigger(allTriggers[i]);
    }
  }

调用getAllUnitedData函数后，我的工作表前几行的图像如下所示：

（注意3张图片中header的所有列都属于SAME sheet的SAME行）

我想调用SetUpTrigger()一次，之后它将每天自动运行函数 getAllUnitedData()。现在，我的主要问题是执行步骤3-6 ，即。将新数据与以前的数据进行比较，根据数据更改标记单元格，并发送电子邮件列出更改的单元格。

我是 Apps Script 的新手，正在慢慢学习。但是，即使经过大量搜索，我也找不到解决此特定问题的方法。请帮我解决这个问题。如果需要，我很乐意提供任何其他详细信息。

标签： google-apps-scriptgoogle-sheetsgoogle-api