将值设置为其他电子表格中的范围 [英] Set values to ranges in a different spreadsheet

问题描述

任何人都可以提供有关如何在其他电子表格中更新或将值设置为范围的建议.我知道importRange函数会将值带到活动工作表中，但是我想将活动工作表中的单行数据推"到位于不同电子表格中名为FinalData的工作表底部的行中.

我要推送"的数据由其他代码填充，从而在名为TempData的工作表中产生一行数据.该数据存在于TempData！A2:U2范围内.

我的目标是将TempData！A2:U2中的数据追加到名为"DataFinal"的表底部的新行中，该表位于完全独立的google电子表格中(但在同一"google驱动器"上.)

这是我到目前为止尝试过的:

// Row to FinalData
   var ss = SpreadsheetApp.getActiveSpreadsheet ();

   var startSheet = ss.getSheetByName("TempData");

   var sourceRange = ss.getRange ("TempData!A2:U");

   var target = SpreadsheetApp.openById("1bWKS_Z1JwLSCO5WSq1iNP1LLQpVXnspA4WkzdyxYDNY");

   var targetSheet = target.getSheetByName("DataFinal");

   var lastRow = targetSheet.getLastRow();

   targetSheet.insertRowAfter(lastRow); 

   sourceRange.copyTo(targetSheet.getRange(lastRow + 1,1), {contentsOnly: true});

运行该程序时，出现错误消息目标范围和源范围必须在同一电子表格上".必须有一种方法可以做到-任何建议都将受到欢迎.

解决方案

copyTo()可以用于同一电子表格.

从您的脚本来看，我认为您可以使用getValues()和setValues()来实现它，因为您使用的是contentsOnly: true.那修改呢?

发件人:

sourceRange.copyTo(targetSheet.getRange(lastRow + 1,1), {contentsOnly: true})

收件人:

var sourceValues = sourceRange.getValues();
targetSheet.getRange(lastRow + 1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);

注意:

• 如果您想使用copyTo()，请 copyTo()
• getValues()
• setValues()

如果这不是您想要的，请告诉我.我想修改它.

Can anybody offer suggestions about how to update or set values to a range in a different spreadsheet. I know the importRange function will bring values into the active sheet, but I want to "push" a single row of data from the active sheet to a row at the bottom of a sheet called FinalData, which is in a different spreadsheet.

The data I want to "push" is populated by other code, resulting in a single row of data in a sheet called TempData. The data exists in range TempData!A2:U2.

My goal is to append data from TempData!A2:U2 to a new row at the bottom of a table called "DataFinal", which is in a completely separate google spreadsheet (but on the same "google drive".)

Here's what I tried so far:

// Row to FinalData
   var ss = SpreadsheetApp.getActiveSpreadsheet ();

   var startSheet = ss.getSheetByName("TempData");

   var sourceRange = ss.getRange ("TempData!A2:U");

   var target = SpreadsheetApp.openById("1bWKS_Z1JwLSCO5WSq1iNP1LLQpVXnspA4WkzdyxYDNY");

   var targetSheet = target.getSheetByName("DataFinal");

   var lastRow = targetSheet.getLastRow();

   targetSheet.insertRowAfter(lastRow); 

   sourceRange.copyTo(targetSheet.getRange(lastRow + 1,1), {contentsOnly: true});

When I run it I get an error that says "Target range and source range must be on the same spreadsheet.". There must be a way to do this-- any suggestions would be welcome.

解决方案

copyTo() can be used for the same spreadsheet.

From your script, I think that you can achieve it using getValues() and setValues(), because you use contentsOnly: true. So how about this modification?

From :

sourceRange.copyTo(targetSheet.getRange(lastRow + 1,1), {contentsOnly: true})

To :

var sourceValues = sourceRange.getValues();
targetSheet.getRange(lastRow + 1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);

Note :

• If you want to use copyTo(), this thread might be useful for your situation.

References :

• copyTo()
• getValues()
• setValues()

If this was not what you want, please tell me. I would like to modify it.

这篇关于将值设置为其他电子表格中的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！