Google Apps Script - Use Column Name (not In row1) rather than column number

Question

I have an Apps Script linked to my Google Sheet which grabs a range from one sheet and copies it to another. The issue I face now (and one that cannot be reasoned with) is that users will add columns to the destination sheet. What I am trying to do instead is look for the column names; to add another slight spanner in the works the column names are on row 7 instead of 1.

    function OPupdates() {


// I have tried looking at this but couldn't get it to work for me- https://stackoverflow.com/questions/45901162/refer-to-column-name-instead-of-number-in-google-app-script


//Copies Status From One Sheet To Another
  var sheetfrom2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Helper Sheet'); //this contains source info
  var sheetto2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Plan');//sheet into which the new source data is copied

  sheetfrom2.getRange(2, 2, sheetfrom2.getLastRow(), 1).copyTo(sheetto2.getRange(8,8, sheetfrom2.getLastRow()-2, 1), {
    contentsOnly: true
  });
//row 8 column 8.  This is what needs to be changes to reflect the column name regardless of where it sits in row 8


}

I have tried looking at a similar issue/solution (link in the code above) but try as I might I could not figure out how to get this to work for my particular issue.

Robin Gertenbach · Accepted Answer · 2021-09-20 13:36:36Z

2

Instead of copying the range you'll need to create a value array that matches the target structure.

In lieu of sample data I'll play with the following toy example:

Sheet "Source":

A	B	C
1	1	2

Sheet "Target":

_	A	B	C	D
1
2
3
4
5
6
7
8	B	E	C	A

function copyOver() {
  const source = SpreadsheetApp
      .getActiveSpreadsheet()
      .getSheetByName("Source")
      .getDataRange()
      .getValues();

  const target = SpreadsheetApp
      .getActiveSpreadsheet()
      .getSheetByName("Target");
  
  const headerRow = 8;
  const targetHeaders = target.getDataRange().getValues()[headerRow - 1];
  const sourceHeaders = source[0];
  const sourceValues = source.slice(1);
  const columnLookup = targetHeaders.map(h => sourceHeaders.indexOf(h));

  function buildRow(row) {
    return columnLookup.map(c => c == -1 ? "" : row[c]);
  }

  const output = sourceValues.map(buildRow);
  if (output.length > 0) {
    target
        .getRange(headerRow + 1, 1, output.length, output[0].length)
        .setValues(output);
  }
}

answered Sep 20, 2021 at 13:36

Robin Gertenbach

10.9k3 gold badges30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Morris Coyle Over a year ago

Thank you Robin, that worked perfectly. Not sure I fully understand why it worked but I'm going to work on that :-)

Robin Gertenbach Over a year ago

The crux of this is the columnLookup which is an array with length of the number of columns in the target sheet and maps to where the column is found in the source sheet if at all. Try setting a break point at the end and looking at the values of each of the variables.

J. G. · Accepted Answer · 2021-09-20 13:42:06Z

1

First of all try yelling at users and making them understand that they may not mess with sheets with active programming (This is successful 75% of the time). Otherwise, you'll want to check your columns before you copy.

function OPupdates() {         
  var sheetfrom2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Helper Sheet'); //this contains source info
  var sheetto2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Plan');//sheet into which the new source data is copied

var statuscolumn = -1;
var headerWewant = "STATUS";//modify for the correct column name
var targetSheetHeader = sheetto2.getRange(1,1,1,sheetto2.getLastColumn().getValues(); 
for (var j=0; i<targetSheetHeader[0].length;i++)
    {
    if (data[0][j] == headerWeWant) statuscolumn = j+1;
    }
if (statuscolumn == -1) {console.error("Couldn't find the status column"; return;}

  sheetfrom2.getRange(2, 2, sheetfrom2.getLastRow(), 1).copyTo(sheetto2.getRange(8,statuscolumn, sheetfrom2.getLastRow()-2, 1), {
    contentsOnly: true
  });


}

But training the users to go in fear of your wroth is a better long term strategy ;).

answered Sep 20, 2021 at 13:42

J. G.

1,8302 gold badges16 silver badges27 bronze badges

1 Comment

Morris Coyle Over a year ago

Ha! Believe me fear is something I use when protecting my own sheets. Sadly this is not one of mine and as much as I protested (maybe even begged) its a sheet used by people to whom rules are merely a suggestion. Thank you for your solution too btw, much appreciated

Collectives™ on Stack Overflow

Google Apps Script - Use Column Name (not In row1) rather than column number

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related