Real-time retrieval of selected range in spreadsheet using GAS

Question

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?

Cooper Cooper · Accepted Answer · 2017-03-29T05:15:55

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);
}

Real-time retrieval of selected range in spreadsheet using GAS

1 Answers

Cut or Copy and Paste