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







share|improve this question
























  • @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















-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







share|improve this question
























  • @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













-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







share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 15 '18 at 18:19







Michael Walker

















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

















  • @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












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





share|improve this answer






















    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


















    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





    share|improve this answer



























      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





      share|improve this answer

























        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





        share|improve this answer













        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






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 15 '18 at 20:05









        TheMasterTheMaster

        10.4k3836




        10.4k3836





























            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














            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





















































            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







            -

            Popular posts from this blog

            Top Tejano songwriter Luis Silva dead of heart attack at 64

            Image
            Ramiro Burr's New Blog - to go back: www.ramiroburr.com From Latin rock to reggaeton, boleros to blues,Tex-Mex to Tejano, conjunto to corridos and beyond, Ramiro Burr has it covered. If you have a new CD release, a trivia question or are looking for tour info, post a message here or e-mail Ramiro directly at: musicreporter@gmail.com Top Tejano songwriter Luis Silva dead of heart attack at 64 By Ramiro Burr on October 23, 2008 8:40 AM | Permalink | Comments (12) | TrackBacks (0) UPDATE: Luis Silva Funeral Service details released Visitation 4-9 p.m. Saturday, Rosary service 6 p.m. Saturday at Porter Loring, 1101 McCullough Ave Funeral Service 10:30 a.m. Monday St. Anthony De Padua Catholic Church, Burial Service at Chapel Hills, 7735 Gibbs Sprawl Road. Porter Loring (210) 227-8221 Related New Flash: Irma Laura Lopez: long time record promoter killed in accident NewsFlash: 9:02 a.m. (New comments below) Luis Silva , one of the most well-known and prolif
            Read more

            ReactJS Fetched API data displays live - need Data displayed static

            Image
            .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box; 0 I'm fetching data from API with axios which is live (counters, date and time etc.). When I display it, browser gets not responding because it gets a lot of requests. What I need just get a first snapshot of live data. Same goes for: var date = new Date; return (<p>date.toLacaleString()</p>) It gets live time (ticking every second) :D I just need date and time at that moment when it gets requested (snapshot). reactjs date time static axios share | improve this question edited Nov 16 '18 at 16:18 Sung M. Kim 17.9k 33 112 165 asked Nov 16 '18 at 13:39 Laurynas Žilinskas Laurynas Žilinskas 8 4 add a comment  |  0 I'm fetching data from API with axios which is live (counters, date and time etc.). When I display it, brow
            Read more

            政党

            Image
            政治主題的一部分 政党政治 政治光譜 左派 極左 中間偏左 中间派 中間偏左 激進中間 （ 英语 ： Radical centrism ） 中間偏右 右派 中間偏右 極右 政綱 極端 激进 （ 英语 ： Political radicalism ） 温和 （ 英语 ： Moderate ） 改良 混合 （ 英语 ： Syncretic politics ） 第三位置 保守 原教旨 反动 政党制度 无党制 （ 英语 ： Non-partisan democracy ） 一党制 一党优势制 兩黨制 多黨制 联盟 （ 英语 ： Coalition ） 懸峙國會 信任與支持 （ 英语 ： Confidence and supply ） 少數派政府 聯合政府 大聯合政府 聯合政府 National unity government （ 英语 ： National unity government ） Majority government （ 英语 ： Majority government ） 列表 各國執政黨 政治意識形態 政治主題頁 查 论 编 系列条目 政治 基本主题 各国政治 政治经济学 政治史 （ 英语 ： Political history ） 世界政治史 （ 英语 ： Political history of the world ） 政治哲学 政治体系 无政府主义 城邦 民主制 联盟制 （ 英语 ： Federacy ） 封建制 独裁制 委员会制 精英政治 君主制 议会制 总统制 半总统制 神权政治 学术门类 政治学 （政治学家） 国际关系学 （理论） 比较政治学 公共行政 官僚制 （基层） 灵活组织机构 （ 英语 ： Adhocracy ） 政策 公共政策 （ 原则 （ 英语 ： Public policy doctrine ） ） 公共利益 外交政策 政府机构 权力分立 立法机构 行政机构 司法机构 选举机构 相关主题 主权 政治行为理论 （ 英语 ： Theories of political behavior ） 政治心理学 生理与政治导向 （ 英语 ： Biology and political orientation ） 政治组织 （ 英语 ： Political organisation ） 下属系列 选举制度 选举（投票） 联邦主义 政府 意识
            Read more