69351

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.

Answer1:

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.

Answer2:

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

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 } } };

Answer3:

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.

Recommend

  • Google Charts + Google Sheets: Specified range does not identify data correctly
  • Why is my Jquery ajax success handler being called with an array (and not the response object)
  • Error: “You have been creating or deleting too many calendars or calendar events in a short time.”
  • Write from Google Firebase to Google Sheets using Google Apps script
  • Find symbol dependencies in source code [closed]
  • Installing SSL on AWS EC2 Bitnami Mean Stack
  • Many to many relationship core data query for specific collection
  • Typecasting `this` of a base class template to its derived class
  • how do you obtain the address of an instance after overriding the __str__ method in python
  • hibernate sets dirty flag (and issues update) even though client did not change value
  • Error in installing package: fatal error: stdlib.h: no such file or directory
  • How to make R's read_csv2() recognise the text characters properly
  • Who propagate bugfixes across branches (corporate development)?
  • Is there a way to save the selected text and highlight it again once the page is refreshed?
  • SonarQube: Cannot deactivate rule with missing quality profile
  • How to disable all widgets inside Panel or inside Composite?
  • How to generate and display a QR Code in ionic 2
  • Jenkins: FATAL: Could not initialize class hudson.util.ProcessTree$UnixReflection
  • SharedPreferences or SQLite Database?
  • Inline R code in YAML for rmarkdown doesn't run
  • How can the INSERT … ON CONFLICT (id) DO UPDATE… syntax be used with a sequence ID?
  • Marklogic : Query response time is very high
  • Why querying a date BC is changed to AD in Java?
  • Record samples being played with OpenAL
  • Atlas images wrong size on iPad iOS 9
  • Repeat a vertical line on every page in Report Builder / SSRS
  • CSS Linear-gradient formatting issue accross different browsers
  • Android screen density dpi vs ppi
  • swift auto completion not working in Xcode6-Beta
  • Bug in WPF DataGrid
  • How would I use PHP exceptions to define a redirect?
  • jQuery tmpl and DataLink beta
  • Jquery - Jquery Wysiwyg return html as a string
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • -fvisibility=hidden not passed by compiler for Debug builds
  • Authorize attributes not working in MVC 4
  • Busy indicator not showing up in wpf window [duplicate]
  • Python/Django TangoWithDjango Models and Databases
  • java string with new operator and a literal
  • Net Present Value in Excel for Grouped Recurring CF