Google App Script - Custom Importrange Function, Need Auto-Update

Question

I am porting data from 9 different Google Sheets to a master overview dashboard to track company metrics. Instead of using ImportRange, I created a custom function with Google Apps Script because I need all the formatting to come with it. The sheet that I want to copy over uses formulas, so I first create a copy of the sheet in the source spreadsheet without formulas and then copy that sheet over to the destination spreadsheet.

The nice thing about ImportRange is that it is constantly fetching the data and auto-updating. The function I wrote below creates and deletes sheets to get the formatting to port over to the masterSS. Is there a work around that anyone can think of to just get the values to auto-update, without having to create and delete a bunch of new sheets?

Also, everytime I create and delete a new sheet, the sheet count increases by 1. Is there a way to reset this counter?

function importPortComp(portComp, srcRange, destRange) {

  // Create temporary sheet in source spreadsheet
  var tempSheet = portComp.insertSheet();

  // Copy values, formatting, and column widths to temporary sheet
  srcRange.copyTo(tempSheet.getRange(1, 1), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  srcRange.copyTo(tempSheet.getRange(1, 1), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  srcRange.copyTo(tempSheet.getRange(1, 1), SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS, false);

  // Copy temporary sheet destination spreadsheet
  var copiedSheet = tempSheet.copyTo(masterSS);

  // Delete temporary sheet from source spreadsheet
  portComp.deleteSheet(tempSheet);

  // Copy source range to destination range
  copiedSheet.getRange(srcRange.getA1Notation()).copyTo(destRange);
  copiedSheet.getRange(srcRange.getA1Notation()).copyTo(destRange, SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS, false);

  // Delete copied sheet from T/R spreadsheet
  masterSS.deleteSheet(copiedSheet);
}

In order to understand the detail situation you want to do, can you provide the sample source and destination spreadsheet you want? — Tanaike

JSmith JSmith · Accepted Answer · 2018-08-20T16:13:44

if I correctly understand your question: use:

array = sheet.getSheetValues(startRow, startColumn, numRows, numColumns)

to get the values then push to the array at the correct index (I guess it's array[row][column])the other datas you want to "concat/add" set the correct range and the use

sheet.range.setValues(array)

you can check this documentation

Google App Script - Custom Importrange Function, Need Auto-Update

1 Answers