使用GAS实时检索电子表格中选定的范围 [英] Real-time retrieval of selected range in spreadsheet using GAS

问题描述

我有一个带有文本框和选择按钮的应用程序脚本UI模式对话框。当按下选择按钮并选择工作表中的单元格范围时，它必须复制由工作表（0）或工作表（1）标识的工作表范围地址位置。有什么想法吗？

解决方案

剪切或复制并粘贴

粘贴或复制并粘贴使用无模式对话框，以便即使在对话框处于活动状态时也可以进行选择。

  function onOpen （）
 {
 var ui = SpreadsheetApp.getUi（）; 
 ui.createMenu（'My Tools'）
 .addItem（'Copy or Cut'，'copyFromToSetupUi'）
 .addItem（'Display Properties'，'dispProperties'）
 .addItem（'Clear Ranges'，'clearCopyProperties'）
 .addToUi（）; 
 
 
函数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 ='从表格属性复制'; 
 var msg ='来源表名称='+ srcShtNameStr +'< br />'; 
 msg + ='Source Sheet Range ='+ srcShtRangeStr +'< br />'; 
 msg + ='Destination Sheet Range ='+ desShtNameStr +'< br />'; 
 msg + ='Destination Sheet Range ='+ desShtRangeStr +'< br />'; 
 msg + ='< input type =buttonvalue =ExitonClick =google.script.host.close（）; />; 
 dispStatus（title，msg）; 
} 
 
函数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'）; 
 
 
函数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'）; 
} 
 
函数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'）; 
 
 
函数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'）; 
 
 
 
 SpreadsheetApp.getUi（）。alert（'至少有一个源字符串属性在clearCopyProperties中是未定义的，所以背景颜色不能被重置。 （desShtNameStr&& desShtRangeStr）
 {
 var desShtRange = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（desShtNameStr）.getRange（desShtRangeStr）; 
} 
。 
 desShtRange.setBackground（'＃ffffff'）; 
 
 
 {
 SpreadsheetApp.getUi（）。alert（'至少有一个目标字符串属性在clearCopyProperties中是未定义的，所以背景颜色不能被重置。 
} 
 copyProperties.setProperty（'SourceSheetName'，''）; 
 copyProperties.setProperty（'SourceSheetRange'，''）; 
 copyProperties.setProperty（'DestinationSheetName'，''）; 
 copyProperties.setProperty（'DestinationSheetRange'，''）; 
 
 
函数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 ='无标题'; 
 var msg ='没有文字'; 
 if（！srcShtNameStr ||！srcShtRangeStr）// if！src 
 {
 title ='选择源范围'; 
 msg ='< p>请从< strong>来源表中选择输入范围。< / strong> < / p> \ 
< br />< input type =buttonvalue =源范围选择onclick =google.script。 run.copyFromToSetupHelper（1）; google.script.host.close（）; />; 
 msg + ='< script> console.log（\'flag1\'）;< / script>'; 
 dispStatus（title，msg）; （（srcShtNameStr& srcShtRangeStr）&&（！desShtNameStr ||！desShtRangeStr））//如果src和！des 
 {
 var srcShtRange = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（srcShtNameStr）.getRange（srcShtRangeStr）; 
 title ='选择目的地范围'; 
 msg ='< p>请按'+ srcShtRange.getNumColumns（）+'列选择'+ srcShtRange.getNumRows（）+'行的目标范围。< / p>'; 
 msg + ='< br />< input type =buttonvalue =目的地范围选择onclick =google.script.run.copyFromToSetupHelper（2）; google.script.host.close （）; />; 
 msg + ='< br />输入范围：'+ srcShtRangeStr +'< br />< input type =buttonvalue =清除范围并重新开始onClick =google。 script.run.clearCopyProperties（）; google.script.host.close（）; />'; 
 dispStatus（title，msg）; 
} 
 if（（srcShtNameStr&& amp; amp; （srcShtRangeStr）&& 
 var desShtRange = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（desShtNameStr）.getRange（desShtRangeStr）; 
 if（（desShtRange.getWidth（）=== srcShtRange.getWidth（））&&（desShtRange .getHeight（）=== srcShtRange.getHeight（）））
 {
 title ='显示源和目标范围'; 
 msg ='< br />源表单/范围：'+ srcShtNameStr +'/'+ srcShtRangeStr +'< br />目的地页/范围：'+ de sShtNameStr +'/'+ desShtRangeStr +'< br />'; 
 msg + ='< br />< input type =buttonvalue =执行复制onclick =google.script.run.copyFromToSheets（）; google.script.host.close ; />; 
 msg + ='< br />< input type =buttonvalue =执行剪切和粘贴onclick =google.script.run.cutnpasteFromToSheets（）; google.script.host。关（）; />; 
 msg + ='< br />< input type =buttonvalue =保持两个范围已定义onclick =google.script.host.close（）; />; 
 msg + ='< br />< input type =buttonvalue =清除范围并重新开始onclick =google.script.run.clearCopyProperties（）; google.script.host。关（）; />; 
 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 ='目标范围调整'; 
 
 msg ='源范围和目标范围维度不匹配。所以假设目的地范围的左上角是正确的'; 
 msg + ='并且图纸选择是正确的。目标范围被修改为具有与源范围相同的尺寸。 ; 
 msg + ='< br />来源表格/范围：'+ srcShtNameStr +'/'+ srcShtRangeStr +'< br />目的地表/范围：'+ desShtNameStr +'/'+ newdesShtRange .getA1Notation（）+'< br />'; 
 msg + ='< br />< input type =buttonvalue =执行复制onclick =google.script.run.copyFromToSheets（）; google.script.host.close ; />; 
 msg + ='< br />< input type =buttonvalue =执行剪切和粘贴onclick =google.script.run.cutnpasteFromToSheets（）; google.script.host。关（）; />; 
 msg + ='< br />< input type =buttonvalue =保持两个范围已定义onclick =google.script.host.close（）; />; 
 msg + ='< br />< input type =buttonvalue =清除范围并重新开始onclick =google.script.run.clearCopyProperties（）;; google.script.host .close（）; />'; 
 dispStatus（title，msg）; 
 
} 
 
 
} 
} 
 
 
函数copyFromToSetupHelper（mode）
 {
 var mode =（typeof（mode）！=='undefined'）？mode：0; 
 switch（ （）; 
 break; 
 case 2：
 setCopyDestination（） ; 
 copyFromToSetupUi（）; 
 break; 
 default：
 clearCopyProperties（）; 
} 
} 
  
 
 
 
这是dispStatus对话框。 
 
 
 函数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'？身高：300; 
 var html = typeof（html）！=='undefined'？ html：'< p>没有提供html。< / p>'; 
 var htmlOutput = HtmlService 
 .createHtmlOutput（html）
 .setWidth（width）
 .setHeight（height）; 
 SpreadsheetApp.getUi（）。showModelessDialog（htmlOutput，title）; 
} 
  
 
I have a apps script UI modal dialog with textbox and pick button. When I press pick button and select the range of cells in sheets, then it has to copy sheet range address location identified by sheet(0) or sheet(1). Any thoughts? 
解决方案
Cut or Copy and Paste


This is a cut and paste or copy and paste that use a modeless dialog so that you can make the selection even when the dialog is active.
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 + '<br />';
  msg += 'Source Sheet Range = ' + srcShtRangeStr + '<br />';
  msg += 'Destination Sheet Range = ' + desShtNameStr + '<br />';
  msg += 'Destination Sheet Range = ' + desShtRangeStr + '<br />';
  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>\
    <br /><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 += '<br /><input type="button" value="Destination Range Selected" onclick="google.script.run.copyFromToSetupHelper(2);google.script.host.close();" />';
      msg += '<br />Input Range: ' + srcShtRangeStr + '<br /><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 = '<br />Source Sheet/Range: ' + srcShtNameStr + '/' + srcShtRangeStr + '<br />Destination Sheet/Range: ' + desShtNameStr + '/' + desShtRangeStr + '<br />';
      msg += '<br /><input type="button" value="Perform Copy" onclick="google.script.run.copyFromToSheets();google.script.host.close();" />';
      msg += '<br /><input type="button" value="Perform Cut & Paste" onclick="google.script.run.cutnpasteFromToSheets();google.script.host.close();" />';
      msg += '<br /><input type="button" value="Keep both Ranges Defined" onclick="google.script.host.close();" />';
      msg += '<br /><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 += '<br />Source Sheet/Range: ' + srcShtNameStr + '/' + srcShtRangeStr + '<br />Destination Sheet/Range: ' + desShtNameStr + '/' + newdesShtRange.getA1Notation() + '<br />';
      msg += '<br /><input type="button" value="Perform Copy" onclick="google.script.run.copyFromToSheets();google.script.host.close();" />';
      msg += '<br /><input type="button" value="Perform Cut & Paste" onclick="google.script.run.cutnpasteFromToSheets();google.script.host.close();" />';
      msg += '<br /><input type="button" value="Keep both Ranges Defined" onclick="google.script.host.close();" />';
      msg += '<br /><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();
  }
}
Here's the the dispStatus dialog.
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);
} 


                        
这篇关于使用GAS实时检索电子表格中选定的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！