How to import data from another Google Sheet using Google Script without using Spreadsheet ID (July 2020 update stopped my code working)

Question

I previous asked on Stack Overflow how I could import data from another Google Sheet using Google Script without using Spreadsheet ID. Previous question and the answers, to which was answered and was working fine. Now it seems that Google has updated something which stops it from working and as such is spamming my inbox with failed to complete error emails.

I am now getting the following error:

Exception: Cannot retrieve the next object: iterator has reached the end. (line 2, file "Data Import")

'Data Import' is the name of my script file.

Not only that, when I try to run other scripts on the same sheet, unless I completely delete this script the others fail to run too.

Here's the code that was running perfectly but has now stopped.

var folder = DriveApp.getFoldersByName("SOURCE FOLDER NAME").next();
var file = folder.getFilesByName("SOURCE FILE NAME").next();
var sourceSpreadsheetID = file.getId();
var sourceWorksheetName = "SOURCE WORKSHEET NAME";
var targetSpreadsheetID = "TARGET FILE ID";
var targetWorksheetName = "TARGET WORKSHEET NAME";

function importData1() {
    var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
    var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
    //var thisData = thisWorksheet.getDataRange();
    var thisData = thisSpreadsheet.getRangeByName("A:Q");

    var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
    var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
    var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())
    toRange.setValues(thisData.getValues()); 
}

var folder = DriveApp.getFolderById(id) where id is the id of your folder. Can you try this out , instead of your first line ? ID of the folder can be found in the URL of the folder in your Google drive. — soMario
@MariosKaramanis Didn't work I'm afraid. Worth noting that the google sheet gets replaced by a script of the same name (by a third party app) every single day so I can't simply use the get sheet ID method. The folder stays the same however. — Adam Newman
Also in the first part of your code you don't do any kind of error checking, maybe the folder that you are looking for does not exist of the file is not created in that folder. Also you may have multiple folders but I don't see you iterating using the next method. Are you expecting to get this on the first file? Checked that your file is indeed there to be found? — Raserhin

Rubén Rubén · Accepted Answer · 2020-07-30T15:13:57

IMHO it's not a good idea to use Google Apps Script calls in the global scope. As you are already facing, the problems with the current calls in your script make other functions to not work.

First thing that you have to do is to move those lines from the global scope to another place. You might put them inside a try...catch, on it's own function or inside of importData1() function declaration.

Regarding the specific error about line 2:

var file = folder.getFilesByName("SOURCE FILE NAME").next();

it means that the code can't find a file named SOURCE FILE NAME inside the folder. Maybe the file name was changed or the effective user doesn't have access to file. If running the code in incognito mode works, then the problem is that the user running the code has being signed-in in multiple Google accounts.

How to import data from another Google Sheet using Google Script without using Spreadsheet ID (July 2020 update stopped my code working)

1 Answers