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.

CodePudding user response：

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":

Sheet "Target":

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);
  }
}

CodePudding user response：

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 ;).