Search code examples
google-apps-scriptgoogle-sheetsconcatenation

How to get part of a string when concatenating?


To improve the loading speed of my sheet page, I would to try to convert the function below into a script. In fact with the Chrome DevTools, I have tried to delete the column with this function inside and the page speed has increased significantly from 35 sec to 14 sec.

The function:
={"ID"; arrayformula(IF(C2:C="";;(LEFT(C2:C;1))&"-"&(LEFT(D2:D;1))&(LEFT(E2:E;1))&"-"&(UPPER(RIGHT(B2:B;8)))))}

I don't know how to concatenate only the first character of the col C, the first character of the col D, first character of the col E and the last eight characters of col B. How to run the script only if there is an entry or an update of the values in the columns C,D,E,B?

The code I tried to write:

function concatenate() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var data = sheet.getDataRange().getValues();
      var lr = sheet.getLastRow()
      var values1 = sheet.getSheetValues(2, 4, lr, 1);
      var values2 = sheet.getSheetValues(2, 5, lr, 1);
      var values3 = sheet.getSheetValues(2, 2, lr, 1);
      var results = [];
      for (var i = 0; i < lr; i++) {
        results.push([values1[i] + " " + values2[i] + " " + values3[i]]);
      }
      sheet.getRange(2, 1, results.length).setValues(results);
    }

Solution

  • Use String.slice to get part of the string:

    results.push([values1[i][0].slice(0,1) + " " + values2[i][0].slice(0,1) + " " + values3[i][0].slice(-8)]);
    

    Note that values1 is a 2D array. So, [0] is needed to index into the inner dimension