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!
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
importrangeformulas; 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.
