Search code examples
google-sheetsgoogle-apps-scriptweb-applications

Appscript Deployment Not running Macros


I was given a spreadsheet with app scrip macros that we would like to use from a friend.

The purpose of the spreadsheet is to display datetime for follow ups over certain time periods(i.e. in 2 weeks or in 8 hours) in different time zones(PST/MST/CST/EST). All of the macro files in the appscript look good and the manifest file also looks right but when I deploy it doesn't update any of the cells in the sheets. If i run the individual macros they update the cell in the sheet that its supposed to. It is supposed to continuously update the date times to current time or at least on sheet open.

I'm new to google app script but have an ok coding background and I feel like this should be something simple. I may be missing a run file or a call to run but im not sure where that should be added. Can add any other relevant info if needed.

JSON Manifest file sample:

{
  "timeZone": "America/Toronto",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "dependencies": {
    "libraries": [
      {
        "userSymbol": "FollowupTagfilterforBCTeam",
        "libraryId": "186UPce75w8KpinMaHxLVgk494S7nthWonp-Iruh_Yo0PsnXqWb2qtBrl",
        "version": "0",
        "developmentMode": true
      }
    ]
  },
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "ANYONE_ANONYMOUS"
  },
  "sheets": {
    "macros": [
      {
        "menuName": "updateCSTTime10days",
        "functionName": "updateCSTTime10days"
      },```

Macro File sample for above:

function updateCSTTime10days() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CST");
  var cstTime = new Date(new Date().getTime() + (240 * 60 * 60 * 1000)); // Adding 240 hours     for CST
  var formattedDate = cstTime.toLocaleString("en-US", {timeZone: "America/Chicago", year:     'numeric', month: '2-digit', day: '2-digit', hour: '2-digit', minute: '2-digit', second: '2-    digit'});
  sheet.getRange("B6").setValue(formattedDate);
}

Tried running individually and the macros work. Tried doing triggers but there are hundreds and started to fail some on open(also think there is a smarter way to call them all rather than a trigger for each) Tested different deployments and made sure the library lined up


Solution

  • Fixed by creating a few new scripts, took a bit of learning but one added a button that will execute all macros and the other is a onOpen timebased trigger that will run all macros every minute until the sheet is closed.