3

How do I get data from one Google spreadsheet A into spreadsheet B so
when Spreadsheet A is modified, then Spreadsheet B also changes automatically?

A formula using QUERY() and IMPORTRANGE() functions was tried, but it didn't return the expected results. For example, I have two spreadsheets Documents DocA and DocB:

    A               B           C
1    uid            serial_no   ph
2   356307042189331 3123958     912345678900
3   356307046077540 3966305     923451245123
4   356307046092465 3966407     978458784578
5   356307046098678 3970932     971548745125

In DocB

    A               B
1   serial_no      uid
2   3123958
3   3284728
4   3284655

I am trying to get A2 from DocA into B2 in DocB with the following formula

=QUERY(
     ImportRange("[key]","Sheet1!A:C"),
     "Select Col1, Col2 where Col1='"&A2&"'",
     1
 )

I get uid serial_no in Cell B2 instead of the value in Cell A2 of DocA.

2

Use the ImportRange function (documentation):

=IMPORTRANGE("spreadsheet key"; "Range")

See these example spreadsheets (A and B), which you may copy to your own Google Drive (File → Copy).

  • 1
    I just need specific cells not the whole range, I have updated the question to clarify this, – Kaippally Dec 23 '15 at 8:29
  • Since the OP updated the question, this answer became obsolete. – Rubén Dec 28 '15 at 19:48
2

Short answer

Remove Col2 from the Select clause and replace Col1 by Col2 in the Where clause.

Details

Instead of

=QUERY(
     ImportRange("[key]","Sheet1!A:C"),
     "Select Col1, Col2 where Col1='"&A2&"'",
     1
 )

use

=QUERY(
     ImportRange("[key]","Sheet1!A:C"),
     "Select Col1 where Col2='"&A2&"'",
     1
 )
0

Depends on the situation. I would set up an apps script file and have it trigger on edit or time based. May need some debugging, but I've used variations of this to do exactly what you said. If you are actually working in the spreadsheet though and need time now changes, I would use the importrange function as the previous respondent said.

    var feederSS = SpreadsheetApp.openById("spreadsheetAId"); //get spreadsheet A
    var feederSheet = feederSS.getSheets()[0];
    var feederRange = feederSheet.getDataRange();
    var feederData = feederRange.getValues();
    /*Then get spreadsheet b*/
    var masterSS = SpreadsheetApp.openById("SpreadsheetBId");
    var masterSheet = masterSS.getSheets()[0];
    var masterRange = masterSheet.getDataRange();
    var masterData = masterRange.getValues();
    /*Now iterate through all of the rows on the feeder sheet and check them against the data on the masterSheet*/
    for (var l = 1; l < feederData.length; l++) {
      var feederRow = feederData[l];
      var feederFirstName = feederRow[0];
      var feederLastName = feederRow[1];
      var feederEmail = feederRow[2];
      var feederCheckRow = [feederFirstName, feederLastName, feederEmail]; 
      var duplicate = false;
      for (var m in masterData) {
        /*This is where spreadsheet A gets compared to B*/
        var masterRow = masterData[m];
        var masterFirst = row[0];
        var masterLast = row[1];
        var masterEmail = row[2];
        var masterCheckRow = [masterFirst, masterLast, masterEmail];

        if (feederCheckRow.join() == masterCheckRow.join()) {
        duplicate = true;
        }
      }
        if(!duplicate) {
        masterSheet.appendRow(feederRow);
      }
}
  • Since the OP updated the question, this answer became obsolete. – Rubén Dec 28 '15 at 19:49
  • That's cool. Should I delete it? – Randy D Dec 28 '15 at 20:59
  • It's opt to you if you deleted it, update it of left it as it is. If you deleted the reputation points that you loose due to the downvotes will turned back to you and to those who downvoted the answer. See webapps.stackexchange.com/help/deleted-answers for further details. – Rubén Dec 28 '15 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.