41
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 ...
12
based on a quick search these are the differences between onEdit(e) and onChange(e):
onEdit(e):
brief description:
The onEdit(e) trigger runs automatically when a user changes the value of any cell in a spreadsheet. Most onEdit(e) triggers use the information in the event object to respond appropriately. For example, the onEdit(e) function below sets a ...
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
7
Building on both previous answers (from Barry and Danny):
Assuming the ID column is column A. Pick a "Next ID" cell and set it to the following formula (assuming it is in "P1"):
=MAX(A:A)+1
Create script using the script editor under the "Tools" menu, and paste in the following:
function onFormSubmit(e) {
// Get the active sheet
var sheet = ...
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 ...
4
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 ...
4
Further to the above answers - This solution doesn't require an extra spreadsheet cell.
You can use the inbuilt event handlers for submission of the form to get a unique id. Because the spreadsheet is just the destination for the form, deleting a row doesn't actually delete the response. With that in mind...
EDIT: removed need for ID and dealt with date ...
3
Short answer
It's not possible to populate a Google Form ListItem based on the user using the form as respondent.
Explanation
Google Forms has two main views:
Editor view: Available to the form owner and editors.
Respondent view: Available to "anyone" with the respondent view link
The available installable triggers are Form Open and Form Submit. From ...
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
Solution was to go to Resources -> Current Triggers and add a new trigger in the Script project.
3
onEdit can be written to take an event parameter that among other things contains the range that was edited. Changing the function declaration to take a parameter 'event' and changing r to be event.range instead of the activecell gets the exact behavior I believe you want.
function onEdit(event) {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName()...
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
How to Fix your problem
Manually install the onFormSubmit trigger.
When installing the Trigger, under "Choose which function to run", choose the function onFormSubmit (the trigger event argument () isn't shown when creating the trigger.
There should only be one onformSubmit trigger, and only function executed by the trigger. If you find that the function ...
2
This is a derivative of the other answers but it might be useful for future users.
function onEdit(e)
{
var sheet = SpreadsheetApp.getActiveSheet();
var row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
var bugCount = sheet.getRange("M2").getValue();
bugCount++;
if (sheet.getRange(row, 1).getValue() == "") {
sheet....
2
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 ...
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.
2
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....
2
Whenever any text is entered in a cell, you want to convert it to uppercase. But your script applies to all kinds of types (includes dates and numbers) and this has undesirable results.
Javascript recognises a variety of types. The reason that your script isn't working as expected is that it applies to all types, rather than only to the "string" type.
...
1
That occurs because only could be one function called onEdit() by Google Apps Script Project.
Change their names to myFunction1, myFunction2, myFuntion3 or anything else but using an unique name for each function, then make an onEdit() function that calls them, something like the following:
function onEdit(){
myFunction1();
myFunction2();
myFunction3(...
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
