CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Smith O.
Smith O.

Reputation: 217

How to compare two sheets and delete/add any column with a distinct value in row 1? Google Script

I want to compare two sheets (based on header values in row 1) and delete any column with a unique value (without a match). For example, Assuming Sheet1, Row 1 data and Sheet 2, Row 1 are uniform, if a user adds/deletes a column within any sheet, I want to always match the number of columns in both sheets with their values

Screenshots of sheets headings.

IF both sheets looks like this enter image description here

And a user adds a new Column N enter image description here

Or delete column N

enter image description here

How can I ensure that both sheet matches by deleting the odd/distinct column in Sheet 1?

I have tried modifying this code below but I can't just get the unique one out. This code only look for headers with a defined value.

function deleteAloneColumns(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastColumnPos = sheet.getLastColumn();
  var headers = sheet.getRange( 1 ,1, 1, lastColumnPos ).getValues()[0];
  for( var i = lastColumnPos ; i < 1; i--){
    if( headers[i] === "alone" ) sheet.deleteColumn(i);
  }
 SpreadsheetApp.getUi().alert( 'Job done!' );
}

Any help to compare and delete the column with the unique value will be appreciated.

Upvotes: 0

Views: 332

Answers (1)

0Valt
0Valt

Reputation: 10365

Problem

Balancing sheets based on header row values mismatch.

Solution

If I understood you correctly, you have a source sheet against which validation is run and two primary use cases: user adds a new column named differently than any other column (if you want to check that the column strictly matches the one in sheet1, it is easy to modify) in source sheet or deletes one that should be there.

const balanceSheets = (sourceShName = 'Sheet1',targetShName = 'Sheet2') => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const s1 = ss.getSheetByName(sourceShName);
  const s2 = ss.getSheetByName(targetShName);

  const s2lcol = s2.getLastColumn();

  //keep all vals from source to reduce I/O
  const s1DataVals = s1.getDataRange().getValues();

  const s2Vals = s2.getRange(1, 1, 1, s2lcol).getValues();

  const h1Vals = s1DataVals[0];
  const h2Vals = s2Vals[0];

  //assume s1 is source (validation) sheet
  //assume s2 is target sheet that a user can edit

  //case 1: target has value not present in source -> delete column in target
  let colIdx = 0;
  h2Vals.forEach(value => {
    const isOK = h1Vals.some(val => val===value);

    isOK ? colIdx++ : s2.deleteColumn(colIdx+1);
  });

  //case 2: target does not have values present in source -> append column from source
  h1Vals.forEach((value,index) => {
    const isOK = h2Vals.some(val => val===value);
    !isOK && s2.insertColumnAfter(index);

    const valuesToInsert = s1DataVals.map(row => [row[index]]);

    const numRowsToInsert = valuesToInsert.length;

    s2.getRange(1,index+1, numRowsToInsert,1).setValues(valuesToInsert);
  });

};

Showcase

Here is a small demo of how it works as a macros:

Balancer demo

Notes

  1. Solving your problem with two forEach is suboptimal, but I kept number of I/O low (it can be lowered further by, for example, moving deleteColum out of the loop while only keeping track of column indices).
  2. The script uses ES6 capabilities provided by V8, so please, be careful (although I would recommend migrating as soon as possible - even if you encounter bugs / inconsistencies , it is worth more than it costs.
  3. UPD made script more flexible by moving sheet names to parameter list.
  4. UPD2 after discussing the issue with deleteColumn() behaviour, the answer is updated to keep column pointer in bounds (for those curious about it - forEach kept incrementing the index, while deleteColumn reduced bounds for any given index).

Reference

  1. insertColumnAfter() method reference

Upvotes: 0

Related Questions