COPY TO WHEN WHEN VALUE OF A8 CHANGES

[Picloan Limited]

I have been trying to copy from on cell to another when another cell changes. when I run the below script, it gives TypeError: Cannot read property 'range' of undefined. Please help me.

function onChange(e){

      var s = SpreadsheetApp.getActive().getSheetByName('DATA')

  var sheet = s.range.getSheet();

  if(sheet.getName() === 'Sheet8' & e.range.getA1Notation() === "A8")  {

  

  s.getRange('E8').copyTo(s.getRange('B18'))//your script

  }

[CBMServices Web]

The getRange  needs to be on sheet and not on s, which is the spreadsheet.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b0e5b242-4e82-464f-8eb0-471b2a9a04a4n%40googlegroups.com.

[CBMServices Web]

Sent this too fast before adding the second statement.

S should be the spreadsheet.

So when you declare it at top, use get Active and not getActiveSheet.

[Picloan Limited]

Thanks,but this line of code var sheet = event.range.getSheet(); still giving me this TypeError: Cannot read property 'range' of undefined

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3WMvP2C8%3D7AmmKB6gL7%3Dg_vmWXFxC-OPz%2BgY_gaxUt2GA%40mail.gmail.com.

[CBMServices Web]

Yes, you need to use getSheetName instead and then compare it against the sheet name you want to trigger from.

If it is the one you want, then getSheet can be called on s which needs to be the spreadsheet.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAP5ssfwk2JzSAhZcnW2FT5QMhiAdD%2B5o%3DT5B9zckQ4eWHfPhsw%40mail.gmail.com.

[Picloan Limited]

this is what I came up with, but

 function checkValue(e) {

  var sp = PropertiesService.getScriptProperties();

  var ss = SpreadsheetApp.getActive();

  var sheet = ss.getSheetByName("sheet8");

  var valueToCheck = sheet.getRange("G12").getValue();

  var oldValue = sp.getProperty("G12") ||1,2,4,5 0;

  if (valueToCheck > 5 && valueToCheck != oldValue) {

   sheet.getRange('E8').copyTo(sheet.getRange('B8'))//your script

  }

}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3UvDyXcq7B0M%3Ddg2dc%3DG%3DLLK1FVaY2EaFMc3zcph0dTYw%40mail.gmail.com.

[CBMServices Web]

You are getting closer.

But what you need to do is: var sheetName = e.range.getSheetName()

Then compare it to the sheet name you want to monitor:

if (sheetName != " sheet8") {return;}

This way any changes to other sheets will be ignored.

Then check that the range that was changed is the one you care about:

if ((e.range.getRow() !=12) || (e.range.getColumn() != 7))

{Return;}

// now you know that your particular cell was changed. Proceed with actions you want.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAP5ssfznSNfM6TUxFK%2Bo7RhKRjrKXLsYBsLOyvROxc-kZDr1YQ%40mail.gmail.com.

[cbmserv...@gmail.com]

Have not heard back, so I modified the function for you. I removed use of script properties because you don’t need it. Also if you want this to get triggered on edits, you need to rename your function as per below. Make sure there is only one function with that name in your script.

 

Here is what you need:

 

function onEdit(e) {

   var row = e.range.getRow();

   var col = e.range.getColumn();

    if ((row !=12) || (col != 7)) {return;}

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getActiveSheet();

    var sheetName = sheet.getSheetName();

    if (sheetName != "Sheet8") {return;}

    var oldValue = e.oldValue;

    var valueToCheck = sheet.getRange("G12").getValue();

   if (valueToCheck > 5 && valueToCheck != oldValue) {

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAP5ssfznSNfM6TUxFK%2Bo7RhKRjrKXLsYBsLOyvROxc-kZDr1YQ%40mail.gmail.com.

[Picloan Limited]

thanks so much.you make my day

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/008d01d76590%24e2198be0%24a64ca3a0%24%40gmail.com.

[cbmserv...@gmail.com]

You are welcome.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAP5ssfwEkRrEM%2Bgesv%3DTmYWUvKK0H-7%3DuRTVqJaTqf8G2ASQEQ%40mail.gmail.com.

[Picloan Limited]

please one more question on this conditional copy and paste, what if I want to copy only if cell G12 Changes like this script below

function onChange(e) {//not onEdit

   var row = e.range.getRow();

   var col = e.range.getColumn();

    if ((row !=12) || (col != 7)) {return;}

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getActiveSheet();

    var sheetName = sheet.getSheetName();

    if (sheetName != "Sheet8"||(row.getA1Notation() !== 'G12' )  ) {return;}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/001a01d76600%24746a36b0%245d3ea410%24%40gmail.com.

[Picloan Limited]

please ignore the first question,what I really want is ,I want to copy two cell if condition is met.

function onChange(e) {//not onEdit

   var row = e.range.getRow();

   var col = e.range.getColumn();

    if ((row !=12) || (col != 7)) {return;}

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getActiveSheet();

    var sheetName = sheet.getSheetName();

    if (sheetName != "Sheet8"||(row.getA1Notation() !== 'G12' )  ) {return;}    

   sheet.getRange('E8').copyTo(sheet.getRange('B8'))//your script

   if (sheetName != "Sheet8"||(row.getA1Notation() !== 'D15' )  ) {return;}  

   sheet.getRange('E10').copyTo(sheet.getRange('B9'))//your script

  }

[CBMServices Web]

If you only have the one condition to check, then once the check is done and has not returned control, you can add below it as many copies as you want.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAP5ssfwfoi3usA%2B7OPUoLAYi_in5NvETk1GoFPK3byqrnof9MA%40mail.gmail.com.

[Picloan Limited]

Thanks but why onChange function is not working only onEdit

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3Vf3-603m97inV%2BrWBcpWHY41izfFap3uJMR0Z6wL2O4Q%40mail.gmail.com.

[CBMServices Web]

Not sure about that. Never personally used onChange. Perhaps someone else has experience with this.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAP5ssfx55dY4%3DnEfW0HAU_Yc5SiWMaBCrq%3Dw%3DSu2k26ehVV%3DNA%40mail.gmail.com.

[Laurie Nason]

Maybe this will help explain the difference - https://itectec.com/webapp/google-sheets-diference-between-onedit-or-onchange-trigger/ 

Essentially - on edit is triggered by changing the values of cells etc - onChange is triggered when changing the structure of the sheet

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3Wf9U48XWL_85pVy9rggynrDyOxvW_42befm%3DCsjAuvCA%40mail.gmail.com.

--

Laurie Nason 

The KAUST School – Operational Analyst

Information Technology Support
Deep in the Bowels of the School
Landline: +966-12-808-6853

Mobile: +966-54-564-3698

[Picloan Limited]

THANKS

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BA7ZWLJ%2B1gT9MZgEa84HZYe5J1jzNc_JQaA3BQDG3n-V%3DEW9A%40mail.gmail.com.