在Google表格之间自动复制数据 [英] Automatically Copy Data Between Google Sheets

问题描述

我正在寻找一些自动在Google表格之间复制数据的帮助.

I am looking for some help automating copying data between google sheets.

我有一个名为当前数据"的工作表.该工作表的A-F列中有数据(带标题). 列A包含用户名. B-F列将具有使用以下方法从Instagram.com提取数据的公式:

I have a sheet called "Current Data". This sheet has data in columns A-F(with headers). Column A contains usernames. Columns B-F will have formulas which pulls data from Instagram.com using

= VALUE(REGEXEXTRACT(IMPORTXML(

=VALUE(REGEXEXTRACT(IMPORTXML(

我从此网站获得了公式: https://www.benlcollins.com/spreadsheets/import-social-media -statistics/

I got the formulas from this website: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/

还有另一个工作表，称为历史数据".该表包含与当前数据"(A-F，带标题)相同的列. 此工作表包含当前数据"工作表中的所有数据，每天粘贴一次.

There is another sheet called "Historical Data". This sheet contains the same columns as "Current Data" (A-F, with headers). This sheet contains all data from the "Current Data" sheet, pasted daily.

我当前的过程:

• 打开工作表导航到当前数据"工作表，从A2-FXXX复制值
• 导航到历史数据"表，滚动到下一个空白行，粘贴 数据.

• Open Sheet Navigate to "Current Data" sheet, copy values from A2-FXXX
• Navigate to "Historical Data" sheet, scroll to next blank row, paste data.

我正在寻求自动化，并且每天都会发生.

I am looking to automate this and have it occur daily.

我正在使用此脚本自动更新我的IMPORTXML函数.这很好用. 定期刷新IMPORTXML()电子表格功能

I am using this script to automatically update my IMPORTXML function. This works great. Periodically refresh IMPORTXML() spreadsheet function

• 我正在将值从当前数据表"复制到历史数据" 数据表，使用此脚本. 使用Google电子表格脚本复制并粘贴.
  • 此脚本也可以使用，但是它只是将第一行数据复制到目标位置.该脚本还清除了当前数据"表中的数据.
  • 我删除了"source.clear();"从代码，但 数据仍然被擦除.
  • I am copying the values from the Current Data sheet to the Historical Data sheet, using this script. copy and paste with google spreadsheet script.
    • This script also works, but it is only copying the first line of data to the destination. The script is also wiping the data from the Current Data sheet.
    • I removed the "source.clear ();" from the code, but the data still gets wiped.

    我也尝试使用此脚本，因为有些人提到用户需要使用appendRow而不是copyTo.这段代码仍然没有运气:

    I also tried using this script, as some people mentioned users needed to use appendRow instead of copyTo. Still no luck with this code:

    function moveValuesOnlyy() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var source = ss.getRange("CurrentData!A2:F100");
      var destSheet = ss.getSheetByName("HistoricalData");
      destSheet.appendRow(source.getValues());
     }
    

    最后，我正在寻找要执行的脚本:

    In the end I am looking for the script to:

    • 从当前数据"表中查找并复制数据范围，从A2开始.
    • 在历史数据"表上找到下一个空行.
    • 粘贴为值.

    任何帮助将不胜感激，谢谢！

    Any help would be greatly appreciated, thank you!

    推荐答案

    尝试一下:

    function copyPaste() {
      var ss=SpreadsheetApp.getActive();
      var srcsh=ss.getSheetByName('CurrentData');
      var dessh=ss.getSheetByName('HistoricalData');
      var srcrg=srcsh.getRange('A2:F100');
      var data=srcrg.getValues();
      var desrg=dessh.getRange(dessh.getLastRow() + 1,1,99,6);
      desrg.setValues(data);
    }
    

    这篇关于在Google表格之间自动复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！