Edit 2
function main() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var target_sheet = ss.getSheetByName('Results Overview');
target_sheet.appendRow(get_data_from_Cancellations(ss));
}
function get_data_from_Cancellations(ss) {
var sheet = ss.getSheetByName('Cancellations')
var ranges = [
[0,1], [1,1], [0,2], // B1, B2, C1
[4,3], [5,3], [6,3], // D5..D7
[8,3], [9,3], [10,3], // D9..D11
[12,3], [13,3], [14,3], // D13..D15
[16,3], // D17
[18,4], [19,0,20,0,21,0], // E19, A20
];
var row = make_new_row(sheet, ranges);
return ['', ...row.slice(0,13), ...new Array(19), ...row.slice(13)];
}
Deleted old post, and re-posted here so I can re-word (and hopefully clarify what exactly I'm after)
The script below ‘saves’ the data to another sheet in the specified columns on the next row Available. I would like to modify this code, so that if there is ANY column on a row that has data, it ignores that row & moves to the next one available. Essentially, I want the data on the target sheet to go the next available sheet BUT only if the data can stay within the same row as each other.
In the code below & the example screenshot. – If I execute the script, it will put the ‘advisor name’ (Source B1) into Row 9, but the 'reference number' (source B2) will be put into Row 5. What I would like is the data to go all into row 9. Then the next time all data to go into row 10. So on, and so forth.
edit I want the Range ["B1", "B2", "C1", "D5", "D6", "D7", "D9", "D10", "D11", "D13", "D14", "D15", "D17"] from the sheet "Cancellations", to go into "Results Overview" sheet.
These data ranges should be start to be inputted at the 2nd column, 3rd row. I said in my og post – the range should be inputted at the next available row where there are no cells present in any column in that rar
So the range will all stay within the same row.
B1 “Cancellations”>Column B “Results Overview”.
B2 “Cancellations”>Column C “Results Overview”
C1 “Cancellations”>Column D “Results Overview”
So on & so forth
Test spreadsheet link: - 
function mytestexport() {
var columnIndex = 2; // Column
var columnIndexIncr = 1; //columnIndex increment per each target range
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheets = ["Cancellations"]; //Sheet names
var targetSS = ss.getSheetByName("Results Overview"); //Target sheet
var targetRanges = ["B1", "B2", "C1", "D5", "D6", "D7", "D9", "D10", "D11", "D13", "D14", "D15", "D17"]; //Target ranges
var sourceData;
var a1Range;
var a1Row;
var targetRange;
for(var range of targetRanges) {
for(var i in sourceSheets) {
if(range == "D5") {a1Row = 2} else {a1Row = 2};
sourceData = ss.getSheetByName(sourceSheets[i]).getRange(range).getValue();
a1Range = targetSS.getRange(a1Row, columnIndex);
if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
else targetRange = targetSS.getRange((a1Row 1),columnIndex);
targetRange.setValue(sourceData);
}
columnIndex = columnIndexIncr;
}
CodePudding user response：
As a guess:
function howsitgoing() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheets = ["Cancellations"]; //Sheet names
var targetSS = ss.getSheetByName("Results Overview"); //Target sheet
var targetRanges = ["B1", "B2", "C1", "D5", "D6", "D7", "D9", "D10", "D11", "D13", "D14", "D15", "D17"]; //Target ranges
var new_row = [];
for (var range of targetRanges) {
for (var i in sourceSheets) {
var sourceData = ss.getSheetByName(sourceSheets[i]).getRange(range).getValue();
new_row.push(sourceData);
}
}
targetSS.appendRow(['',...new_row]);
}
It gathers the data from the targetRanges (they are source ranges actually, go figure) and append it as a new row at the end of the target sheet.
But if you mean to add the same way into this new row the same ranges from another sheets ('Queries', 'Unanswered') there can be a trouble. The sheet 'Unanswered' has 9 ranges (d5...d15) but sheets 'Cancellations' and 'Queries' have 10 ranges (d5...d17).
Update
Here is the code that takes ranges from the three sheets and append data from every of the sheets as a new row at end of sheet 'Results Overview':
function main() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var target_sheet = ss.getSheetByName('Results Overview');
target_sheet.appendRow(get_data_from_Cancellations(ss));
target_sheet.appendRow(get_data_from_Unanswered(ss));
target_sheet.appendRow(get_data_from_Queries(ss));
}
function get_data_from_Cancellations(ss) {
var sheet = ss.getSheetByName('Cancellations')
var ranges = [
[0,1], [1,1], [0,2], // B1, B2, C1
[4,3], [5,3], [6,3], // D5..D7
[8,3], [9,3], [10,3], // D9..D11
[12,3], [13,3], [14,3], // D13..D15
[16,3], // D17
[18,4], [19,0], // E19, A20
];
var row = make_new_row(sheet, ranges);
return ['', ...row.slice(0,13), ...new Array(19), ...row.slice(13)];
}
function get_data_from_Unanswered(ss) {
var sheet = ss.getSheetByName('Unanswered')
var ranges = [
[0,1], [1,1], [0,2], // B1, B2, C1
[4,3], [5,3], [6,3], // D5..D7
[8,3], [9,3], [10,3], // D9..D11
[12,3], [13,3], [14,3], // D13..D15
[17,4], [19,0], // E18, A20
];
var row = make_new_row(sheet, ranges);
return ['', ...row.slice(0,3), ...new Array(10),
...row.slice(3,12), ...new Array(10), ...row.slice(12)];
}
function get_data_from_Queries(ss) {
var sheet = ss.getSheetByName('Queries')
var ranges = [
[0,1], [1,1], [0,2], // B1, B2, C1
[4,3], [5,3], [6,3], // D5..D7
[8,3], [9,3], [10,3], // D9..D11
[12,3], [13,3], [14,3], // D13..D15
[16,3], // D17
[18,4], [19,0], // E19, A20
];
var row = make_new_row(sheet, ranges);
return ['', ...row.slice(0,3), ...new Array(19), ...row.slice(3)];
}
function make_new_row(sheet, ranges) {
var data = sheet.getDataRange().getValues();
return ranges.map(([row, col]) => data[row][col]);
}
Update 2
If you want three 'stand-alone' functions (to call them separately from the custom menu, for example), here you go:
function get_data_from_Cancellations() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var target_sheet = ss.getSheetByName('Results Overview');
var data = ss.getSheetByName('Cancellations').getDataRange().getValues();
var row = [
[ 2,1], // B3
[ 0,1], [ 1,1], [ 0,2], // B1, B2, C1
[ 4,3], [ 5,3], [ 6,3], // D5, D6, D7
[ 8,3], [ 9,3], [10,3], // D9, D10, D11
[12,3], [13,3], [14,3], // D13, D14, D15
[16,3], // D17
[18,4], [19,0], // E19, A20
].map(([r,c]) => data[r][c]);
var new_row = [ ...row.slice(0,14), ...new Array(19), ...row.slice(14) ];
target_sheet.appendRow(new_row);
}
function get_data_from_Unanswered() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var target_sheet = ss.getSheetByName('Results Overview');
var data = ss.getSheetByName('Unanswered').getDataRange().getValues();
var row = [
[ 2,1], // B3
[ 0,1], [ 1,1], [ 0,2], // B1, B2, C1
[ 4,3], [ 5,3], [ 6,3], // D5, D6, D7
[ 8,3], [ 9,3], [10,3], // D9, D10, D11
[12,3], [13,3], [14,3], // D13, D14, D15
[17,4], [19,0], // E18, A20
].map(([r,c]) => data[r][c]);
var new_row = [ ...row.slice(0,4), ...new Array(10),
...row.slice(4,13), ...new Array(10), ...row.slice(13) ];
target_sheet.appendRow(new_row);
}
function get_data_from_Queries() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var target_sheet = ss.getSheetByName('Results Overview');
var data = ss.getSheetByName('Queries').getDataRange().getValues();
var row = [
[ 2,1], // B3
[ 0,1], [ 1,1], [ 0,2], // B1, B2, C1
[ 4,3], [ 5,3], [ 6,3], // D5, D6, D7
[ 8,3], [ 9,3], [10,3], // D9, D10, D11
[12,3], [13,3], [14,3], // D13, D14, D15
[16,3], // D17
[18,4], [19,0], // E19, A20
].map(([r,c]) => data[r][c]);
var new_row = [ ...row.slice(0,4), ...new Array(19), ...row.slice(4) ];
target_sheet.appendRow(new_row);
}
You still can run all these three functions at once with a main function like this:
function main() {
get_data_from_Cancellations();
get_data_from_Unanswered();
get_data_from_Queries();
}