How can I extract data from multiple tabs into one google sheet via google script app
All,
Before I explain the problem, I would like to provide some information on what I am working with.
Tab 1 can have columns: date, state, actual / forecast, value
Tab 2 can have columns: Date, Actual / Forecast, Value
Tab 3 will be: Date, Actual / Forecast, Value
I need to output the date, actual / forecast columns and values from tabs 1 and 2 and populate tab 3 in the correct table format without any blank lines / breaks for column A. I would ideally like to do this using a click on tab 3. How should I do it? I found that I can copy data one sheet at a time via:
function MoveData() {
var ss =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1");
var ss3 =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet3");
ss.getRange("A2:A").copyTo(ss3.getRange("A2"), {contentsOnly:true});
ss.getRange("C2:C").copyTo(ss3.getRange("B2"), {contentsOnly:true});
ss.getrange("D2:D").copyTo(ss3.getrange("C2"), {contentsOnly:true});
}
The next step is to fill in the data from the second sheet right below what is pulled out with this function.
Any help is greatly appreciated.
+3
source to share
1 answer
I think this will do what you described.
function MoveData() {
var ss=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1");
var ss2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet2");
var ss3=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet3");
ss.getRange("A2:A").copyTo(ss3.getRange("A2"), {contentsOnly:true});
ss.getRange("C2:C").copyTo(ss3.getRange("B2"), {contentsOnly:true});
ss.getrange("D2:D").copyTo(ss3.getRange("C2"), {contentsOnly:true});
var nextRow=ss3.getLastRow()+1;//I assume that the above results in
same size columns.
ss2.getRange("A2:A").copyTo(ss3.getRange(nextRow,1),
{contentsOnly:true});
ss2.getRange("B2:B").copyTo(ss3.getRange(nextRow,2),
{contentsOnly:true});
ss2.getrange("C2:C").copyTo(ss3.getRange(nextRow,3),
{contentsOnly:true});
}
+2
source to share