首页 文章

Google表格中的Shift Cell范围

提问于
浏览 次
0

我正在尝试取一系列细胞并将它们全部移到右边一个细胞 . 我希望每周自动发生一次 . 我知道在脚本编辑器中我可以创建一个触发器来安排每周安排，但我不知道如何编写代码 .

如果有人可以帮助提供一个代码，允许我指示哪个SHEET和CELL RANGE向右移动一个单元格，我将不胜感激 .

基本上，我想要完成的是跟踪数据数周 . 每周都会更新工作表，我希望旧数据向右移动一个，基本上表明数据已经过了一周 .

此外，我只是试图将数据保存6周，因此数据不会永远持续下去 .

举个例子：A列=当前周（手动更新） . 列B-F =前几周（1-5周前） .

每周一次，A-E列中的数据应右移1以保留数据 . 在班次之后，在A列中手动更新数据以表示当前周 . 这将导致显示本周和另外5周的数据......共计6周的数据 .

如果可能的话，如果有一种方法可以在排除 Headers 行的同时排除列，而不是范围，我会感兴趣 .

javascript google-apps-script google-sheets

3 回答

  • 3

    可以尝试这样的事吗？

    function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This inserts 1 columns before the first column
 sheet.insertColumnsBefore(1, 1);
  
  // labels the weeks static
      ss.getRange('A1').setFontWeight("bold").setValue("Week1").setBackground("#BBBBBB");
      ss.getRange('B1').setFontWeight("bold").setValue("Week2").setBackground("#BBBBBB");
      ss.getRange('C1').setFontWeight("bold").setValue("Week3").setBackground("#BBBBBB");
      ss.getRange('D1').setFontWeight("bold").setValue("Week4").setBackground("#BBBBBB");
      ss.getRange('E1').setFontWeight("bold").setValue("Week5").setBackground("#BBBBBB");
       ss.getRange('F1').setFontWeight("bold").setValue("Week6").setBackground("#BBBBBB");
}
    回复于
  • 0

    复制或剪切和粘贴

    根据您的上次评论，我决定再给您一个解决方案 . 此解决方案可以将任何工作表中的任何数据范围复制或剪切并粘贴到具有相同尺寸的任何其他范围 . 范围可以在同一页面或不同页面上，范围可以重叠 . 此外，复制范围后，文档将记住源和目标范围，以便您可以复制或剪切并粘贴它们，而无需设置范围 . 如果你想改变范围只需运行例程，它将让你选择清除范围，然后你可以再次运行它，它会记住新的范围 . 菜单上的复制和剪切选择运行例程 . “显示属性”选项显示当前设置 . 清除范围将删除DocumentProperties中的所有内容 .

    但复制和剪切选项将为您提供所有相同的信息 .

    function onOpen()
{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('My Tools')
        .addItem('Copy or Cut', 'copyFromToSetupUi')
        .addItem('Display Properties','dispProperties')
        .addItem('Clear Ranges','clearCopyProperties')
        .addToUi();
}

function dispProperties()
{
  var copyProperties = PropertiesService.getDocumentProperties();
  var srcShtNameStr = copyProperties.getProperty('SourceSheetName');
  var srcShtRangeStr = copyProperties.getProperty('SourceSheetRange');
  var desShtNameStr = copyProperties.getProperty('DestinationSheetName');
  var desShtRangeStr = copyProperties.getProperty('DestinationSheetRange');
  var title = 'Copy From To Sheets Properties';
  var msg = 'Source Sheet Name = ' + srcShtNameStr + '
    ';
  msg += 'Source Sheet Range = ' + srcShtRangeStr + '
    ';
  msg += 'Destination Sheet Range = ' + desShtNameStr + '
    ';
  msg += 'Destination Sheet Range = ' + desShtRangeStr + '
    ';
  msg += '<input type="button" value="Exit" onClick="google.script.host.close();" />';
  dispStatus(title,msg);
}

function copyFromToSheets()
{
  var copyProperties = PropertiesService.getDocumentProperties();
  var srcRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(copyProperties.getProperty('SourceSheetName')).getRange(copyProperties.getProperty('SourceSheetRange'));
  var srcA = srcRange.getValues();
  srcRange.setBackground('#ffffff');
  var desRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(copyProperties.getProperty('DestinationSheetName')).getRange(copyProperties.getProperty('DestinationSheetRange'));
  desRange.setValues(srcA);
  desRange.setBackground('#ffffff');
}

function cutnpasteFromToSheets()
{
  var copyProperties = PropertiesService.getDocumentProperties();
  var srcRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(copyProperties.getProperty('SourceSheetName')).getRange(copyProperties.getProperty('SourceSheetRange'));
  var srcA = srcRange.getValues();
  srcRange.clearContent();
  srcRange.setBackground('#ffffff');
  var desRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(copyProperties.getProperty('DestinationSheetName')).getRange(copyProperties.getProperty('DestinationSheetRange'));
  desRange.setValues(srcA);
  desRange.setBackground('#ffffff');
}

function setCopySource()
{
  var srcShtName = SpreadsheetApp.getActiveSheet().getName();
  var srcShtRange = SpreadsheetApp.getActiveRange();
  var copyProperties = PropertiesService.getDocumentProperties();
  copyProperties.setProperty('SourceSheetRange', srcShtRange.getA1Notation());
  copyProperties.setProperty('SourceSheetName', srcShtName);
  srcShtRange.setBackground('#d9caa9');
}

function setCopyDestination()
{
  var desShtName = SpreadsheetApp.getActiveSheet().getName();
  var desShtRange = SpreadsheetApp.getActiveRange();
  var copyProperties = PropertiesService.getDocumentProperties();
  copyProperties.setProperty('DestinationSheetRange',desShtRange.getA1Notation());
  copyProperties.setProperty('DestinationSheetName', desShtName);
  desShtRange.setBackground('#c4df87');
}

function clearCopyProperties()
{
  var copyProperties = PropertiesService.getDocumentProperties();
  var srcShtNameStr = copyProperties.getProperty('SourceSheetName');
  var srcShtRangeStr = copyProperties.getProperty('SourceSheetRange');
  var desShtNameStr = copyProperties.getProperty('DestinationSheetName');
  var desShtRangeStr = copyProperties.getProperty('DestinationSheetRange');
  if(srcShtNameStr && srcShtRangeStr)
  {
    var srcShtRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(srcShtNameStr).getRange(srcShtRangeStr);
    srcShtRange.setBackground('#ffffff');
  }
  else
  {
    SpreadsheetApp.getUi().alert('At least one of the Source String Properties is undefined in clearCopyProperties so background color cannot be reset.');
  }
  if(desShtNameStr && desShtRangeStr)
  {
    var desShtRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(desShtNameStr).getRange(desShtRangeStr);
    desShtRange.setBackground('#ffffff');
  }
  else
  {
    SpreadsheetApp.getUi().alert('At least one of the Destination String Properties is undefined in clearCopyProperties so background color cannot be reset.');
  }
  copyProperties.setProperty('SourceSheetName', '');
  copyProperties.setProperty('SourceSheetRange', '');
  copyProperties.setProperty('DestinationSheetName', '');
  copyProperties.setProperty('DestinationSheetRange', '');
}

function copyFromToSetupUi()
{
  var copyProperties = PropertiesService.getDocumentProperties();
  var srcShtNameStr = copyProperties.getProperty('SourceSheetName');
  var srcShtRangeStr = copyProperties.getProperty('SourceSheetRange');
  var desShtNameStr = copyProperties.getProperty('DestinationSheetName');
  var desShtRangeStr = copyProperties.getProperty('DestinationSheetRange');
  var title='No Title';
  var msg = 'No Text';
  if(!srcShtNameStr || !srcShtRangeStr )  //if !src
  {
    title = 'Select Source Range';
    msg = '<p>Please select input range from <strong>Source Sheet.</strong> and then press "Source Range Selected" button below.</p>\
    
    <input type="button" value="Source Range Selected" onclick="google.script.run.copyFromToSetupHelper(1);google.script.host.close();" />';
    msg += '<script>console.log(\'flag1\');</script>';
    dispStatus(title, msg);
  }
  if ((srcShtNameStr && srcShtRangeStr) && (!desShtNameStr || !desShtRangeStr)) //if src and !des
  {
      var srcShtRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(srcShtNameStr).getRange(srcShtRangeStr);
      title = 'Select Destination Range';
      msg = '<p>Please select a destination range which is ' + srcShtRange.getNumRows() + ' rows by ' + srcShtRange.getNumColumns() + ' columns.</p>';
      msg += '
    <input type="button" value="Destination Range Selected" onclick="google.script.run.copyFromToSetupHelper(2);google.script.host.close();" />';
      msg += '
    Input Range: ' + srcShtRangeStr + '
    <input type="button" value="Clear Ranges and Start Over" onClick="google.script.run.clearCopyProperties();google.script.host.close(); />';
      dispStatus(title, msg);
  }
  if((srcShtNameStr && srcShtRangeStr) && (desShtNameStr && desShtRangeStr))//if src and des
  {
    var srcShtRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(srcShtNameStr).getRange(srcShtRangeStr);
    var desShtRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(desShtNameStr).getRange(desShtRangeStr);
    if((desShtRange.getWidth()===srcShtRange.getWidth()) && (desShtRange.getHeight()===srcShtRange.getHeight()))
    {
      title= 'Displaying Source and Destination Ranges';
      msg = '
    Source Sheet/Range: ' + srcShtNameStr + '/' + srcShtRangeStr + '
    Destination Sheet/Range: ' + desShtNameStr + '/' + desShtRangeStr + '
    ';
      msg += '
    <input type="button" value="Perform Copy" onclick="google.script.run.copyFromToSheets();google.script.host.close();" />';
      msg += '
    <input type="button" value="Perform Cut & Paste" onclick="google.script.run.cutnpasteFromToSheets();google.script.host.close();" />';
      msg += '
    <input type="button" value="Keep both Ranges Defined" onclick="google.script.host.close();" />';
      msg += '
    <input type="button" value="Clear Ranges and Start Over" onclick="google.script.run.clearCopyProperties();google.script.host.close();" />';
      dispStatus(title,msg);
    }
    else
    {
      var srcShtRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(srcShtNameStr).getRange(srcShtRangeStr);
      var desShtRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(desShtNameStr).getRange(desShtRangeStr);
      var newdesShtRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(desShtNameStr).getRange(desShtRange.getRow(), desShtRange.getColumn(), srcShtRange.getNumRows(), srcShtRange.getNumColumns());
      desShtRange.setBackground('white');
      newdesShtRange.setBackground('#c4df87');
      copyProperties.setProperty('DestinationSheetRange', newdesShtRange.getA1Notation());
      title = 'Destination Range Adjusted';

      msg = 'Source Range and Destination Range Dimension did not Match. So it was assumed that the upper left corner of the Destination Range is correct';
      msg += 'and that the Sheet Selections were correct. The Destination Range was modified to have the same dimensions as the Source Range. ';
      msg += '
    Source Sheet/Range: ' + srcShtNameStr + '/' + srcShtRangeStr + '
    Destination Sheet/Range: ' + desShtNameStr + '/' + newdesShtRange.getA1Notation() + '
    ';
      msg += '
    <input type="button" value="Perform Copy" onclick="google.script.run.copyFromToSheets();google.script.host.close();" />';
      msg += '
    <input type="button" value="Perform Cut & Paste" onclick="google.script.run.cutnpasteFromToSheets();google.script.host.close();" />';
      msg += '
    <input type="button" value="Keep both Ranges Defined" onclick="google.script.host.close();" />';
      msg += '
    <input type="button" value="Clear Ranges and Start Over" onclick="google.script.run.clearCopyProperties();;google.script.host.close(); />';
      dispStatus(title,msg);

    }


  }
}


function copyFromToSetupHelper(mode)
{
  var mode = (typeof(mode) !== 'undefined')? mode : 0;
  switch(mode)
  {
    case 1:
      setCopySource();
      copyFromToSetupUi();
      break;
    case 2:
      setCopyDestination();
      copyFromToSetupUi();
      break;
    default:
      clearCopyProperties();
  }
}
// Display a modeless dialog box with custom HtmlService content.
function dispStatus(title,html,width,height)
{
  var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
  var width = typeof(width) !== 'undefined' ? width : 400;
  var height = typeof(height) !== 'undefined' ? height : 300;
  var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
  var htmlOutput = HtmlService
     .createHtmlOutput(html)
     .setWidth(width)
     .setHeight(height);
 SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);
}
    回复于
  • -1

    Google脚本：向任意方向移动活动选择范围

    这将根据您的任意方向移动任何选定范围，并可选择目标范围作为活动选择 . 到目前为止，我只试过一次细胞步骤 .

    function rangeJog(hoffset,voffset,moveselection)
{
  var hoffset = (typeof(hoffset) !== 'undefined')? hoffset : 1;
  var voffset = (typeof(voffset) !== 'undefined')? voffset : 0;
  var moveselection = (typeof(moveselection) != 'undefined')? moveselection : true;
  var src = SpreadsheetApp.getActiveRange();
  var srcA1 = src.getA1Notation();
  var row = src.getRow() + voffset;
  var col = src.getColumn() + hoffset; 
  var rows = src.getLastRow() - src.getRow() + 1; 
  var cols = src.getLastColumn() - src.getColumn() +1;  

  if((row<1) || (col<1))
  {
    //dispStatus('No More Room to Move','<p>Either the first row or the first column or both will be less than one. <input type="button" value="exit" onClick="google.script.host.close();" /></p>', 400, 200);
    SpreadsheetApp.getUi().alert('No more room to move.');
  }
  else
  {
    var des = SpreadsheetApp.getActiveSheet().getRange(src.getRow() + voffset, src.getColumn() + hoffset, src.getLastRow() - src.getRow() + 1, src.getLastColumn() - src.getColumn() +1);
    var srcA = src.getValues();
    src.clearContent();
    des.setValues(srcA);
    if(moveselection)
    {
      SpreadsheetApp.getActiveSheet().setActiveSelection(des);
    }
  }
  var end = "is near";
}

    它现在适用于所选范围，但任何范围都可以工作，因为所有计算都与src范围有关 . 如果moveselection为true，我还修改它以移动选择 . 如果你有麻烦回来，我会尽力帮助你 .

    但是向左侧插入新列的想法可能是一种更简单的方法 . 虽然我没有看到任何插入单元格命令，因此插入列也会移动 Headers . 所以如果你想要的话，我会把它留在这里 .

    回复于

相关问题