如何自动将数据从一个谷歌表格移动到另一个谷歌表格 [英] How to automate moving data from one google sheet to another

问题描述

我尝试将Google表格中的值发送到Firebase，以便数据表自动更新。为此，我使用Google Drive CMS将数据完美导出到Firebase。问题是我从网站上抓取数据。例如，我通过使用importXML得到一个数据列表：

I am trying to send values from Google sheets to Firebase so that the data table updates automatically. To do this I used Google Drive CMS which exported the data to Firebase perfectly. The problem is that I scrape data off of websites. For example, I get a list of data through the use of importXML:

=IMPORTXML("https://www.congress.gov/search?q=%7B%22source%22%3A%22legislation%22%7D", "//li[@class='expanded']/span[@class='result-heading']/a[1]")

CMS似乎没有得到这个公式导致的值，但实际上公式导致错误。我强调这一点的方式是制作一个新的带有公式的选项卡，并只保留CMS选项卡的值。我一直在手动复制和粘贴，但想要自动完成该过程。我无法找到任何帮助来制作一个脚本，该脚本从一个选项卡获取公式的值，并将这些值放在另一个表中。

CMS doesn't seem to get the values that this formula results in but the actual formula which causes errors. The way I adressed this is to make a new tab that has the formulas and keep the CMS tab with the value only. I've been copying and pasting this manually but want to make that process automatic. I cannot find any help to make a script that takes the values of the formulas from one tab and place those values in a different sheet.

以下是一些图片以供参考：

Here are some pictures for reference:

*我将蓝色突出显示放在Google表格的单元格上，而firebase中的数据显示已导出的第一行数据。 p>

*I put the blue highlight on the cell I am referring to for the google sheets and the data from the firebase is showing the first row of data that was exported.

推荐答案

这个示例脚本怎么样？请把它想成几个答案之一。这个脚本的流程如下。当您使用此脚本时，请将其复制并粘贴，然后运行 sample（）。

流程：

How about this sample script? Please think of this as one of several answers. The flow of this script is as follows. When you use this script, please copy and paste it and run sample().

1. 输入活动工作表的源范围。
   
2. 检索源范围。
   
3. 输入目标电子表格的目的地范围。 从源范围中复制数据到目的地范围。
   
   
   
   
   • 复制了包含值，公式和格式的数据。
   
   

示例脚本：

Sample script :

function sample() {
  // Source
  var range = "a1:b5"; // Source range
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcrange = ss.getActiveSheet().getRange(range);

  // Destination
  var range = "c1:d5"; // Destination range,
  var dstid = "### file id ###"; // Destination spreadsheet ID
  var dst = "### sheet name ###"; // Destination sheet name
  var dstrange = SpreadsheetApp.openById(dstid).getSheetByName(dst).getRange(range);

  var dstSS = dstrange.getSheet().getParent();
  var copiedsheet = srcrange.getSheet().copyTo(dstSS);
  copiedsheet.getRange(srcrange.getA1Notation()).copyTo(dstrange);
  dstSS.deleteSheet(copiedsheet);
}

如果我误解了您的问题，我很抱歉。

If I misunderstand your question, I'm sorry.

此示例脚本从源电子表格的值复制到目标电子表格。

This sample script copies from values of source spreadsheet to destination spreadsheet.

function sample() {
  // Source
  var range = "a1:b5"; // Source range
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcrange = ss.getActiveSheet().getRange(range);

  // Destination
  var range = "c1:d5"; // Destination range,
  var dstid = "### file id ###"; // Destination spreadsheet ID
  var dst = "### sheet name ###"; // Destination sheet name
  var dstrange = SpreadsheetApp.openById(dstid).getSheetByName(dst).getRange(range);

  var dstSS = dstrange.getSheet().getParent();
  var sourceValues = srcrange.getValues();
  dstrange.setValues(sourceValues);
}

这篇关于如何自动将数据从一个谷歌表格移动到另一个谷歌表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！