windows - Need help: Actively update date on another sheet, sort rows by date, and checkboxes - Stack Overflow

admin管理员组

文章数量:1122846

First, here is my sheet link:

Now what i'm trying to do:

Have the expiring list actively update the info when the master is updated. Actively keep expiring list sorted by expiration date When I manually check a checkbox, it inputs the current date into the cell next to it When the expiration date is updated (changed), I want the checkbox to uncheck. When the expiration date is updated (changed), I want the cells in Column F:H to clear it's content.

What I've accomplished: I've imported data from another sheet using the importrange function, but I can't modify the data after that. I used the sort function to do both the importing data and sort by expiration date like I wanted. I've got the checkbox to input the current date into the cell next to it using =IF(E2=true, NOW(), "")

What i'm having trouble with or can't get to work:

I can't get the cells in columns E:H to move with the rest of the row when they move due to the data moving from changed expiration date and it being sorted. (they're not part of the imported data in the sorting function) I tried an AppScript for the clearing content out of cells, but it wasn't working. (I think because I wasn't making the change on the sheet, the date was being updated from another sheet. I've also tried using the filter and filter view to help with the sorting problem early on, but It wouldn't sort the data, possibly because the data was being imported from another sheet.

Addon thought: If the cells in E:H stayed with the rest of the row's data when moving due to sorting, I don't think I would need the auto clearing of the content, I could just manually clear the cells. It would be a cherry on the sundae though.

Any help would be appreciated. If it involves AppScript, I'm not familiar with it, but I'd be willing to give it a try.

First, here is my sheet link: https://docs.google.com/spreadsheets/d/11UjpVxBH1pto_e6sP_m93mve9ZYEPmwuUc1DkYzB8I8/edit?usp=sharing

Now what i'm trying to do:

Have the expiring list actively update the info when the master is updated. Actively keep expiring list sorted by expiration date When I manually check a checkbox, it inputs the current date into the cell next to it When the expiration date is updated (changed), I want the checkbox to uncheck. When the expiration date is updated (changed), I want the cells in Column F:H to clear it's content.

What I've accomplished: I've imported data from another sheet using the importrange function, but I can't modify the data after that. I used the sort function to do both the importing data and sort by expiration date like I wanted. I've got the checkbox to input the current date into the cell next to it using =IF(E2=true, NOW(), "")

What i'm having trouble with or can't get to work:

I can't get the cells in columns E:H to move with the rest of the row when they move due to the data moving from changed expiration date and it being sorted. (they're not part of the imported data in the sorting function) I tried an AppScript for the clearing content out of cells, but it wasn't working. (I think because I wasn't making the change on the sheet, the date was being updated from another sheet. I've also tried using the filter and filter view to help with the sorting problem early on, but It wouldn't sort the data, possibly because the data was being imported from another sheet.

Addon thought: If the cells in E:H stayed with the rest of the row's data when moving due to sorting, I don't think I would need the auto clearing of the content, I could just manually clear the cells. It would be a cherry on the sundae though.

Any help would be appreciated. If it involves AppScript, I'm not familiar with it, but I'd be willing to give it a try.

• windows
• google-sheets
• automatic-updates

Share Improve this question Follow asked Nov 21, 2024 at 18:49 Joshua CutronaJoshua Cutrona 311 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Use onEdit Simple Trigger

You may use a simple trigger to update the data in the sheet Expiring. However, please take note that the data in columns A through D must be hard coded and not from an IMPORTRANGE function because simple triggers cannot detect changes if the data is updated by formulas. The script for the simple trigger should look like this:

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  var col = e.range.getColumn();
  var name = sh.getName();
  var lr = sh.getLastRow();
  if (col == 5 && e.value === "TRUE" && name == "Expiring") {
    sh.getRange(e.range.getRow(), col + 1).setValue(Utilities.formatDate(new Date(), "GMT", "MM/dd"));
  }
  if (col == 3 && name == "Expiring") {
    sh.getRange(e.range.getRow(), 5, 1, 2).clearContent();
    var data = sh.getRange(2, 1, lr - 1, 8).getValues().sort((x, y) => x[2] - y[2]);
    sh.getRange(2, 1, data.length, data[0].length).setValues(data);
  }
}

As mentioned before, the data in columns A to D should be hard coded and not updated by importrange:

When Column E is triggered, the output should look like this:

When Column C is modified, the output should look like this:

NOTE: onEdit is already applied when you save the script. It will automatically run once you edit the data in the sheet. Running it manually will return an error because the object e will have no value to begin with if no changes were made on the sheet.

References

• Simple Triggers
• Sort

本文标签：