36
Ok, here is a modified version of the code in my previous answer:
function onEdit(e) {
// Your sheet params
var sheetName = "MySheet";
var dateModifiedColumnIndex = 2;
var dateModifiedColumnLetter = 'B';
var range = e.range; // range just edited
var sheet = range.getSheet();
if (sheet.getName() !== sheetName) {
return;
}
// If the ...
8
FYI, the accepted answer uses DD as the day format, which will get you something like "312", i.e. the 312th day of the year.
I used this:
"yyyy-MM-dd, hh:mm:ss"
to get this:
2013-11-12, 03:43:20
5
try this code:
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getRange("2:2");
var values = range.getValues();
values = values[0];
var day = 24*3600*1000;
var today = parseInt((new Date().setHours(0,0,0,0))/day);
Logger.log(today);
var ssdate;
for (var i=0; i<values....
4
I hope this is OK to put here. Below is a modification of the function above but to do it for a row (row 9) rather than columns. Figuring out how to use the column assignment was very frustrating but ended up being extremely simple. Thanks to OnenOnlyWalter for the original code:
function onEdit() {
var s = SpreadsheetApp.getActiveSheet(); // Get ...
3
Add getValue() to var r = sheet.getRange("D11");. The resulting code line is:
var r = sheet.getRange("D11").getValue();
The above because getRange returns Range object which not return directly it's value/values.
Also replace r.hideRows(11); by sheet.hideRows(11);
For further details checkout https://developers.google.com/apps-script/guides/sheets
3
If your dates are in column A, and you want your file to be opened on today's row:
In your google sheet: add this in the cell A1: =match(TODAY();A2:A;0)+1 It will return the row number which contains today's date (ex: row 154).
In script editor write this code that will automatically place your cursor on the cell A154:
function onOpen() {
var ss ...
3
It's much simpler now in 2017 :)
Simply add "&range=A6" after the URL of the Google Sheet.
The "&range" is the range that you want the cursor to be at, and the "A" is for the column, and the "6" for the row.
So the URL would look something like this:
https://docs.google.com/spreadsheets/d/JrKpzRQVXptmRfuE/edit#gid=0&range=A6
Alternative ...
2
Having two onEdit scripts isn't gonna work. So try to merge the two scripts into one...
function onEdit(e) {
var s = e.source.getActiveSheet(),
sheets = ['In', 'Out'],
ind = sheets.indexOf(s.getName());
if (ind === -1 || e.range.columnStart < 2 || e.range.columnStart > 8) return;
e.range.offset(0, 9 - e.range.columnStart)
.setValue(new ...
2
I searched for this earlier and figured out that it depends if you have private accounts or part of the same company. For private accounts it's not possible, at least it was not when I researched. My solution was to have each person fill out his/her name.
I copy my code here, you have to change the name of the sheet, row number etc
function onEdit(e) {
...
2
Say I'm forgetful, but I don't want to pester my coworker by calling her more than every two hours. I might remind myself just by jotting down when I call, so I can glance at that note next time I'm once again reaching for the phone.
A script could do the same, scribbling down when it needs to run again, and checking it later.
function onOpen() {
var ...
2
You're missing the text "function" in front of oneditfx1 and oneditfx1. Also, your huge if/else if statement could be simplified by using a switch statement and nesting furthers ifs within that.
1
Use on edit / on change triggers and the corresponding event object.
Example
function onEdit(e){
var a1Notation = e.range.getA1Notation();
e.source.toast(a1Notation + ' was clicked');
}
Related
How to trigger a script in Google sheets when a value changes
References
https://developers.google.com/apps-script/guides/sheets
https://developers.google....
1
Turns out I can check "Anyone with the link can edit" in the Sharing menu on Google Sheets and anonymous edits do in fact trigger the scripts successfully. The only thing that doesn't work in my case are toast messages (they don't show up for anonymous users) and the executions don't get tracked in the dashboard, which makes it impossible to accurately track ...
1
Only users with edit access to the spreadsheet are able to fire through their actions on edit / on change triggers.
The alternative is to create a web application setting it to run as the script owner by anyone. The overview about how to create such web application are on https://developers.google.com/apps-script/guides/web.
1
Figured it out:
function onEdit() {
var s = SpreadsheetApp.getActiveSheet(); // Get spreadsheet name
var r = s.getActiveCell(); // store active cell name in current spreadsheet
var cell1 = 2 // This is the row I want to put values
var headerIndex = 1;
var isHeader = r.getRow() === headerIndex;
if(r.getColumn() != cell1 &&...
1
I tried this and its actually worked .
function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var r = sheet.getRange("L2").getValue();
if (r == 'ADD'){
// Add your code here
}}
1
onChange isn't a reserved word for a simple trigger on Google Apps Script but there is an "on change" installable trigger that could execute a specified function regardless of it's name. Unfortunately for this case, this installable trigger, the same way that onEdit works, only is triggered by changes made by the user.
If the update is made by a script, ...
1
I believe, You're looking for something like time-driven trigger
So, there are at least two options for Your case:
Manual setup, which does not seam very versatile, but You can setup a call of particular functions at hour length window every day - Edit > Current project's triggers > Create new trigger
Programmatically, using something like following:
...
1
It was the line
var historySheet = SpreadsheetApp.openById('spreadsheetID');
This requires authentication and therefore cannot be run by simple triggers such as onOpen() or onEdit(). moving the function into newDay() and setting up manual triggers through the edit menu resolved my issue.
1
I had the same issue on a code working for 1 year.
Seems Google added recently new restrictions to simple triggers and it isn't possible to open other files on the server side.
So, to sum up, onEdit is NOT compatible with .openById(id) nor .openByUrl(url) anymore.
In order to solve it you should use SpreadsheetApp.getActive().
In your case, I'd suggest ...
1
Solution was to go to Resources -> Current Triggers and add a new trigger in the Script project.
1
If you are open to use filters and to hide the other rows, use a filter / filter view with Filter by condition set to Date is > Today
Comparison to other alternatives
This will work for editors and viewers while the script based solutions only work for editors
A URL based solution, like including &range=someting or using filter view's url only work ...
1
As Eddy told you, it can be done by using the onEdit trigger.
Code
//global
var ss = SpreadsheetApp.getActiveSpreadsheet();
var name = "Preferences";
function onEdit(e) {
// clear cache if on preferences sheet
if(ss.getActiveSheet().getName() == name) {
CacheService.getPrivateCache().remove(name)
} else {
// get all the data
var prefs = ...
answered Dec 27 '13 at 22:50
Jacob Jan Tuinstra
19.1k12 gold badges73 silver badges163 bronze badges
1
This piece of code works:
function onEdit(){
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var editedCell = sh.getActiveRange().getColumnIndex();
if(editedCell == 2) {
var range = sh.getRange("A2:B10");
range.sort({column: 2});
}
}
One key difference is the .getColumnIndex. Without it you don't receive an integer !!
...
answered Jan 16 '13 at 15:55
Jacob Jan Tuinstra
19.1k12 gold badges73 silver badges163 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible
