How to Parse student marks .txt data to google sheet using google script

Question

I have multiple .txt file that contains students' mark for each subject.

1. students' mark1
2. students' mark2

I want to parse the student mark to google sheet using google script and the output should be like this.

1. Student's mark sheet.

I have a problem with the range of the column as each student they are taking a different number of subject. Is there any suggestion on the parsing method?

sheet.getRange(lastRow +1,5,mark.length,marks[1].length).setValues(mark);

Answer 1

When I saw your sample image, I guessed that your current issue might be due to the array length of each element in the values for putting to the Spreadsheet. If my understanding is correct, how about the following modification?

From:

sheet.getRange(lastRow +1,5,mark.length,marks[1].length).setValues(mark);

To:

const maxLen = Math.max(...mark.map(r => r.length));
const values = mark.map(r => r.length < maxLen ? [...r, ...Array(maxLen - r.length).fill("")] : r);
sheet.getRange(lastRow + 1, 5, values.length, values[1].length).setValues(values);

• In this modification, each length of your values becomes the same length. By this, setValues can be used.

Note:

• In your showing script, it seems that mark and marks are used. From your script, I understood that mark is the value for putting to Spreadsheet.

Reference:

• map()