Why is my copy & paste column function not working in google scripts

Question

Sample sheet I have a script to gather some data and format it. Eventually I want to copy column H & paste into F.

I recorded this step as a macro and as a standalone function, it works fine doing the copying.

But when I added this to the rest of the script, just that part will not run. The script stops just before the copy/paste. I don't understand what is wrong.

Individually this is the copy paste function:

function Copypaste() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getSheetByName('RESULT').activate();
  spreadsheet.getRange('G1').activate();
  spreadsheet.getActiveSheet().setFrozenRows(0);
  spreadsheet.getRange('F1').activate();
  spreadsheet.getRange('H:H').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('F1').activate();

This is the entire script with this copy paste code at the end:

const sS = SpreadsheetApp.getActiveSpreadsheet()
function grabData() {
  const sheetIn = sS.getSheetByName('DATA')
  const sheetOut = sS.getSheetByName('RESULT')
  const range = 'A1:B'
  /* Grab all the data from columns A and B and filter it */
  const values = sheetIn.getRange(range).getValues().filter(n => n[0])
  /* Retrieve only the names if it containes 250p */
  /* In format [[a], [b], ...] */
  const parsedValues = values.map((arr) => {
    const [type, name] = arr
    if (type.toLowerCase().includes('250g')) {
      return name.split('\n')
    }
  })
    .filter(n => n)
    .flat()
    .map(n => [n])
  /* Add the values to the Desired Outcome Sheet */
  sheetOut
    .getRange(sheetOut.getLastRow() + 1, 1, parsedValues.length)
    .setValues(parsedValues)

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "RESULT";
  var sheet = ss.getSheetByName(sheetname);
  var LR = sheet.getLastRow();
  var meals = sheet.getRange(1, 1, LR);
  //Logger.log("DEBUG: range: "+range.getA1Notation());
  var v = meals.getValues();

  var startrow = 1;
  var outputrange = sheet.getRange(startrow, 1, LR - startrow, 1);
  var output = []


  for (var i = startrow - 1; i < LR; i++) {
    var outputrow = []; output
    var Unit = v[i][0].substring(0, 1);
    var Name = v[i][0].substring(2);
    // Logger.log("DEBUG: Unit:"+Unit+,Name: "+Unit+");
    outputrow.push(Unit);
    outputrow.push(Name);
    output.push(outputrow);
    String
  }
  var outputrange = sheet.getRange(startrow, 1, LR - startrow + 1, 2);
  outputrange.setValues(output);

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Result";
  var sheet = ss.getSheetByName(sheetname);

  var lastRow = sheet.getLastRow();
  var rangeb = sheet.getRange("B1:B" + lastRow);
  rangeb.trimWhitespace();
}
{
  var spreadsheet = SpreadsheetApp.getActive();
  var sh = spreadsheet.getSheetByName('RESULT').activate();
  sh.getRange('G1').activate();
  sh.getCurrentCell().setFormula('=QUERY(A:B,"Select B, Sum(A) group by B")');

{  
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getSheetByName('RESULT').activate();
  spreadsheet.getRange('G1').activate();
  spreadsheet.getActiveSheet().setFrozenRows(0);
  spreadsheet.getRange('F1').activate();
  spreadsheet.getRange('H:H').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('F1').activate();
};
};

logger.log()

Please provide this Logger.log(): const sS = SpreadsheetApp.getActiveSpreadsheet() function grabData() { const ish = sS.getSheetByName('DATA') const osh = sS.getSheetByName('RESULT') const range = 'A1:B' const vs = ish.getRange('A1:B' + ish.getLastRow()).getValues().filter(n => n[0]) const parsedValues = vs.map((r) => { const [type, name] = r if (type.toLowerCase().includes('250g')) { return name.split('\n') } }) .filter(n => n) .flat() .map(n => [n]) Logger.log(parsedValues); — Cooper
Sorry I don't understand what you mean. I am quite new to scripting. :( — Genny
I want to see the Logger.log() of parsedValues. Please post it into your question — Cooper

Daniel Daniel · Accepted Answer · 2022-06-09T01:05:21

It's hard to tell without a sample spreadsheet to test, but TypeError: v[i][0].substring is not a function means that you're calling substring() on something that is not a string. You can log the value of v[i][0] at that point to know for sure.

Maybe the cell has a numeric format or something else. According to the Apps Script getValues() documentation: "The values may be of type Number, Boolean, Date, or String, depending on the value of the cell.".

Since the values in v seem to come only from a single column, a quick and dirty fix could be to just convert all the elements to strings, and then call substring on them. Something like this:

  var v = meals.getValues();
  vstring = v.map(function(v){return v.toString()})

The array vstring is now just a one-dimensional array with the values so you'll have to change that in your for loop as well:

  for (var i = startrow - 1; i < LR; i++) {
    var outputrow = []; output
    var Unit = vstring[i].substring(0, 1);
    var Name = vstring[i].substring(2);
    //etc

Why is my copy & paste column function not working in google scripts

1 Answers