从电子表格复制到具有动态标题的另一个电子表格 [英] copy from spreadsheet to another spreadsheet with dynamic title

问题描述

我目前正在一个项目中，我需要将数据从电子表格中的一个特定工作表复制到另一个具有动态标题(带有日期的文本)的电子表格中.可以使用ID或名称移动数据，但是使用新创建的电子表格似乎很麻烦.所有功能都将在同一个应用脚本中:创建复制

I'm currently working on a project where to I need to copy data from one specific sheet in a spreadsheet to another spreadsheet with a dynamic title ( text with date). moving the data with ID or by name is ok but with a freshly created spreadsheet seems tuff. all the function will be in the same app script: creation copy

function titleAsDate() {

  var currentDate = Utilities.formatDate(new Date(), "GMT+8", "dd-MM-yyyy HH:mm:ss")
  SpreadsheetApp.create("Report of the " + currentDate)
}

function copyWithValues() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Sources');
  
  let sourceRange = sourceSheet.getDataRange();
  let sourceValues = sourceRange.getValues();
  
  let rowCount = sourceValues.length;
  let columnCount = sourceValues[0].length;
  
  let targetSheet = spreadSheet.getSheetById('Target');
  let targetRange = targetSheet.getRange(1, 1, rowCount, columnCount);
  
  targetRange.setValues(sourceValues);
}

推荐答案

说明:

• SpreadsheetApp.create(name)返回电子表格对象，以便您可以直接使用无需额外的代码即可直接使用此功能.

  Explanation:

  • SpreadsheetApp.create(name) returns a spreadsheet object so you can directly use the output of this function directly without the need of extra code.

    当您手动创建新的电子表格文件时，新生成的电子表格将具有一张名为 Sheet1 的工作表.因此，您可以使用 sheet.setName(name)函数将工作表的名称更改为 Target.此函数还会返回 sheet 对象( targetSheet )，然后可以用来设置值.

    The newly generated spreadsheet will have one sheet with the name Sheet1 as when you manually create a new spreadsheet file. Therefore, you can use the sheet.setName(name) function to change the name of the sheet to Target. Also this function returns a sheet object (targetSheet) which can then be used to set the values.

    这是一个功能中的所有代码:

    This is all the code in one function:

    function copyWithValues() {
      const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
      const sourceSheet = spreadSheet.getSheetByName('Sources');
      const sourceRange = sourceSheet.getDataRange();
      const sourceValues = sourceRange.getValues();
      
      const currentDate = Utilities.formatDate(new Date(), "GMT+8", "dd-MM-yyyy HH:mm:ss"); // new code
      const targetSpreadsheet = SpreadsheetApp.create("Report of the " + currentDate); // new code
      
      let rowCount = sourceValues.length;
      let columnCount = sourceValues[0].length;
      
      let targetSheet = targetSpreadsheet.getSheetByName('Sheet1').setName("Target"); // new code
      let targetRange = targetSheet.getRange(1, 1, rowCount, columnCount);
      
      targetRange.setValues(sourceValues);
    }
    

    如果要将 titleAsDate 用作帮助函数，而该函数将由 copyWithValues 调用，则可以使用此代码并仅执行 copyWithValues :

    If you want to use titleAsDate as a helper function which will be called by copyWithValues, then you can use this code and execute only copyWithValues:

    // helper function, used by copyWithValues
    function titleAsDate() {
      const currentDate = Utilities.formatDate(new Date(), "GMT+8", "dd-MM-yyyy HH:mm:ss");
      return SpreadsheetApp.create("Report of the " + currentDate); // new code
    }
    
    // main function, you should execute this function
    function copyWithValues() {
      const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
      const sourceSheet = spreadSheet.getSheetByName('Sources');
      const sourceRange = sourceSheet.getDataRange();
      const sourceValues = sourceRange.getValues();
      
      const targetSpreadsheet = titleAsDate(); // new code
      
      let rowCount = sourceValues.length;
      let columnCount = sourceValues[0].length;
      
      let targetSheet = targetSpreadsheet.getSheetByName('Sheet1').setName("Target"); // new code
      let targetRange = targetSheet.getRange(1, 1, rowCount, columnCount);
      
      targetRange.setValues(sourceValues);
    }
    

    这篇关于从电子表格复制到具有动态标题的另一个电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！