CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Graphic.Patrick
Graphic.Patrick

Reputation: 29

Can't get Google Scripts working

I'm new to Google scripts. I found something that was supposed to work but didn't. I searched, but couldn't find a solution.

The script I found:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("My Sheet name");
  var statusValues = sheet.getRange("D:D").getValues();
  var dateValues = sheet.getRange("E:E").getValues();
  for (var row in statusValues) {
    if (statusValues[row] != "Yes" && dateValues[row] == "") {
      var num = parseInt(row) + 1;
      sheet.getRange(num, 17).setValue(new Date());
    } 
  }
};

It is supposed to automatically apply the editing date in column E when a cell in D says Yes (on the same row). When I debug it, I get:

TypeError: Cannot call method "getRange" of null. (line 5, file "Code")*

My D column is a drop menu with blank/Yes/No options. Could this be why? Also, it doesn't show up in my Drive. I read somewhere that was supposed to happen.

Upvotes: 2

Views: 1296

Answers (3)

PistolPete
PistolPete

Reputation: 46

Not sure if you have solved all your issues or not, but I figured I would share my solution.

Our team has multiple spreadsheets that record the last user to edit a row and the date of the edit in the last two columns of that row.

First I have a masterOnEdit(e) function that looks like this:

function masterOnEdit(e) { 

  // Get active spreadsheet and row information
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = e.source.getActiveCell().getRow();
  var col = e.source.getActiveCell().getColumn();

  // Set last updated and user cells if not editing those cells
  if (row > 3 && col < (sheet.getLastColumn()-2)) {
    setLastUpdated(sheet);
  }
}

This function ignores the first three rows (header rows for me) and ignores edits to the last two columns (in case you want to manually add a user/date).

This function also calls a function called setLastUpdated(sheet). Note that I pass the sheet variable to this sub-function to limit Google API calls. The real editing takes place here:

function setLastUpdated(sheet){
  var row = sheet.getActiveCell().getRow();
  var email = Session.getActiveUser().getEmail();
  var user = parseUser(email);
  var userCell = sheet.getRange(row, sheet.getLastColumn()-1)
  userCell.setValue(user.name);
  userCell.setBackground(user.color);

  var date = Utilities.formatDate(new Date(), "GMT-5", "h:mm a   M/d/yy");
  sheet.getRange(row, sheet.getLastColumn()).setValue(date);
}

One more sub function in this code block parses out the username. This function takes in the e-mail returned by google and parses it to our team names using regex. This also allows me to set the color of the user cell depending on which team member made the edit. 

function parseUser(email) {
  var user = {name: email, color:'#FFFFFF'}
  if (/peter/.test(email)){
    user.name = 'Peter';
    user.color = '#4585F1';
  }
}

Hope this helps. I built this based on some of the most complete examples I found online. Really helps our team track who is working on our spreadsheet workflows.

Upvotes: 2

Graphic.Patrick
Graphic.Patrick

Reputation: 29

Finally, after a lot of buggering around and help from @Harold, I got this working. FYI I have a table where E records the date that D drop menu was set to YES and this date in E gets deleted if I change D to anything else. Same runs for F and G.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("MySheet"); // don't forget to change by your sheet name
  var statusValues = sheet.getRange("D:E").getValues(); // performed a one time import - more efficient
  var statusValues2 = sheet.getRange("F:G").getValues();


  for (var row in statusValues) {
    if (statusValues[row][0] == "Yes" && statusValues[row][1] == "") {
      var num = parseInt(row) + 1;
      sheet.getRange(num, 5).setValue(new Date()); // "5" is the column where the date is wrote
    } 
      if (statusValues[row][0] != "Yes" && statusValues[row][1] != "Date") {
      var num = parseInt(row) + 1;
      sheet.getRange(num, 5).setValue(""); // "5" is the column where the date is wrote
    } 
  }

  for (var row in statusValues2) {
    if (statusValues2[row][0] == "Yes" && statusValues2[row][1] == "") {
      var num = parseInt(row) + 1;
      sheet.getRange(num, 7).setValue(new Date()); 
    } 
      if (statusValues2[row][0] != "Yes" && statusValues2[row][1] != "Date") {
      var num = parseInt(row) + 1;
      sheet.getRange(num, 7).setValue("");
    } 
  }
};

Any suggestions to streamline this are still welcome.

Upvotes: 0

Harold
Harold

Reputation: 3337

try this code:
Don't forget to change "My Sheet name" for your sheet name

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("My Sheet name"); // don't forget to change by your sheet name
  var statusValues = sheet.getRange("D:E").getValues(); // performed a one time import - more efficient
  for (var row in statusValues) {
    if (statusValues[row][0] == "Yes" && statusValues[row][1] == "") {
      var num = parseInt(row) + 1;
      sheet.getRange(num, 5).setValue(new Date()); // "5" is the column where the date is wrote
    } 
  }
};

Upvotes: 1

Related Questions