CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsimportrange
Alex Pilugin
Alex Pilugin

Reputation: 703

ImportRange: Error: Cannot find range or sheet for imported range

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

Upvotes: 0

Views: 6550

Answers (1)

user6655984
user6655984

Reputation:

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.

Upvotes: 1

Related Questions