Why does Google sheet show old data when using IMPORTRANGE?

Question

I have created a simple Google App script linked to a simple google sheet.

The Google sheet has 2 sheets: one sheet named "Source" and the second sheet named "Imported":

https://docs.google.com/spreadsheets/d/1NODMmY8ua-enfjECumm76qszgogE2hyNkhygy8qe1uk/edit#gid=0

The sheet "Imported" imports the data from cell "A1" from the sheet "Source".

However when I run the code, I notice that the code is not able to pick up the imported data even when the new imported data is visible to the human eye in the sheet "Imported".

Steps to replicate the issue:

Step 1: Go to sheet "Source" and enter country name "SPAIN" in cell A1.
Step 2: Run the code.
Step 3: Check the logs. You will notice that even though we assigned "America" in the code, the code still logs the name as "Spain" which was the old data.

If you run the code for the second time, everything works as expected. To replicate the code you will have to start from Step 1 again.

Any pointer to the right direction where I can investigate this?

My code for your quick reference:

function Stack_Overflow_Function() {
    var name = "America";
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source").activate();
    var ss_source = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    ss_source.getRange(1,1).setValue(name);
    Utilities.sleep(1000)

    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Imported").activate();
    var ss_imported = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var lr = ss_imported.getLastRow();

    var current_name = ss_imported.getRange(1,1).getValue();
    Logger.log('The Current name in Imported sheet is '+current_name);


}

Edit: apologies for not mentioning it, the data is imported using IMPORTRANGE in the sheet "Imported" using the standard function IMPORTRANGE('Google sheet URL',Source!A1:A1)

You don't seem to copy any data from Source to Imported? Am I misunderstanding your problem? — Chris
apologies for not mentioning it, the data is imported using IMPORTRANGE in the sheet "Imported" — Stan

Chris Chris · Accepted Answer · 2019-02-19T04:12:09

I've commented the code throughout, but it seems as if you were never copying the value to the 'Imported' sheet. Additionally, It is recommended that you only use .activate() when you want to change what is being displayed to the user, otherwise it should be avoided for just getting Spreadsheet and Sheet objects.

function stackOverflowFunction() {
    var name = "America";
    /* Consider the method I've used below for getting the Spreadsheet and Sheet Object.
       You should only use .activate() when you want to change what the user is looking at.

    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source").activate();
    var ss_source = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    */

    var spread = SpreadsheetApp.getActiveSpreadsheet();
    var ss_source = spread.getSheetByName("Source");
    var sourceRange = ss_source.getRange(1,1); //Get the soure range.

    sourceRange.setValue(name); //Sets the value of the source range.

    Utilities.sleep(1000);

    var ss_imported = spread.getSheetByName("Imported"); //Get the imported sheet
    var importedRange = ss_imported.getRange(1, 1); //Gets the imported sheet range.

    importedRange.setValue(sourceRange.getValue()); //Gets source sheet value and uses it to set the imported sheet value.

    // var lr = ss_imported.getLastRow(); //Not sure what this was for but getLastRow() gets an Integer representing the last row in the sheet.

    //Now lets check the imported range value...
    var current_name = ss_imported.getRange(1,1).getValue();
    Logger.log('The Current name in Imported sheet is '+ current_name);
}

Please let me know if I've misunderstood your question.

Why does Google sheet show old data when using IMPORTRANGE?

1 Answers