CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
ella thompson
ella thompson

Reputation: 23

Script is triggering but nothing happens?

I want to create a script that moves data from one sheet to another when I mark it as completed in a particular column. Using some other code I found on the internet, I have this, but when I go in and change that status to completed nothing happens. The trigger page in google apps script says it's executing, but it isn't doing anything to the actual sheet. Here is the code:

    function onEdit(e) {
  if(SpreadsheetApp.getActiveSpreadsheet() == "Planner" && e.value == "Completed"){ //If the edit was on Planner marking the Status "Completed"
  var spr = SpreadsheetApp.getActiveSpreadsheet();
    var myRange = e.range.offset(0,-3,0,3).getValue() //get the information from Planner
    //find the first row of Calendar where completed assignments is blank
    var column = spr.getRange('O:O');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct][0] != "" ) {
    ct++;
    ct++;
     e.source.getSheetByName("Calendar").getRange(ct,15,1,3).setValues(myRange).getValues(); //copy the values from Planner to Calendar
     e.source.getSheetByName("Planner").getRange(myRange).setValues("").getValues(); //delete values from Planner
  ;}
  return (ct);
}
    }

I assume something is wrong with it but I don't know what. I've never used apps script before so I honestly don't know what I'm doing. Here is the sheet: Sheet

I want to move completed homework from the planner sheet to the calendar sheet when I change the status. Thanks so much for any help!!

EDIT: I used lamblichus's code and it works great except that I still want to delete the data from the Planner Sheet after I move it. I tried this code and it didn't work:

function onEdit(e) {
  const ss = e.source;
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() == "Planner" && e.value == "Completed") {
    var otherData = range.offset(0,-3,1,3).getValues();
    var currentClass = range.offset(0,-4).getMergedRanges()[0].getValue();
    var [task,,date] = otherData[0];
    var targetSheet = ss.getSheetByName("Calendar");
    var targetRange = targetSheet.getRange("O1").getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0,1,3);
    targetRange.setValues([[date,task,currentClass]]);
    var initialSheet = ss.getSheetByName("Planner");
    var initialRange = initialSheet.range.offset(0,-3,1,3);
    initialRange.clearContent(); //delete values from Planner
  }
}

Upvotes: 2

Views: 1177

Answers (2)

Iamblichus
Iamblichus

Reputation: 19339

Issues and solution:

There are several issues with your current code:

  • If you want to check the sheet name, you have to use Sheet.getName(). SpreadsheetApp.getActiveSpreadsheet() just returns the active spreadsheet, not sheet, and not its name anyway.
  • If you want to get values from multiple cells, you should use getValues(), not getValue().
  • The third parameter of offset corresponds to the number of rows of the resulting range. Therefore, it should not be 0.
  • The "Class" name is in a merged range, and only the top-left cell in a merged range includes the corresponding value. To get that value, you can use getMergedRanges and retrieve the first element in the resulting array. Since getValue() returns the value in the top-left cell of a range, it will return the "Class" name.

Code sample:

function onEdit(e) {
  const ss = e.source;
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() == "Planner" && e.value == "Completed") {
    var otherDataRange = range.offset(0,-3,1,3);
    var otherData = otherDataRange.getValues();
    var currentClass = range.offset(0,-4).getMergedRanges()[0].getValue();
    var [task,,date] = otherData[0];
    var targetSheet = ss.getSheetByName("Calendar");
    var targetRange = targetSheet.getRange("O1").getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0,1,3);
    targetRange.setValues([[date,task,currentClass]]);
    otherDataRange.clearContent();
  }
}

Upvotes: 1

Gabriel Pierce
Gabriel Pierce

Reputation: 406

It looks like a syntax error on line 14, you put ;}, it should be }; you don't need to tell JavaScript (the coding language that AppScript is based on) when you end comments. But it likes it when you tell it when you end while loops.

Here is the updated code.

    function onEdit(e) {
  if(SpreadsheetApp.getActiveSpreadsheet() == "Planner" && e.value == "Completed"){ //If the edit was on Planner marking the Status "Completed"
    var spr = SpreadsheetApp.getActiveSpreadsheet();
    var myRange = e.range.offset(0,-3,0,3).getValue() //get the information from Planner
    //find the first row of Calendar where completed assignments is blank
    var column = spr.getRange('O:O');
    var values = column.getValues(); // get all data in one call
    var ct = 0;
    while ( values[ct][0] != "" ) {
      ct++;
      ct++;
      e.source.getSheetByName("Calendar").getRange(ct,15,1,3).setValues(myRange).getValues(); //copy the values from Planner to Calendar
      e.source.getSheetByName("Planner").getRange(myRange).setValues("").getValues(); //delete values from Planner
    };
    return (ct);
  };
}

Upvotes: 0

Related Questions