Trying to reproduce Excel “LEFT()” function in GoogleScript

Question

I have data in Google Sheets that I need to clean. One column in particular is may contain too many characters and I'd like to take the first 200 characters only if its 201+ long.

I found a way to do it, but it's so nasty that I can see the cells being corrected 1 by 1... visually. I have 100k+ lines to process so my solution is not really an option.

here's the code for now, not working. I'm working on the range.Value = LEFT(range.Value,200); line

function myFunction() {
  var maxRow = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getLastRow();  
  removeOverLenght(4, maxRow);  //COLUMN D index = 4  
}



function removeOverLenght(column, maxRow){  

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var rangeArray = [];

  rangeArray = activeSheet.getRange(1,column,maxRow,1).getValues();

  for(var i=0;i<rangeArray.length;i++){

    rangeArray[i] = rangeArray[i].toString().substr(0, 200);

  }

  activeSheet.getRange(1,column,maxRow,1).setValues([rangeArray]);     // I get an error saying that the length is 1 but should be 74 (maxRow value)
  //activeSheet.getRange(1,column,1,maxRow).setValues([rangeArray]);   //This code runs without error, but the values are pasted on the same row, on 74 different columns, flipping from vertical to horizontal which is nonsense
}

I have searched and read tons of articles, but couldn't get the sub-string functions to work either, for some reason I can't even access them. Also, if there's a way to do all the cells at once without doing a for() function any leads are greatly welcome.

EDIT: I added the solution proposed and it works for the substring. However while trying to do it as an array, the result is that all cells get the same value (coming from D1), even though the getValue is part of the for(). Any idea what I might be doing wrong? I tried to base my code on the best practices, but I really don't know what they are doing differently

EDIT2: i could get my code further by using getValues() outside of the for() loop, treating the values in the loop and using setValues([Array]). The only problem is that it doesn't let me set the values in the same range, I need to switch getRange(1,column,maxRow,1) to getRange(1,column,1,maxRow) which is nonsense because it flips the column to a row. Any idea? I've been reading around 4 hours on Arrays and documentation available is horrible

Why don't use the Google Sheets built-in LEFT function? Also try ARRAYFORMULA. — Rubén
Ruben, I'm trying to create a script. the Left function is available in googlesheets, not in googleapps script — Mathieu J.
setValue and setValues are quite different. Same for getValue and getValues. — tehhowch

rmccreary rmccreary · Accepted Answer · 2018-10-15T21:57:59

I'm not sure why the substring functions aren't working for you. I'm thinking it might be because the values you're reading aren't being interpreted as strings.

Try this:

function removeOverLenght(column, maxRow){  
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  
  for(var i=1;i<maxRow+1;i++){
    var range = activeSheet.getRange(i,column),
        // ensure you're working with a string
        value = "" + range.getValue();
    range.setValue(value.substr(0, 200));
  }  
}

Trying to reproduce Excel “LEFT()” function in GoogleScript

1 Answers