如何将数据从工作表1移动到工作表2并将工作表1数据附加到旧工作表2数据之上 [英] How to move data from Sheet 1 to Sheet 2 and append Sheet 1 data above old Sheet 2 data

问题描述

我在将数据从s = ss.getSheetByName('Last Seven Days');追加到ts = tss.getSheetByName('History');的顶部时遇到问题.我在该区域的XP值很低，因此当我绕开它时可能需要一些手握力.

I am having an issue getting my data from s = ss.getSheetByName('Last Seven Days'); to append to the top of ts = tss.getSheetByName('History');. My xp in this area is very low so may need a bit of hand-holding while I get my head around it.

当前，我只能使用以下脚本使函数saveToHistory将过去七天"的数据附加到历史记录表"的底部:

Currently I can only get my function saveToHistory to append the data from 'Last Seven Days' to the bottom of the 'History Sheet' with the below script:

function saveToHistory() {
  var ss, s, r, v, target,ts,tss;
  ss = SpreadsheetApp.getActive();
  s = ss.getSheetByName('Last Seven Days');
  if (s.getRange(2, 1).getValue()) {  
    tss = SpreadsheetApp.getActive();
    ts = tss.getSheetByName('History'); // destination Sheet tab name
    s.getRange("A2:W").moveTo(ts.getRange(ts.getLastRow()+1, 1)); // Added
  }
}

是否有一个.getFirstRow?或会产生这种效果的东西?

Is there a .getFirstRow ?? or something that would have that effect?

提前谢谢！

推荐答案

您可以通过以下方式进行操作:

You can do this in the following way:

1. 获取最近7天的数据var lastSevenDays = sheet1.getValues();
2. 从历史记录表var history = sheet2.getValues();
中获取数据
3. 清除历史记录表sheet2.clear();
4. 将历史记录表中的数据连接到最近7天lastSevenDays.concat(history);
5. 将合并的数据写回到工作表中. sheet2.setValues(lastSevenDays);

1. Get the data from last 7 days var lastSevenDays = sheet1.getValues();
2. Get the data from the history sheet var history = sheet2.getValues();
3. Clear the history sheet sheet2.clear();
4. Concatenate the data form the history sheet to the last 7 days lastSevenDays.concat(history);
5. Write the combined data back to the sheet. sheet2.setValues(lastSevenDays);

您的最终代码如下:

(假设两个表上都有标题，并且数据的宽度相同)

(Assuming you have header on both tables and the width of the data is the same)

function moveToHistory() {
  var ss = SpreadsheetApp.getActive();
  var lastSevenDays = ss.getSheetByName("Last Seven Days");
  var history = ss.getSheetByName("History");

  var historyDataWithoutHeader = history.getDataRange().getValues();
  historyDataWithoutHeader.shift(); //This removes the first row (the header)

  var finalData = lastSevenDays.getDataRange().getValues().concat(historyDataWithoutHeader);

  history.getRange(1, 1, finalData.length, finalData[0].length).setValues(finalData);
}

希望这会有所帮助！

这篇关于如何将数据从工作表1移动到工作表2并将工作表1数据附加到旧工作表2数据之上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！