How do I pull project properties in Google Scripts?

Question

I'm currently working on a google script that emails designated users when a project is marked as complete in the associated spreadsheet. The first function triggers when the spreadsheet is edited, and checks column 5 and moves the row if it matches accordingly. The first If statement checks column 5 to see if it matches "Completed" and moves it to the "COMPLETED" sheet if it does. I'm able to have it email a desired user successfully, but I'm also trying to include the first column of the selected row in the email.

This lead me to trying to use project properties as global variables aren't possible in Google Scripts. However, when trying to pull a test property, it breaks the script.

Does anybody know how I might correct my code to be able to push column A to a project property in one function and pull it in a separate function?

The original idea for using project properties was taken from the following thread: How to define global variable in Google Apps Script

Thank you!

function onEditTrigger(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange()

  // Below sends a completed project from the IN PROGRESS sheet to the COMPLETED sheet
  if(s.getName() == "IN PROGRESS" && r.getColumn() == 5 && r.getValue() == "Completed") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("COMPLETED");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
    // Send an email when a project is marked complete
    sendEmail();  
  }
    // Below sends a completed project from the COMPLETED sheet to the IN PROGRESS sheet
   if(s.getName() == "COMPLETED" && r.getColumn() == 5 && (r.getValue() == "In Development" || r.getValue() == "On Hold" || r.getValue() == "Delayed")) {
    var row2 = r.getRow();
    var numColumns2 = s.getLastColumn();
    var targetSheet2 = ss.getSheetByName("IN PROGRESS");
    var target2 = targetSheet2.getRange(targetSheet2.getLastRow() + 1, 1);
    s.getRange(row2, 1, 1, numColumns2).moveTo(target2);
    s.deleteRow(row2); 
  }
}
// Code that establishes desired emails, subject
function sendEmail() {
  var project = PropertiesService.getScriptProperties().getProperty('project');
  var subject = "Learning Solutions project has been completed: " + project;
  var emailAddress = "test@email.com";
  MailApp.sendEmail(emailAddress,subject,"This is a test"
    );
}

Screenshot of the current setup for Project Properties

score 0 · Answer 1 · answered Jul 05 '17 at 21:18

Global variables are supported by Google Apps Script, see this simple example of Code.gs:

var myGlobalVar = 'This is global var value';

function myFunction()
{
  Logger.log(myGlobalVar);
}

Run function myFunction then select View - Logs from script editor menu and you will see

[17-07-06 00:12:58:691 EEST] This is global var value

score 0 · Answer 2 · answered Jul 05 '17 at 22:05

I've attached the code after my adjustments that allows me to dynamically fill the subject of the email I'm sending out based on the first item of the row being moved to the COMPLETED sheet.

            function onEditTrigger(event) {
              // assumes source data in sheet named Needed
              // target sheet of move to named Acquired
              // test column with yes/no is col 4 or D
              var ss = SpreadsheetApp.getActiveSpreadsheet();
              var s = event.source.getActiveSheet();
              var r = event.source.getActiveRange()

              // Below sends a completed project from the IN PROGRESS sheet to the COMPLETED sheet
              if(s.getName() == "IN PROGRESS" && r.getColumn() == 5 && r.getValue() == "Completed") {
                var row = r.getRow();
                var numColumns = s.getLastColumn();
                var targetSheet = ss.getSheetByName("COMPLETED");
                var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
                //Added below code to pull the first value of the current row and map it to an object in the project properties
                var project = s.getRange(row, 1,row,1).getDisplayValue();
                PropertiesService.getScriptProperties().setProperty('project', project);
                s.getRange(row, 1, 1, numColumns).moveTo(target);
                s.deleteRow(row);
                // Send an email when a project is marked complete
                sendEmail();  
              }
                // Below sends a completed project from the COMPLETED sheet to the IN PROGRESS sheet
               if(s.getName() == "COMPLETED" && r.getColumn() == 5 && (r.getValue() == "In Development" || r.getValue() == "On Hold" || r.getValue() == "Delayed")) {
                var row2 = r.getRow();
                var numColumns2 = s.getLastColumn();
                var targetSheet2 = ss.getSheetByName("IN PROGRESS");
                var target2 = targetSheet2.getRange(targetSheet2.getLastRow() + 1, 1);
                s.getRange(row2, 1, 1, numColumns2).moveTo(target2);
                s.deleteRow(row2); 
              }
            }
            // Code that establishes desired emails, subject
            function sendEmail() {
           //pulls down the 'project' property established earlier in the previous function and sets it as a variable in the new function              
              var project = PropertiesService.getScriptProperties().getProperty('project');
              var subject = "Learning Solutions project has been completed: " + project;
              var emailAddress = "test@email.com";
              MailApp.sendEmail(emailAddress,subject,"This is a test"
                );
            }

How do I pull project properties in Google Scripts?

2 Answers2