Google Apps脚本：仅复制和粘贴公式 [英] Google Apps Script: Copy and Paste Formulas Only

问题描述

我需要仅使用Google Apps脚本将公式复制并粘贴到电子表格中。

I need to copy and paste Formulas only in a spreadsheet using Google Apps Script

var range = activeSheet.getRange(targetRow-1, 1, 1, activeSheet.getLastColumn());
range.copyTo(activeSheet.getRange(targetRow, 1, 1, activeSheet.getLastColumn()), {contentsOnly:false});

这给我提供了完整的信息，包括公式和数据。但是，我希望只复制公式（不含数据）。

This give me the full information, including the formulas and data. However, I wish to only copy the formulas (without data).

CopyFormulasToRange 不幸的是不存在。

推荐答案

以下函数将仅复制公式。它有用户设置，用于开始获取公式的行和列。

The following function will copy formulas only. It has user settings for what row and column to start getting the formulas from.

function copyFormulas() {
  var activeSheet,numberOfSourceColumnsToGet,sourceColumnStart,sourceFormulas,sourceRange,
      sourceRowStart,targetColumn,targetRange,targetRowStart;

  //USER INPUT

  sourceRowStart = 1; //Row to start getting formulas from
  sourceColumnStart = 2; //Column to start getting formulas from
  numberOfSourceColumnsToGet = 1; //Number of columns to get formulas from

  targetRowStart = 1; //Row to start copying formulas to
  targetColumn = 3; //Column to start copying formulas to

  //END OF USER INPUT

  activeSheet = SpreadsheetApp.getActiveSheet();
  sourceRange = activeSheet.getRange(sourceRowStart, sourceColumnStart, activeSheet.getLastRow(), numberOfSourceColumnsToGet);

  sourceFormulas = sourceRange.getFormulas();//Get only formulas from the source range

  targetRange = activeSheet.getRange(targetRowStart,targetColumn,sourceFormulas.length,sourceFormulas[0].length);

  targetRange.setFormulas(sourceFormulas);//Copy the formulas to the target range
}

这篇关于Google Apps脚本：仅复制和粘贴公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！