Google sheets script to move row to bottom if marked "done" AND work on all tabs except 1st

huangapple go评论68阅读模式
英文:

Google sheets script to move row to bottom if marked "done" AND work on all tabs except 1st

问题

I can help you modify the two scripts to work on all tabs but only when the status in column B is marked as "done." Here's the modified script:

function onEdit(e) {
  const sh = e.range.getSheet();
  const statusColumn = 2; // Assuming column B is the status column

  // Check if the edited sheet is not the first sheet (overview) and the edited column is the status column
  if (sh.getIndex() > 0 && e.range.columnStart == statusColumn && e.range.rowStart > 1 && e.value === "done") {
    const row_new = sh.getRange(e.range.rowStart, 1, 1, e.range.columnStart);
    row_new.copyTo(sh.getRange(sh.getLastRow() + 1, 1, 1, e.range.columnStart));
    sh.deleteRow(e.range.rowStart);
  }
}

This script will run on all tabs except the first one (assuming it's the overview) and will move a row to the bottom of its own tab when the status in column B is marked as "done." Make sure to replace the statusColumn variable with the correct column number if your status column is different from column B.

英文:

I have a google sheet for projects. First tab is an overview of all the following tabs. I then have one separate tab for each project, and each project tab has a to-do list of tasks.

The set-up for all project tabs are the same - the top 2 rows are headers, 20 rows total (including headers), 3 columns:
A - task
B - status (blank=not started/in progress/done)
C - notes

What I want to do is add a script that will move a row to the bottom of it's own tab once the status is marked as "done". And I want it to run on all tabs except the 1st which is the overview.
Some of the 20 rows will be empty since most of the projects don't have that many tasks. It would be best if "done" tasks are moved to the very last row (row 20) in the tab (leaving empty rows in between pending and done tasks), but not necessary - if they move to the bottom of the active tasks instead, say row 8 if I have 7 tasks filled in (leaving the empty rows on the bottom of the tab) that would also work okay.

(The reason I want this in a script is that on the overview tab I want to pull from each project tab the next task due on the to-do list - which should then always be in A3 - just to make it easier to pick what to do next instead of having to go into each tab to find the next task. This part I know how to do.)

I've tried searching on here and google for answers, but since I don't know how to write scripts myself I haven't been able to figure out what to modify when the poster's question isn't exactly the same as mine.

I found a script that moves the rows marked as "done" to the bottom, but it only works for one tab:

function onEdit(e) {
const row = e.range.getRow();
const col = e.range.getColumn();
const as = e.source.getActiveSheet();
const lc = as.getLastColumn();
if(as.getName() == "Tab2" && col == 2  && row > 1 && as.getRange(row,col).getValue() == 'done') {
const row_new = as.getRange(row, 1, 1, lc);
row_new.copyTo(as.getRange(as.getLastRow() + 1, 1, 1, lc));
as.deleteRow(row);
} 
}

I found another script that works on all tabs, but which also runs on all edits to column B.

function onEdit(e) {
const sh = e.range.getSheet();
const shts = ['Sheet1','Sheet2','Sheet3'];
const idx = shts.indexOf(sh.getName())
if(~idx && e.range.columnStart == 2  && e.range.rowStart > 1 && e.value) {  
const row_new = sh.getRange(e.range.rowStart,1,1,e.range.columnStart);
row_new.copyTo(sh.getRange(sh.getLastRow()+1,1,1,e.range.columnStart));
sh.deleteRow(e.range.rowStart);
}
}

Can I merge/modify these two scripts to do work on all tabs but only when the status in column B is marked as "done"? How?

答案1

得分: 0

使用Miguel Rivera Rios的建议,我通过使用公式而不是脚本来解决了这个问题,这对我所需的功能完美地起作用,而且更简单。

我在我的总览表格(工作表中的第一个选项卡)的B列(下一个任务)中添加了以下公式,并仅编辑了每一行的选项卡名称(Project1,Project2等)。因此,我的总览表格具有以下列:A - 项目,B - 下一个任务(这里有公式,现在从每个项目选项卡的待办事项列表中提取下一个未完成的任务)。我还编辑了起始行为A3/B3,因为我的项目选项卡中的前两行是标题。

=index(filter(Project1!A3:A,Project1!B3:B<>"Done"),1,1)
英文:

Solved this by the suggestion from Miguel Rivera Rios to use a formula instead of a script which worked perfectly for what I needed and was much simpler way to do it.

I added the formula below in column B (next task) of my overview sheet (first tab in the sheet), and just edited the tab names for each row (Project1, Project2, etc). So my overview sheet has column A - projects, B - next tasks (the formula is here which now pulls the next non-completed task from each project tab's to-do list). I also edited the starting row to A3/B3 since my first two rows in the project tabs are headers.

=index(filter(Project1!A3:A,Project1!B3:B&lt;&gt;&quot;Done&quot;),1,1)

huangapple
  • 本文由 发表于 2023年7月24日 16:02:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/76752461.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定