CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
P.J
P.J

Reputation: 85

Copy filter data from one spreadsheet file to another - Google Apps Script

I'm currently working on a project where I need to copy data to another google sheet. let me explain: I'd created a function where I applied filter by text and it works. But the issue is that I can't copy only the filtered data to another sheet. I'd seen many stack conversations but nothing fit.

here is the code

function copyWithValues() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = spreadSheet.getSheetByName('testo');
  const sourceRange = sourceSheet.getDataRange();
  const sourceValues = sourceRange.getValues(); 
  
  const targetSpreadsheet = titleAsDate();
  
  let rowCount = sourceValues.length;
  let columnCount = sourceValues[0].length;
  
  let targetSheet = targetSpreadsheet.getSheetByName('Sheet1').setName("Report");
  let targetRange = targetSheet.getRange(1, 1, rowCount, columnCount);
  
  targetRange.setValues(sourceValues);
}

Upvotes: 1

Views: 1458

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

The following solution is inspired by this answer.

  • The latter has one limitation though, it only works for transferring the filter data between sheets of the same spreadsheet.

We are going to use the exact same answer to copy the filter values from the source sheet and paste them to a temporary sheet temp_sheet of the source spreadsheet file.

Then we simply get the values of that temporary file and paste them to another spreadsheet file with the regular method of getting and setting the values as you have already implemented in your code.

Solution:

function copyWithValues() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = spreadSheet.getSheetByName('testo');
  const temp_sheet = spreadSheet.insertSheet('temp_sheet');
  const sourceRange = sourceSheet.getFilter().getRange();
  sourceRange.copyTo(
    temp_sheet.getRange('A1'),
    SpreadsheetApp.CopyPasteType.PASTE_NORMAL,
    false);
  SpreadsheetApp.flush();
  
  const sourceValues = temp_sheet.getDataRange().getValues();
  
  const targetSpreadsheet = titleAsDate();
  
  const rowCount = sourceValues.length;
  const columnCount = sourceValues[0].length;
  
  const targetSheet = targetSpreadsheet.getSheetByName('Sheet1').setName("Report");
  const targetRange = targetSheet.getRange(1, 1, rowCount, columnCount);
  targetRange.setValues(sourceValues);
  spreadSheet.deleteSheet(temp_sheet);
}

where the helper function titleAsDate() is defined as:

function titleAsDate() {
  const currentDate = Utilities.formatDate(new Date(), "GMT+8", "dd-MM-yyyy HH:mm:ss");
  return SpreadsheetApp.create("Report of the " + currentDate);
}

based on your previous question.

Upvotes: 3

Related Questions