App Script to Crop Sheet to Data Range

[Dan Klinker]

Hi all, I'm in need of some help. I have created a script that uses a series of importranges to extract data from a number of Google Sheets into a centralised file which is working fine. The imported data includes a lot of white space and I would like the imported data to be trimmed after extraction, so that the Sheet is cropped to the data range. I've seen the online Google article that details adding this as an Extension, but ideally I would like this function to be added to my existing App Script and performed after each run of it, otherwise it'll be a very manual process of trimming each tab. Many thanks for your help. 

[Keith Andersen]

Whitespace? 

In a cell?

Empty columns and rows?

On Wed, Jun 14, 2023, 6:17 AM Dan Klinker <daniel....@bmigroup.com> wrote:

Hi all, I'm in need of some help. I have created a script that uses a series of importranges to extract data from a number of Google Sheets into a centralised file which is working fine. The imported data includes a lot of white space and I would like the imported data to be trimmed after extraction, so that the Sheet is cropped to the data range. I've seen the online Google article that details adding this as an Extension, but ideally I would like this function to be added to my existing App Script and performed after each run of it, otherwise it'll be a very manual process of trimming each tab. Many thanks for your help. 

Registered Office: BMI Group Holdings UK Limited, Thames Tower Reading RG1 1LX.

Company number: 09984607

VAT number. GB294008404 

This email, including any attachments, is for the sole use of the intended recipient(s), and may contain information that is confidential or legally protected. If you are not the intended recipient, any disclosure, copying, distribution, or use of the contents of this information or any attachments is prohibited and may be unlawful. If you have received this electronic transmission in error, please reply immediately to the sender by return e-mail that you have received the message in error and delete it along with any attachments. Please note that the Internet is not a safe means of communication or form of media.

Whilst we have taken reasonable precautions to ensure that this e-mail and any attachment has been checked for viruses, we cannot guarantee that they are virus free and we cannot accept liability for any damage sustained as a result of software viruses. We would advise that you carry out your own virus checks, especially before opening an attachment.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7c5cb291-fcdc-42b4-8d83-aa5ca1ff3efen%40googlegroups.com.

[Dan Klinker]

Hi Keith,

I would like to remove all empty rows under the completed dataset. I've managed to write a working script, but when adding it into the extraction script it doesn't work. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4ypkGjvOWuc3nSJUMn8cjT01t05qpVa76NsnRg4w2Q2Yw%40mail.gmail.com.

--

 

Dan Klinker

Finance Systems and Digitalisation Manager

T: +447702947674 
M: +447702947674  
E: daniel....@bmigroup.com
W: bmigroup.com

  

        

[Keith Andersen]

Without seeing your script it's impossible to say why it isn't working.

Can you keep the extraction script separate from the delete rows script and call it from a button or special menu?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAMAoYMwh%3DncLied0eUdVOF6HomBpMURNnL_xyRGqwdgZD0KUjg%40mail.gmail.com.

[Dan Klinker]

This is my extraction script:

function myFunction() {

importRange(
//Central
"1-G0zxki4ggyO11GThssnB1pd91tcAvTRqZEGttc9NdY",
"Consolidated 5+7!A1:G",
"1Bfr8_kqQ2hxs3BeuEl8hh7NB9_RZigszPVxAtBIT1Jc",
"Central!A1"
);

};

function importRange(sourceID, sourceRange, destinationID, destinationRangeStart){
 
  // Gather Source Range Values
  const sourceSS = SpreadsheetApp.openById(sourceID);
  const sourceRng = sourceSS.getRange(sourceRange);
  const sourceVals = sourceRng.getValues();

  // Get Destiation Sheet and Cell Location
  const destinationSS = SpreadsheetApp.openById(destinationID);
  const destStartRange = destinationSS.getRange(destinationRangeStart);
  const destSheet = destStartRange.getSheet();
 
  // Clear Previous Data
  destSheet.clear();
 
  // Get the full data range to paste from start range.
  const destRange = destSheet.getRange(
      destStartRange.getRow(),//Start Row
      destStartRange.getColumn(),//Start Column
      sourceVals.length,//Row Depth
      sourceVals[0].length //Column Width
    );
 
  // Paste in the Values
  destRange.setValues(sourceVals);

  SpreadsheetApp.flush();

setTimeStamp()

}

function setTimeStamp() {

SpreadsheetApp.getActive().getSheetByName("Overview")
.getRange("D8").setValue(new Date())

 SpreadsheetApp.flush();

}

And this is the row deletion script:

function removeEmptyRows(){

  var sh = SpreadsheetApp.getActive().getSheetByName("Central");

  var maxRows = sh.getMaxRows();

  var lastRow = sh.getLastRow();

  sh.deleteRows(lastRow+1, maxRows-lastRow);

};

Ideally I would like to have these functions joined, but if that's not possible I can keep them apart

[Keith Andersen]

For now it might be more prudent to keep the functions separate.

I will be off vacation and in front of my computer Monday.

I will reach back out at that time and if you wish to try and combine them, I will work with you on that.

Cheers,

Keith

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAMAoYMz2tjuJ3xDdoQcwEzf2rDWzND4XWQ8xz1d%3DPELsQ3oGAw%40mail.gmail.com.