Google Sheets: Logging a Cells new value on a log sheet
-2
I am writing a onEdit function for a ledger worksheet.
I work at a music studio, and as we grow and improve we gradually increase the cost per track to cover overhead and the new studio gear.
However, We want our previous customers per song rate to be preserved.
The best way I have found to do this is with a vlookup table searching a separate price log sheet.
But I'd like to automate the logging of new prices to avoid forgetting when the price was changed or forgetting to log it all together.
The ideal solution for me would be:
[onEdit] If the active sheet is 'setup' and active cell is 'C6']
{Copy C6 new value to first free row in column B in the page called 'log'}.
Below is my code for adding various times automatically for artists, at the very bottom is my nonfunctioning attempt to copy the new price to "log.
I also made an extremely simple sample sheet to better illustrate my goal.
function onEdit(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = SpreadsheetApp.getActiveSheet();
var sheetName = "Boring";
var sheet1 = ss.getSheetByName("Roster");
var sheet2 = ss.getSheetByName("Ledger");
var sheet3 = ss.getSheetByName("Setup");
var sheet4 = ss.getSheetByName("Invoice");
var sheet5 = ss.getSheetByName("Boring");
var Log = ss.getSheetByName("Log");
var r = s.getActiveCell();
var Change = sheet3.getRange('C6');
var row = r.getRow();
var time = new Date();
var sheet = s.getSheetName();
var letter = r.getColumn() ;
var Tracking = sheet1.getRange('E' + row.toString()).getValue();
var Editing = sheet1.getRange('F' + row.toString()).getValue();
var Mixing =sheet1.getRange('G' + row.toString()).getValue();
var Master =sheet1.getRange('H' + row.toString()).getValue();
var date = sheet2.getRange('C' + row.toString()).isBlank();
switch (sheet){
case'Roster':
switch(letter){
case 5 :
switch (Tracking){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('S' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);}
break;
case 5 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('T' + row.toString()).setValue(time);
break;
case "":
ss.getRange("S"+row).clearContent();
ss.getRange("T"+row).clearContent();
break;
}//switch tracking
break;
case 6:
switch (Editing){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('U' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);}
break;
case 4 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('V' + row.toString()).setValue(time);
break;
case "":
ss.getRange("U"+row).clearContent();
ss.getRange("V"+row).clearContent();
break;
}
break;
case 7 :
switch (Mixing) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('W' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);}
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('X' + row.toString()).setValue(time);
break;
case "":
ss.getRange("W"+row).clearContent();
ss.getRange("X"+row).clearContent();
break;
}
break;
case 8 :
switch(Master) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Y' + row.toString()).setValue(time);
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Z' + row.toString()).setValue(time);
break;
case "":
ss.getRange("Y"+row).clearContent();
ss.getRange("Z"+row).clearContent();
break;
}
break;
}
break;
case'Setup':
switch (letter){
case 'C' :
switch (row){
case 6 :
var targetSheet = ss.getSheetByName("Log"); //gets the lgo sheet
var target = Log.getRange(Log.getLastRow() + 1, 1);// "log"
var nextCell = r.offset(0, 1);
sheet3.getRange(3, 6, 1).copyTo(target, {contentsOnly:true});
break;}}
}
}Here is an extremely simplified version of my spreadsheet.
https://docs.google.com/spreadsheets/d/1i192uK-a-ALim7pvL-mkty_1nh_m9aghvmrxmiiRYKs/edit?usp=sharing
Here is my actual sheet if you need to see it
https://docs.google.com/spreadsheets/d/1PK0-WsfdRtFooLWb1GUCHjxGoQJHFLAg4CVh8pmuEO4/edit?usp=sharing
javascript excel google-apps-script google-sheets google-sheets-api
asked Nov 15 '18 at 18:14
Michael WalkerMichael Walker
34
add a comment |
-2
I am writing a onEdit function for a ledger worksheet.
I work at a music studio, and as we grow and improve we gradually increase the cost per track to cover overhead and the new studio gear.
However, We want our previous customers per song rate to be preserved.
The best way I have found to do this is with a vlookup table searching a separate price log sheet.
But I'd like to automate the logging of new prices to avoid forgetting when the price was changed or forgetting to log it all together.
The ideal solution for me would be:
[onEdit] If the active sheet is 'setup' and active cell is 'C6']
{Copy C6 new value to first free row in column B in the page called 'log'}.
Below is my code for adding various times automatically for artists, at the very bottom is my nonfunctioning attempt to copy the new price to "log.
I also made an extremely simple sample sheet to better illustrate my goal.
function onEdit(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = SpreadsheetApp.getActiveSheet();
var sheetName = "Boring";
var sheet1 = ss.getSheetByName("Roster");
var sheet2 = ss.getSheetByName("Ledger");
var sheet3 = ss.getSheetByName("Setup");
var sheet4 = ss.getSheetByName("Invoice");
var sheet5 = ss.getSheetByName("Boring");
var Log = ss.getSheetByName("Log");
var r = s.getActiveCell();
var Change = sheet3.getRange('C6');
var row = r.getRow();
var time = new Date();
var sheet = s.getSheetName();
var letter = r.getColumn() ;
var Tracking = sheet1.getRange('E' + row.toString()).getValue();
var Editing = sheet1.getRange('F' + row.toString()).getValue();
var Mixing =sheet1.getRange('G' + row.toString()).getValue();
var Master =sheet1.getRange('H' + row.toString()).getValue();
var date = sheet2.getRange('C' + row.toString()).isBlank();
switch (sheet){
case'Roster':
switch(letter){
case 5 :
switch (Tracking){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('S' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);}
break;
case 5 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('T' + row.toString()).setValue(time);
break;
case "":
ss.getRange("S"+row).clearContent();
ss.getRange("T"+row).clearContent();
break;
}//switch tracking
break;
case 6:
switch (Editing){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('U' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);}
break;
case 4 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('V' + row.toString()).setValue(time);
break;
case "":
ss.getRange("U"+row).clearContent();
ss.getRange("V"+row).clearContent();
break;
}
break;
case 7 :
switch (Mixing) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('W' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);}
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('X' + row.toString()).setValue(time);
break;
case "":
ss.getRange("W"+row).clearContent();
ss.getRange("X"+row).clearContent();
break;
}
break;
case 8 :
switch(Master) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Y' + row.toString()).setValue(time);
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Z' + row.toString()).setValue(time);
break;
case "":
ss.getRange("Y"+row).clearContent();
ss.getRange("Z"+row).clearContent();
break;
}
break;
}
break;
case'Setup':
switch (letter){
case 'C' :
switch (row){
case 6 :
var targetSheet = ss.getSheetByName("Log"); //gets the lgo sheet
var target = Log.getRange(Log.getLastRow() + 1, 1);// "log"
var nextCell = r.offset(0, 1);
sheet3.getRange(3, 6, 1).copyTo(target, {contentsOnly:true});
break;}}
}
}Here is an extremely simplified version of my spreadsheet.
https://docs.google.com/spreadsheets/d/1i192uK-a-ALim7pvL-mkty_1nh_m9aghvmrxmiiRYKs/edit?usp=sharing
Here is my actual sheet if you need to see it
https://docs.google.com/spreadsheets/d/1PK0-WsfdRtFooLWb1GUCHjxGoQJHFLAg4CVh8pmuEO4/edit?usp=sharing
javascript excel google-apps-script google-sheets google-sheets-api
asked Nov 15 '18 at 18:14
Michael WalkerMichael Walker
34
@TheMaster Sure! That would have been a good thing to do! It out puts nothing and i cant figure out why. every other line of code works exactly as i intended! however the "Price Logger" does absolutely nothing. it doesn't add anything anywhere on my sheet that i can see
– Michael Walker
Nov 15 '18 at 19:18
You did exactly what I wanted! I can edit the rest to do what i want! thank you so much!
– Michael Walker
Nov 15 '18 at 20:32
add a comment |
-2
-2
-2
1
I am writing a onEdit function for a ledger worksheet.
I work at a music studio, and as we grow and improve we gradually increase the cost per track to cover overhead and the new studio gear.
However, We want our previous customers per song rate to be preserved.
The best way I have found to do this is with a vlookup table searching a separate price log sheet.
But I'd like to automate the logging of new prices to avoid forgetting when the price was changed or forgetting to log it all together.
The ideal solution for me would be:
[onEdit] If the active sheet is 'setup' and active cell is 'C6']
{Copy C6 new value to first free row in column B in the page called 'log'}.
Below is my code for adding various times automatically for artists, at the very bottom is my nonfunctioning attempt to copy the new price to "log.
I also made an extremely simple sample sheet to better illustrate my goal.
function onEdit(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = SpreadsheetApp.getActiveSheet();
var sheetName = "Boring";
var sheet1 = ss.getSheetByName("Roster");
var sheet2 = ss.getSheetByName("Ledger");
var sheet3 = ss.getSheetByName("Setup");
var sheet4 = ss.getSheetByName("Invoice");
var sheet5 = ss.getSheetByName("Boring");
var Log = ss.getSheetByName("Log");
var r = s.getActiveCell();
var Change = sheet3.getRange('C6');
var row = r.getRow();
var time = new Date();
var sheet = s.getSheetName();
var letter = r.getColumn() ;
var Tracking = sheet1.getRange('E' + row.toString()).getValue();
var Editing = sheet1.getRange('F' + row.toString()).getValue();
var Mixing =sheet1.getRange('G' + row.toString()).getValue();
var Master =sheet1.getRange('H' + row.toString()).getValue();
var date = sheet2.getRange('C' + row.toString()).isBlank();
switch (sheet){
case'Roster':
switch(letter){
case 5 :
switch (Tracking){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('S' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);}
break;
case 5 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('T' + row.toString()).setValue(time);
break;
case "":
ss.getRange("S"+row).clearContent();
ss.getRange("T"+row).clearContent();
break;
}//switch tracking
break;
case 6:
switch (Editing){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('U' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);}
break;
case 4 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('V' + row.toString()).setValue(time);
break;
case "":
ss.getRange("U"+row).clearContent();
ss.getRange("V"+row).clearContent();
break;
}
break;
case 7 :
switch (Mixing) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('W' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);}
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('X' + row.toString()).setValue(time);
break;
case "":
ss.getRange("W"+row).clearContent();
ss.getRange("X"+row).clearContent();
break;
}
break;
case 8 :
switch(Master) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Y' + row.toString()).setValue(time);
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Z' + row.toString()).setValue(time);
break;
case "":
ss.getRange("Y"+row).clearContent();
ss.getRange("Z"+row).clearContent();
break;
}
break;
}
break;
case'Setup':
switch (letter){
case 'C' :
switch (row){
case 6 :
var targetSheet = ss.getSheetByName("Log"); //gets the lgo sheet
var target = Log.getRange(Log.getLastRow() + 1, 1);// "log"
var nextCell = r.offset(0, 1);
sheet3.getRange(3, 6, 1).copyTo(target, {contentsOnly:true});
break;}}
}
}Here is an extremely simplified version of my spreadsheet.
https://docs.google.com/spreadsheets/d/1i192uK-a-ALim7pvL-mkty_1nh_m9aghvmrxmiiRYKs/edit?usp=sharing
Here is my actual sheet if you need to see it
https://docs.google.com/spreadsheets/d/1PK0-WsfdRtFooLWb1GUCHjxGoQJHFLAg4CVh8pmuEO4/edit?usp=sharing
javascript excel google-apps-script google-sheets google-sheets-api
asked Nov 15 '18 at 18:14
Michael WalkerMichael Walker
34
I am writing a onEdit function for a ledger worksheet.
I work at a music studio, and as we grow and improve we gradually increase the cost per track to cover overhead and the new studio gear.
However, We want our previous customers per song rate to be preserved.
The best way I have found to do this is with a vlookup table searching a separate price log sheet.
But I'd like to automate the logging of new prices to avoid forgetting when the price was changed or forgetting to log it all together.
The ideal solution for me would be:
[onEdit] If the active sheet is 'setup' and active cell is 'C6']
{Copy C6 new value to first free row in column B in the page called 'log'}.
Below is my code for adding various times automatically for artists, at the very bottom is my nonfunctioning attempt to copy the new price to "log.
I also made an extremely simple sample sheet to better illustrate my goal.
function onEdit(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = SpreadsheetApp.getActiveSheet();
var sheetName = "Boring";
var sheet1 = ss.getSheetByName("Roster");
var sheet2 = ss.getSheetByName("Ledger");
var sheet3 = ss.getSheetByName("Setup");
var sheet4 = ss.getSheetByName("Invoice");
var sheet5 = ss.getSheetByName("Boring");
var Log = ss.getSheetByName("Log");
var r = s.getActiveCell();
var Change = sheet3.getRange('C6');
var row = r.getRow();
var time = new Date();
var sheet = s.getSheetName();
var letter = r.getColumn() ;
var Tracking = sheet1.getRange('E' + row.toString()).getValue();
var Editing = sheet1.getRange('F' + row.toString()).getValue();
var Mixing =sheet1.getRange('G' + row.toString()).getValue();
var Master =sheet1.getRange('H' + row.toString()).getValue();
var date = sheet2.getRange('C' + row.toString()).isBlank();
switch (sheet){
case'Roster':
switch(letter){
case 5 :
switch (Tracking){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('S' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);}
break;
case 5 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('T' + row.toString()).setValue(time);
break;
case "":
ss.getRange("S"+row).clearContent();
ss.getRange("T"+row).clearContent();
break;
}//switch tracking
break;
case 6:
switch (Editing){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('U' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);}
break;
case 4 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('V' + row.toString()).setValue(time);
break;
case "":
ss.getRange("U"+row).clearContent();
ss.getRange("V"+row).clearContent();
break;
}
break;
case 7 :
switch (Mixing) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('W' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);}
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('X' + row.toString()).setValue(time);
break;
case "":
ss.getRange("W"+row).clearContent();
ss.getRange("X"+row).clearContent();
break;
}
break;
case 8 :
switch(Master) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Y' + row.toString()).setValue(time);
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Z' + row.toString()).setValue(time);
break;
case "":
ss.getRange("Y"+row).clearContent();
ss.getRange("Z"+row).clearContent();
break;
}
break;
}
break;
case'Setup':
switch (letter){
case 'C' :
switch (row){
case 6 :
var targetSheet = ss.getSheetByName("Log"); //gets the lgo sheet
var target = Log.getRange(Log.getLastRow() + 1, 1);// "log"
var nextCell = r.offset(0, 1);
sheet3.getRange(3, 6, 1).copyTo(target, {contentsOnly:true});
break;}}
}
}Here is an extremely simplified version of my spreadsheet.
https://docs.google.com/spreadsheets/d/1i192uK-a-ALim7pvL-mkty_1nh_m9aghvmrxmiiRYKs/edit?usp=sharing
Here is my actual sheet if you need to see it
https://docs.google.com/spreadsheets/d/1PK0-WsfdRtFooLWb1GUCHjxGoQJHFLAg4CVh8pmuEO4/edit?usp=sharing
function onEdit(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = SpreadsheetApp.getActiveSheet();
var sheetName = "Boring";
var sheet1 = ss.getSheetByName("Roster");
var sheet2 = ss.getSheetByName("Ledger");
var sheet3 = ss.getSheetByName("Setup");
var sheet4 = ss.getSheetByName("Invoice");
var sheet5 = ss.getSheetByName("Boring");
var Log = ss.getSheetByName("Log");
var r = s.getActiveCell();
var Change = sheet3.getRange('C6');
var row = r.getRow();
var time = new Date();
var sheet = s.getSheetName();
var letter = r.getColumn() ;
var Tracking = sheet1.getRange('E' + row.toString()).getValue();
var Editing = sheet1.getRange('F' + row.toString()).getValue();
var Mixing =sheet1.getRange('G' + row.toString()).getValue();
var Master =sheet1.getRange('H' + row.toString()).getValue();
var date = sheet2.getRange('C' + row.toString()).isBlank();
switch (sheet){
case'Roster':
switch(letter){
case 5 :
switch (Tracking){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('S' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);}
break;
case 5 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('T' + row.toString()).setValue(time);
break;
case "":
ss.getRange("S"+row).clearContent();
ss.getRange("T"+row).clearContent();
break;
}//switch tracking
break;
case 6:
switch (Editing){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('U' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);}
break;
case 4 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('V' + row.toString()).setValue(time);
break;
case "":
ss.getRange("U"+row).clearContent();
ss.getRange("V"+row).clearContent();
break;
}
break;
case 7 :
switch (Mixing) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('W' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);}
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('X' + row.toString()).setValue(time);
break;
case "":
ss.getRange("W"+row).clearContent();
ss.getRange("X"+row).clearContent();
break;
}
break;
case 8 :
switch(Master) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Y' + row.toString()).setValue(time);
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Z' + row.toString()).setValue(time);
break;
case "":
ss.getRange("Y"+row).clearContent();
ss.getRange("Z"+row).clearContent();
break;
}
break;
}
break;
case'Setup':
switch (letter){
case 'C' :
switch (row){
case 6 :
var targetSheet = ss.getSheetByName("Log"); //gets the lgo sheet
var target = Log.getRange(Log.getLastRow() + 1, 1);// "log"
var nextCell = r.offset(0, 1);
sheet3.getRange(3, 6, 1).copyTo(target, {contentsOnly:true});
break;}}
}
}function onEdit(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = SpreadsheetApp.getActiveSheet();
var sheetName = "Boring";
var sheet1 = ss.getSheetByName("Roster");
var sheet2 = ss.getSheetByName("Ledger");
var sheet3 = ss.getSheetByName("Setup");
var sheet4 = ss.getSheetByName("Invoice");
var sheet5 = ss.getSheetByName("Boring");
var Log = ss.getSheetByName("Log");
var r = s.getActiveCell();
var Change = sheet3.getRange('C6');
var row = r.getRow();
var time = new Date();
var sheet = s.getSheetName();
var letter = r.getColumn() ;
var Tracking = sheet1.getRange('E' + row.toString()).getValue();
var Editing = sheet1.getRange('F' + row.toString()).getValue();
var Mixing =sheet1.getRange('G' + row.toString()).getValue();
var Master =sheet1.getRange('H' + row.toString()).getValue();
var date = sheet2.getRange('C' + row.toString()).isBlank();
switch (sheet){
case'Roster':
switch(letter){
case 5 :
switch (Tracking){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('S' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('S' + row.toString()).setValue(time);}
break;
case 5 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('T' + row.toString()).setValue(time);
break;
case "":
ss.getRange("S"+row).clearContent();
ss.getRange("T"+row).clearContent();
break;
}//switch tracking
break;
case 6:
switch (Editing){
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('U' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('U' + row.toString()).setValue(time);}
break;
case 4 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('V' + row.toString()).setValue(time);
break;
case "":
ss.getRange("U"+row).clearContent();
ss.getRange("V"+row).clearContent();
break;
}
break;
case 7 :
switch (Mixing) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);
break;
default :
if(sheet1.getRange('W' + row.toString()).getValue() > 0 ){
break;
} else{
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('W' + row.toString()).setValue(time);}
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('X' + row.toString()).setValue(time);
break;
case "":
ss.getRange("W"+row).clearContent();
ss.getRange("X"+row).clearContent();
break;
}
break;
case 8 :
switch(Master) {
case 1 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Y' + row.toString()).setValue(time);
break;
case 2 :
time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
sheet1.getRange('Z' + row.toString()).setValue(time);
break;
case "":
ss.getRange("Y"+row).clearContent();
ss.getRange("Z"+row).clearContent();
break;
}
break;
}
break;
case'Setup':
switch (letter){
case 'C' :
switch (row){
case 6 :
var targetSheet = ss.getSheetByName("Log"); //gets the lgo sheet
var target = Log.getRange(Log.getLastRow() + 1, 1);// "log"
var nextCell = r.offset(0, 1);
sheet3.getRange(3, 6, 1).copyTo(target, {contentsOnly:true});
break;}}
}
}javascript excel google-apps-script google-sheets google-sheets-api
javascript excel google-apps-script google-sheets google-sheets-api
asked Nov 15 '18 at 18:14
Michael WalkerMichael Walker
34
asked Nov 15 '18 at 18:14
Michael WalkerMichael Walker
34
edited Nov 15 '18 at 18:19
Michael Walker
asked Nov 15 '18 at 18:14
Michael WalkerMichael Walker
34
asked Nov 15 '18 at 18:14
Michael WalkerMichael Walker
34
asked Nov 15 '18 at 18:14
Michael WalkerMichael Walker
34
34
@TheMaster Sure! That would have been a good thing to do! It out puts nothing and i cant figure out why. every other line of code works exactly as i intended! however the "Price Logger" does absolutely nothing. it doesn't add anything anywhere on my sheet that i can see
– Michael Walker
Nov 15 '18 at 19:18
You did exactly what I wanted! I can edit the rest to do what i want! thank you so much!
– Michael Walker
Nov 15 '18 at 20:32
add a comment |
@TheMaster Sure! That would have been a good thing to do! It out puts nothing and i cant figure out why. every other line of code works exactly as i intended! however the "Price Logger" does absolutely nothing. it doesn't add anything anywhere on my sheet that i can see
– Michael Walker
Nov 15 '18 at 19:18
You did exactly what I wanted! I can edit the rest to do what i want! thank you so much!
– Michael Walker
Nov 15 '18 at 20:32
@TheMaster Sure! That would have been a good thing to do! It out puts nothing and i cant figure out why. every other line of code works exactly as i intended! however the "Price Logger" does absolutely nothing. it doesn't add anything anywhere on my sheet that i can see
– Michael Walker
Nov 15 '18 at 19:18
@TheMaster Sure! That would have been a good thing to do! It out puts nothing and i cant figure out why. every other line of code works exactly as i intended! however the "Price Logger" does absolutely nothing. it doesn't add anything anywhere on my sheet that i can see
– Michael Walker
Nov 15 '18 at 19:18
You did exactly what I wanted! I can edit the rest to do what i want! thank you so much!
– Michael Walker
Nov 15 '18 at 20:32
You did exactly what I wanted! I can edit the rest to do what i want! thank you so much!
– Michael Walker
Nov 15 '18 at 20:32
add a comment |
1 Answer
1
active
oldest
votes
0
For the sample sheet provided, Try this script:
Sample Script:
/*@description: [onEdit] If the active sheet is 'Setup' and active cell is 'A4'] {Copy A4 new value to first free row in column B in the page called 'log'}.*/
function onEdit(e){
var rng = e.range;
var ss = e.source;
if(rng.getA1Notation()==='A4' && rng.getSheet().getName()=='Setup'){
ss.getSheetByName('log').appendRow([new Date(),e.value])
}
}
References:
Event Objects- Range
answered Nov 15 '18 at 20:05
TheMasterTheMaster
10.5k3936
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53325570%2fgoogle-sheets-logging-a-cells-new-value-on-a-log-sheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
For the sample sheet provided, Try this script:
Sample Script:
/*@description: [onEdit] If the active sheet is 'Setup' and active cell is 'A4'] {Copy A4 new value to first free row in column B in the page called 'log'}.*/
function onEdit(e){
var rng = e.range;
var ss = e.source;
if(rng.getA1Notation()==='A4' && rng.getSheet().getName()=='Setup'){
ss.getSheetByName('log').appendRow([new Date(),e.value])
}
}
References:
Event Objects- Range
answered Nov 15 '18 at 20:05
TheMasterTheMaster
10.5k3936
add a comment |
0
For the sample sheet provided, Try this script:
Sample Script:
/*@description: [onEdit] If the active sheet is 'Setup' and active cell is 'A4'] {Copy A4 new value to first free row in column B in the page called 'log'}.*/
function onEdit(e){
var rng = e.range;
var ss = e.source;
if(rng.getA1Notation()==='A4' && rng.getSheet().getName()=='Setup'){
ss.getSheetByName('log').appendRow([new Date(),e.value])
}
}
References:
Event Objects- Range
answered Nov 15 '18 at 20:05
TheMasterTheMaster
10.5k3936
add a comment |
0
0
0
For the sample sheet provided, Try this script:
Sample Script:
/*@description: [onEdit] If the active sheet is 'Setup' and active cell is 'A4'] {Copy A4 new value to first free row in column B in the page called 'log'}.*/
function onEdit(e){
var rng = e.range;
var ss = e.source;
if(rng.getA1Notation()==='A4' && rng.getSheet().getName()=='Setup'){
ss.getSheetByName('log').appendRow([new Date(),e.value])
}
}
References:
Event Objects- Range
answered Nov 15 '18 at 20:05
TheMasterTheMaster
10.5k3936
For the sample sheet provided, Try this script:
Sample Script:
/*@description: [onEdit] If the active sheet is 'Setup' and active cell is 'A4'] {Copy A4 new value to first free row in column B in the page called 'log'}.*/
function onEdit(e){
var rng = e.range;
var ss = e.source;
if(rng.getA1Notation()==='A4' && rng.getSheet().getName()=='Setup'){
ss.getSheetByName('log').appendRow([new Date(),e.value])
}
}
References:
Event Objects- Range
answered Nov 15 '18 at 20:05
TheMasterTheMaster
10.5k3936
answered Nov 15 '18 at 20:05
TheMasterTheMaster
10.5k3936
answered Nov 15 '18 at 20:05
TheMasterTheMaster
10.5k3936
answered Nov 15 '18 at 20:05
TheMasterTheMaster
10.5k3936
10.5k3936
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53325570%2fgoogle-sheets-logging-a-cells-new-value-on-a-log-sheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
@TheMaster Sure! That would have been a good thing to do! It out puts nothing and i cant figure out why. every other line of code works exactly as i intended! however the "Price Logger" does absolutely nothing. it doesn't add anything anywhere on my sheet that i can see
– Michael Walker
Nov 15 '18 at 19:18
You did exactly what I wanted! I can edit the rest to do what i want! thank you so much!
– Michael Walker
Nov 15 '18 at 20:32