I have a script that imports data from different google sheets into one sheet, it works perfectly except the fact that every time when I'm running the script the imported data starts further down in the sheet. What do I need to change in the script that every time i start the script it will push the data starting the first row of the sheet?
function getData() {
get_IDs=["1pkcy4jx14odSN-Ir2Nqai89_0dJc7RiLapWxRWIURj4","1J8_cZsjEa9c8vktPBvqGvcdwUBAVkWT3NSdTnXp6tMk"];
var ss=SpreadsheetApp.getActiveSpreadsheet();
var copySheet =ss.getSheetByName("Sheet3");
copySheet.getRange('A1:Z').clear();
for(z=0;z<get_IDs.length;z++)
{
var ss1=SpreadsheetApp.openById(get_IDs[z]);
var sheets =ss1.getSheetByName("Export Department");
var sheetsRange = sheets.getDataRange();
var sheetsValues = sheetsRange.getValues();
for(var y = 1;y<sheetsValues.length;y++)
{
copySheet.appendRow(sheetsValues[y]);
}
}
}```
Use setValues() instead of appendRow() and increment a counter by the number of rows on each import:
function getData() {
get_IDs = ["1pkcy4jx14odSN-Ir2Nqai89_0dJc7RiLapWxRWIURj4", "1J8_cZsjEa9c8vktPBvqGvcdwUBAVkWT3NSdTnXp6tMk"]
var ss = SpreadsheetApp.getActiveSpreadsheet()
var copySheet = ss.getSheetByName("Sheet3")
copySheet.getRange('A1:Z').clear()
let pasteRow = 1
for (z = 0; z < get_IDs.length; z++) {
var ss1 = SpreadsheetApp.openById(get_IDs[z])
var sheets = ss1.getSheetByName("Export Department")
var sheetsRange = sheets.getDataRange()
var sheetsValues = sheetsRange.getValues()
sheetsValues.splice(0, 1)
copySheet.getRange(pasteRow, 1, sheetsValues.length, sheetsValues[0].length).setValues(sheetsValues)
pasteRow += sheetsValues.length
}
}