清除内容并从另一张纸复制 [英] Clear content and copy from another sheet

问题描述

我编写了一个脚本，该脚本旨在清除Google表格电子表格的内容，然后复制并粘贴另一张表格的内容.

I have written a script that intends to clear the content of a Google Sheets spreadsheet and copy and paste the content of another sheet.

需要清除的工作表称为"NEW_SHEET"，要复制的工作表称为数据库".

The sheet that needs to be cleared is called "NEW_SHEET" and the one to be copied over is called "Database".

由于某种原因，该脚本目前无法正常工作.当我运行它时，什么也没有发生. 另外，此刻，我正在使用.getActiveRange选择要清除的范围.但是，我希望将其作为A到AC列，并且仅保留到其中有数据的最后一行.

For some reason, the script is not working at the moment. Nothing happens when I run it. Also, at the moment, I am using .getActiveRange to select the range to clear. However, I would like that to be columns A to AC and only to the last row with data in it.

这是我正在使用的代码:

This is the code I am using:

function importDB() {
  //Delete content of New Sheet
  var sh1 = SpreadsheetApp.openById('SHEET_ID').getSheetByName('NEW_SHEET')
  var range1 = sh1.getActiveRange()
  range1.clearContent()

  //Copy new database data
 var sh = SpreadsheetApp.openById('SHEET_ID').getSheetByName('Database')
   var range = sh.getDataRange();  
   var data = range.getValues();
   var ts = SpreadsheetApp.openById('SHEET_ID').getSheetByName('NEW_SHEET')
   ts.setValues(data)
}

推荐答案

您不应在此处使用getActiveRange().下面的代码应该可以工作.

You shouldn't be using getActiveRange() here. The code below should work.

function importDB() {
  //Delete contents of "A:AC" in New Sheet
  var sh1 = SpreadsheetApp.openById('SHEET_ID').getSheetByName('NEW_SHEET').getRange("A:AC");
  sh1.clearContent();

  //Copy new database data
  var sh = SpreadsheetApp.openById('SHEET_ID').getSheetByName('Database');
  var range = sh.getDataRange();  
  range.copyTo(sh1.getCell(1,1));
}

使用copyTo()函数，您仅位于其中的第一个单元格目的地很重要.因此，请注意从数据库"复制的数据不会超出AC列，否则将覆盖"NEW_SHEET"中的数据.为避免这种情况，您可以尝试var range = sh.getRange("A:AC")而不是在整个数据范围内进行复制.

With the copyTo() function, you only the first cell in the destination matters. So you need to be careful that the data being copied from "Database" doesn't extend past column AC, otherwise you'll be overwriting that data in "NEW_SHEET". To prevent that, you could try var range = sh.getRange("A:AC") rather than copying over the entire data range.

这篇关于清除内容并从另一张纸复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！