Google Spreadsheet. I dynamically remove old sheet and I create (I use sheet.copyTo() function) a new sheet with the same name, but IMPORTRANGE formula can't find (periodically) new Sheets.

=ImportRange("ssId", "Data!A1:B1")

This script works fine but other Spreadsheets which fetch data from this Spreadsheet by IMPORTRANGE formula periodically can't find new (generated) sheets:

=QUERY(IFERROR(IMPORTRANGE("ssId", "Data!A:AU")),"SELECT * ", 0)

This formula doesn't return anything!

function copySpreadSheet(sourceId, targetId) { try { var sourceSS = SpreadsheetApp.openById(sourceId); SpreadsheetApp.setActiveSpreadsheet(sourceSS); var sourceSheets = sourceSS.getSheets(); var targetSS = SpreadsheetApp.openById(targetId); SpreadsheetApp.setActiveSpreadsheet(targetSS); var targetSheets = targetSS.getSheets(); //you can't delete all sheets! var timestamp = new Date().getTime(); var tempName = "TEMPORARY-FOR-DELETE-" + timestamp; targetSS.insertSheet(tempName); for(var i = 0; i < targetSheets.length; i++) { targetSS.deleteSheet(targetSheets[i]); } var sourceCharts = []; var newSheetCharts = []; var newSheet = null; var newSheetName = ""; for(var i = 0; i < sourceSheets.length; i++) { newSheet = sourceSheets[i].copyTo(targetSS) newSheetName = newSheet.getName().replace("Copy of ", ""); newSheet.setName(newSheetName); /* newSheetCharts = newSheet.getCharts(); sourceCharts = sourceSheets[i].getCharts(); for(var j = 0; j < sourceCharts.length; j++) { /* Server Error: var chart = newSheetCharts[j].modify() .setChartType(sourceCharts[j].getType()) .addRange(sourceCharts[j].getRange()) .build(); newSheet.updateChart(chart); */ //insertImage(blob, column, row, offsetX, offsetY) /* newSheet.insertImage( sourceCharts[j].getBlob(), sourceCharts[j].getContainerInfo().getAnchorColumn(), sourceCharts[j].getContainerInfo().getAnchorRow(), sourceCharts[j].getContainerInfo().getOffsetX(), sourceCharts[j].getContainerInfo().getOffsetY() ); */ /* } */ } targetSS.deleteSheet(targetSS.getSheetByName(tempName)); //remove a temporary sheet //Adding Date Stamp: targetSS.insertSheet("Last time updated").hideSheet().getRange(1, 1).setValue(new Date()); SpreadsheetApp.flush(); Utilities.sleep(500); return targetSS; } catch (err) { Logger.log(err.toString()); } }

I tried to "cheat" the Spreadsheet:

var querySheet = dataSourceSS.getSheetByName("Query"); querySheet.activate(); Logger.log(formula); querySheet.getRange(2, 1).setFormula("=Minute(Now())"); //any FORMULA Utilities.sleep(1000); querySheet.getRange(2, 1).setFormula(formula);

It still doesn't work! enter image description here

1 Answer

Creating a new sheet with the same name is no guarantee that it will be treated as a replacement for the old sheet in all circumstances. Sheets have ID numbers that are used to identify them independently of names. If the importrange has been linked to a particular sheet, and that sheet is deleted, there is no promise that it will automatically re-link to another sheet that has the same name. Possible solutions:

  • Delete and re-enter the importrange formulas; this can also be done with a script if it has access to those spreadsheets
  • (Preferable): do not delete and re-created sheets. Clear the existing sheet with and copy new values to it.

Example:

targetSheets[i].clear() var values = sourceSheets[i].getDataRange().getValues(); targetSheets[i].getRange(1, 1, values.length, values[0].length).setValues(values);

This will not copy formatting or formulas; the effect is essentially same as copying and pasting values only. But importrange gets only the values, so it makes no difference to it.

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoa3BwYm6BdIWOoqSpp6Kpv6K6xp5knqqipL9ur8CnpaisXZu2r7CMq5inn5VivLN50qGcnqxdm7yzecimp6iqpJqxbr7Ap56e