Google Sheets lineup text

Question

Hello I would like to know how I can lineup the text based on the characters that the row have, I will show you an example of what I want to make and the code that I currently have.

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange('F3:L' + sheet.getLastRow());
  var rows = range.getValues().filter(([ f, g, _, i, j, , l]) => f.toString() != "" && g.toString() != "" && i.toString() != "" && j.toString() != ""  && l.toString() != "");
  var fileName="example.txt";
  var folderName="Example";
  var data = rows.splice(0);
  var str = data.map(([f, g, _, i, j,  , l]) => `${f} ${g} (${i}${l})`).join("
");
  var content = str;

This code export the content of that rows to a .txt like this.

And this is want i want to make.

I think that i need to count how much characters have the line and put the spaces that require to line up the text.

question from:https://stackoverflow.com/questions/65946914/google-sheets-lineup-text

Answer 1

I believe your goal as follows.

• You want to create the string values with the same length for each line using Google Apps Script.

Modification points:

• In this case, I would like to propose the following flow.
  1. Retrieve the array with the string values in each element.
     • In this case, I use your script of data.map(([f, g, _, i, j, , l]) => `${f} ${g} (${i}${l})`).
  2. Retrieve the maximum length of the string values from the array.
  3. Insert the space using the retrieved maximum length. By this, the result values are obtained.

When this flow is reflected to your script, it becomes as follows.

Modified script:

Please modify your script as follows.

From:

var str = data.map(([f, g, _, i, j,  , l]) => `${f} ${g} (${i}${l})`).join("
");

To:

var ar = data.map(([f, g, _, i, j,  , l]) => `${f} ${g} (${i}${l})`);
var maxLen = ar.reduce((l, e) => l < e.length ? e.length : l, 0);
var str = ar.map(e => e.replace(/(([sSw]+))$/, `${"".padEnd(maxLen - e.length, " ")}$1`)).join("
");

Result:

When the sample values are used for above modified script, the following result is obtained.

f3 g3     (i3l3)
f4 g4     (i4l4)
f5 g5     (i5l5)
f6 g6     (i6l6)
f7 g7     (i7l7)
f8 g8     (i8l8)
f9 g9     (i9l9)
f10 g10 (i10l10)
f11 g11 (i11l11)
f12 g12 (i12l12)
f13 g13 (i13l13)
f14 g14 (i14l14)
f15 g15 (i15l15)
f16 g16 (i16l16)
f17 g17 (i17l17)
f18 g18 (i18l18)
f19 g19 (i19l19)
f20 g20 (i20l20)

References:

• reduce()
• padEnd()