I was wondering if anyone could help build a script for a Macro Extension on my google sheet. We use sheets to track application statuses and would like an automation to be set.

My Goal: I would like any application that is still in the "Pre-IC" agreement (column N) after 30 days of their application date(column H) to be automatically changed to "Delayed" in column N.

I have attached a screenshot to this, please let me know if further information is needed in order to build this script -- I'm a newbie at this I really need support

CodePudding user response：

Description

You can use a Time Driven Trigger to run unattended periodically. You do this from the Script Editor Triggers.

The following script can be set to run daily or more frequently as needed.

I have created a simple mock up of your sheet with only 2 columns that you were interested in.

Code.gs

function statusTimer() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    let values = sheet.getRange(7,1,sheet.getLastRow()-6,sheet.getLastColumn()).getValues();
    let today = new Date();
    for( let i=0; i<values.length; i   ) {
      if( values[i][1] ===  "Pre IC Agreement" ) {
        if( today.valueOf()-values[i][0].valueOf() > (30*24*60*60*1000) ) {  // valueOf is in milliseconds
          values[i][1] = "Delayed";
        }
      }
    }
    values = values.map( row => [row[1]] ); // extract only 2nd column
    // Note my test sheet has only 2 columns.  You need to adjust getRange() for your case
    sheet.getRange(7,2,values.length,1).setValues(values);
  }
  catch(err) {
    console.log(err);
  }
}

Reference

• Installable Trigger
• Spreadsheet Service
• Date Object
• Array.map()

CodePudding user response：

Try

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  var d = new Date();
  var oneMonthAgo = new Date();
  oneMonthAgo.setDate(d.getDate() - 30)
  const date = sh.getRange('H7:H' sh.getLastRow()).getValues()
  const status = sh.getRange('N7:N' sh.getLastRow()).getValues()
  status.forEach(function(r,i){
    if (r[0] == 'Pre-IC Agreement' && date[i][0] < oneMonthAgo){
      r[0] = 'Delayed'
    }
  })
  sh.getRange('N7:N' sh.getLastRow()).setValues(status)
}