Google Appscript insert column in array

Question

I'd like to know how to insert a column into an array. I know the push function to add a row but not how to add a column like that:

A ; 1
B ; 2
C ; 3
D ; 4

To:

A ; A1 ; 1
B ; B2 ; 2
C ; C3 ; 3
D ; D4 ; 4

I guess I will have to loop between each row to add an item between two item but is there a way to do it as a bulk?

Answer 1

Here is a example.

function test() {
  try {
    let a = [["A",1],["B",2],["C",3],["D",4]];
    let b = a.map( row => [row[0],row[0]+row[1],row[1]]);
    console.log(b);
  }
  catch(err) {
    console.log("Error in test: "+err)
  }
}

Execution log

8:11:48 AM  Notice  Execution started
8:11:49 AM  Info    [ [ 'A', 'A1', 1 ],
  [ 'B', 'B2', 2 ],
  [ 'C', 'C3', 3 ],
  [ 'D', 'D4', 4 ] ]
8:11:49 AM  Notice  Execution completed

Reference

• Array.map()
• Arror function =>

Answer 2

You can splice the concatenated values of the left and right columns.

The following Apps Script should work:

Note: The index is the position of the column you will be inserting after.

const __spliceAndMap = (rows, index, mapFn) =>
  rows.map((row) =>
    row.toSpliced(index + 1, 0, mapFn(row[index], row[index + 1])));

const __addColumn = (sheet, index, mapFn) => {
  let range = sheet.getDataRange();

  // Concatenate values and set them in the new column
  const updatedRows = __spliceAndMap(range.getValues(), index, mapFn);

  // Insert a new column after columnIndex
  sheet.insertColumnAfter(index + 1);
  
  // Update the sheet with the new values
  range = sheet.getRange(1, 1, updatedRows.length, updatedRows[0].length);
  range.setValues(updatedRows);
}

function concatenateColumns() {
  // Get the active sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Add a column after index 0 and concatenate the left and right values
  __addColumn(sheet, 0, (left, right) => left + right);
}

Here is a demo of the __spliceAndMap being used:

const __spliceAndMap = (rows, index, mapFn) =>
  rows.map((row) =>
    row.toSpliced(index + 1, 0, mapFn(row[index], row[index + 1])));

const interpolate = (str, delim = '\t', sep = '\n') => {
  const rows = str.trim().split(sep).map(ln => ln.split(delim));
  return __spliceAndMap(rows, 0, (left, right) => left + right)
    .map(row => row.join(delim)).join(sep);
};

// Output:
// A ; A1 ; 1
// B ; B2 ; 2
// C ; C3 ; 3
// D ; D4 ; 4
console.log(interpolate(`
A ; 1
B ; 2
C ; 3
D ; 4
`, ' ; '));

Answer 3

Try this:

function insertcol() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getDataRange().getValues();
  const o = vs.map(([a,b]) => [a,a+b,b]);
  sh.getRange(sh.getLastRow() + 2,1,o.length,o[0].length).setValues(o);
}

Input and Output

	A	B	C
1	A	1
2	B	2
3	C	3
4	D	4
5
6	A	A1	1
7	B	B2	2
8	C	C3	3
9	D	D4	4

function insertcol() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getDataRange().getValues();
  const o = vs.map(([a,b,c,d,e,f,g]) => [a,f+g,b]);
  sh.getRange(sh.getLastRow() + 2,1,o.length,o[0].length).setValues(o);
}

input and output

	A	B	C	D	E	F	G
1	A1	B1	C1	D1	E1	F1	G1
2	A2	B2	C2	D2	E2	F2	G2
3	A3	B3	C3	D3	E3	F3	G3
4	A4	B4	C4	D4	E4	F4	G4
5
6	A1	F1G1	B1
7	A2	F2G2	B2
8	A3	F3G3	B3
9	A4	F4G4	B4