How to Update a workbook sheet data in google sheets from other different workbook sheet(s)?

Question

In google drive, I have two different workbooks/files, let's call them this: Workbook (A) Workbook (B)

Workbook (B) has 3 different sheets included (Sheet 1, Sheet 2, Sheet 3) with cells "A1", "B1", "C1" occupied with some numerical, date or string values.

Workbook (A) has only 1 sheet (Sheet 1) and blank columns "A", "B", "C".

I want to set an automatic and/or global function/script/hyperlink between WB(B) and WB(A), in which it transfers values of cells "A1", "B1", "C1" of WB(B) from Sheet 1, Sheet 2, Sheet 3, to WB(A) in columns "A", "B", "C" accordingly. each in its own new row as a set.

and whenever someone adds a new sheet in WB(B), i.e. Sheet 4, the rule will apply on it automatically without manually setting it.

I already tried the importrange() function, but it needs to be set manually every time!. I know programming and sometimes I write my own codes, I tried writing a script for it but I couldn't reach what I want (lack of experience) and I am so in dire need of help because it's a heavy task at work and we are trying to apply a new work methodology, would you please help me with the code?

Possible duplicate of [copy data from one sheet to another in google app script and append a row, one small issue](https://stackoverflow.com/questions/44967086/copy-data-from-one-sheet-to-another-in-google-app-script-and-append-a-row-one-s) — Sourabh Choraria, Sep 27 '19 at 05:05

score 0 · Accepted Answer · answered Sep 26 '19 at 11:16

0

Copy and run this function to a script bound to Workbook B:

function copyData() {
  var workbookB = SpreadsheetApp.getActiveSpreadsheet();
  var sheetsB = workbookB.getSheets();
  var sheetA = SpreadsheetApp.openById('workbook_A_id').getSheetByName('Sheet1');
  sheetA.clear(); // This deletes all data in WBA before copying everything from WBB, use this only if you don't want all data to be duplicated all the time.
  for(var index = 0; index < sheetsB.length; index++) {
    var lastRow = sheetA.getLastRow() + 1;
    var valuesB = sheetsB[index].getRange(1, 1, 1, 3).getValues();
    var rangeA = sheetA.getRange(lastRow, 1, 1, 3);
    rangeA.setValues(valuesB);
  }
}

answered Sep 26 '19 at 11:16

Iamblichus

18,540
2
11
27

first of all, thank you so much for replying!!!! I am speechless, dude.. now, I have a question after tracing and trying the code, this only applies on 1 cell value,,, but what if I want multiple cells values at the same time? i.e. (from source sheet) C8, J9, L34, L60, L89... and return their values (in destination sheet) accordingly in A1 (C8 goes here), B1 (J9 goes here), C1 (L34 goes here) and so on, every set of grouped results in its own row. thanks again man, much appreciated <3 – Samy Sep 26 '19 at 17:07
never mind ^_^, I found a lazy work around I used to do in college :D, I defined all the cells I want in different variables and put it's correct index in the getrange() function, and it returned it all amazingly in the other sheet, thank you so much man, you made my day!!!:))) if there's a more efficient way to handle this dynamically, let me know please! – Samy Sep 26 '19 at 17:37
@Samy I'm glad my answer helped you. Now, for documentation purposes, if my answer was useful to you and did what you described you wanted to do in the original post, please mark this question as solved it and create another one describing exactly what you want to do now. Thank you – Iamblichus Sep 27 '19 at 14:26

How to Update a workbook sheet data in google sheets from other different workbook sheet(s)?

1 Answers1