Google Sheets, rearrange data to new rows

Question

Second (and hopefully last) google sheets question today and forever. As I am importing my data from a management system to another via google sheets, I am having some difficulty in following the new system's method of importing data. So currently my sheet looks like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	First Name	Email Address	Phone	Email ID (Email IDs)	Number (Contact Numbers)	Is Primary (Email IDs)	Is Primary Phone (Contact Numbers)	Is Primary Mobile (Contact Numbers)	Mobile No	Company Name	Link Document Type (Links)	Link Name (Links)	Link Title (Links)
2	FERTICLINIC HOME CARE CENTRE L.L.C .	FERTICLINIC HOME CARE CENTRE L.L.C .	[email protected]	971 2 1234567	[email protected]		1			971 50 12345678	1 FERTICLINIC HOME CARE CENTRE L.L.C .	Customer	1 FERTICLINIC HOME CARE CENTRE L.L.C .	1 FERTICLINIC HOME CARE CENTRE L.L.C .

What I want to do is have numbers on column D and J to show at F (second number would be on a new row) So the end result would look like this

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	First Name	Email Address	Phone	Email ID (Email IDs)	Number (Contact Numbers)	Is Primary (Email IDs)	Is Primary Phone (Contact Numbers)	Is Primary Mobile (Contact Numbers)	Mobile No	Company Name	Link Document Type (Links)	Link Name (Links)	Link Title (Links)
2	FERTICLINIC HOME CARE CENTRE L.L.C .	FERTICLINIC HOME CARE CENTRE L.L.C .	[email protected]	971 2 1234567	[email protected]	971 50 12345678 (From J)	1			971 50 12345678	1 FERTICLINIC HOME CARE CENTRE L.L.C .	Customer	1 FERTICLINIC HOME CARE CENTRE L.L.C .	1 FERTICLINIC HOME CARE CENTRE L.L.C .
3						971 2 1234567 (From D)

So basically the script would copy data from "J" to "F" then make a new row and copy data from "D" to "F" on the new row.

I hope this makes sense, any assistance is appreciated. Thanks!

Answer 1

In your situation, how about the following sample script?

Sample script:

Please set the source and destination sheet names.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ss.getSheetByName("Sheet1"); // Please set the source sheet name.
  var dstSheet = ss.getSheetByName("Sheet2"); // Please set the destination sheet name.

  var [, ...values] = srcSheet.getDataRange().getValues();
  var res = values.flatMap(r => {
    r[5] = r[9];
    var temp = Array(r.length).fill(null);
    temp[5] = r[3];
    return [r, temp];
  });
  dstSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

• When this script is run, each row is put as 2 rows. The value of column "J" is put to the column "F". And, the value of column "D" is put to the column "F".

Reference:

• flatMap()

Answer 2

Remapping

function remap() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const [hA,...vs] = sh.getDataRange().getValues();
  let vo = [];
  vs.forEach(([a,b,c,d,e,f,g,h,i,j,k,l]) => {
    let o = [];
    if(d) {
      vo.push([a,b,c,d,e,d,g,h,i,j,k,l]);
    }
    if(j) {
      vo.push(['','','','','',j,'','','','','','']);
    }
  });
  Logger.log(JSON.stringify(vo))
  ss.getSheetByName("Sheet1").getRange(1,1,vo.length,vo[0].length).setValues(vo);
}

Answer 3

what do you think about such a solution?

=TRANSPOSE(QUERY(A2:N2;"select J, D"))

paste into column F

you just have to enable iterative calculation in the settings.

File> Settings> Calculation> Iterative Calculation Enable this option and save