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);

CodePudding user response：

Try this

  const file = DriveApp.getFileById('YOUR TEXT FILE ID').getBlob().getDataAsString();
  const data = Utilities.parseCsv(file,'|');
  const arr = []
  const index = [] //0,7,12
  for(let i =0; i< data.length;i  ){
    if(data[i].toString().includes('*')){
      index.push(i);
    }
  }
  const arr1 = []
  for (let i =0;i< index.length;i  ){
      const start = index[i];
      const end = index[i 1]
      if(i 1 > index.length){
        arr1.push(data.slice(end))
      }else{
        arr1.push(data.slice(start,end))
      }
  }
  const finalarr = []
  for(let i=0; i< arr1.length;i  ){
    const transit = []
    for(let j=0;j<arr1[i].length;j  ){
       if(j == 0){
         transit.push(arr1[i][j][0],arr1[i][j][2])
       }else{
         transit.push(arr1[i][j][0].split(':')[1])
       }
    }
    finalarr.push(transit)
  }
  finalarr.forEach(r =>{
    if(r[0].includes('*')){
      r[0] = r[0].replace('*','')
    }
  })

  
  //sheet.getRange(lastRow  1,5,finalarr.length,finalarr[1].length).setValues(finalarr);
  console.log(finalarr)
  //OUTPUT 
  [ [ 'ELMI ALSA', '89.0', '60', '88', '78', '68', '89', '70' ],
  [ 'EMMY JOE', '80.0', '86', '84', '89', '90' ],
  [ 'ELIZA MOHRE', '73', '60', '70' ] ]

CodePudding user response：

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()