Skip to content
Advertisement

Wait for Google translate api before writing to excel sheet

I can’t figure out how to make my code wait for google translate before trying to write to the excel sheet. In the console i can see the translated data but in the excel doc it is still in the original language. i know I’m doing this all wrong but I can’t figure it out.

It’s not shown in the code below, but at the beginning i read data from a .csv file. Get only the data i need and store it as an array of objects for each case. I then go through that array and write down each case/object to an excel file.

There is a prop called verbatim which is text i am trying to translate to english before writing it to the excel file. But it’s not working.

  cases.push({
    caseid: row['[CALLERNO_EMAIL_SOCIAL]'],
    caseorigine: row['[CASE_ORIGINE]'],
    emailaddress: row['[EMAIL]'],
    agent: row['[AGENT]'],
    region: row['[REGION2]'],
    csat: parseInt(row['Satisfaction note'], 10),
    fcr: fcr,
    phone: row['[PHONE]'],
    sentDate: row.date_envoi.slice(0, 10),
    receivedDate: row.date_reponse.slice(0, 10),
    verbatim: row['Verbatim experience'],
    tally1: row['[TALLY_1]'],
    tally2: row['[TALLY_2]'],
  });

const filter = {
  'Email entrant': 'emailentrant',
  'Email Sortant': 'emailsortant',
  'Appel Entrant': 'appelentrant',
  'Appel Sortant': 'appelsortant',
  Chat: 'chat',
};

function filterArray(array, filter) {
  return array.filter((item) => {
    if (filters.includes(filter[item.caseorigine])) {
      if (!filters.includes('includecsat5')) {
        if (parseInt(item.csat, 10) !== 5) return item;
      } else {
        return item;
      }
    }
  });
}
const filteredCases = await filterArray(cases, filter);

// translate verbatim value to english
async function translateTextWithModel() {
  filteredCases.forEach(async (item) => {
    const options = {
      to: target,
      model: model,
    };
    if (item.verbatim !== '') {
      let [verbatim] = await translate.translate(item.verbatim, options);
      console.log(verbatim);
      item.verbatim = verbatim;
    }
  });
}

await translateTextWithModel();

// write data to excel
filteredCases.forEach(async (item) => {
  worksheet.getRow(rowNum).getCell(1).value = item.caseid;
  worksheet.getRow(rowNum).getCell(2).value = item.caseorigine;
  worksheet.getRow(rowNum).getCell(3).value = item.emailaddress;
  worksheet.getRow(rowNum).getCell(4).value = item.agent;
  worksheet.getRow(rowNum).getCell(5).value = item.region;
  worksheet.getRow(rowNum).getCell(6).value = item.csat;
  worksheet.getRow(rowNum).getCell(7).value = item.fcr;
  worksheet.getRow(rowNum).getCell(8).value = item.phone;
  worksheet.getRow(rowNum).getCell(9).value = item.sentDate;
  worksheet.getRow(rowNum).getCell(10).value = item.receivedDate;
  worksheet.getRow(rowNum).getCell(11).value = item.verbatim;
  worksheet.getRow(rowNum).getCell(12).value = item.tally1;
  worksheet.getRow(rowNum).getCell(13).value = item.tally2;
  worksheet.getRow(rowNum).commit();
  rowNum += 1;
});

const outFile = `${destination}/${shortid.generate()}.xlsx`;

await workbook.xlsx.writeFile(outFile).then(async function () {
  const file = new File({
    title,
    description,
    file_path: outFile,
    file_mimetype: mimetype,
  });
  await file.save().then(res.send('file uploaded successfully.'));
});

Advertisement

Answer

I fixed it using a module called p-iteration

const { forEach } = require('p-iteration');


  await forEach(filteredCases, async (item) => {
          async function translateTextWithModel(text) {
            const options = {
              to: 'en',
              model: 'nmt',
            };
            if (text !== '') {
              var [verbatim] = await translate.translate(text, options);
              return verbatim;
            }
          }
          item.verbatim = await translateTextWithModel(item.verbatim);
          worksheet.getRow(rowNum).getCell(1).value = item.caseid;
          worksheet.getRow(rowNum).getCell(2).value = item.caseorigine;
          worksheet.getRow(rowNum).getCell(3).value = item.emailaddress;
          worksheet.getRow(rowNum).getCell(4).value = item.agent;
          worksheet.getRow(rowNum).getCell(5).value = item.region;
          worksheet.getRow(rowNum).getCell(6).value = item.csat;
          worksheet.getRow(rowNum).getCell(7).value = item.fcr;
          worksheet.getRow(rowNum).getCell(8).value = item.phone;
          worksheet.getRow(rowNum).getCell(9).value = item.sentDate;
          worksheet.getRow(rowNum).getCell(10).value = item.receivedDate;
          worksheet.getRow(rowNum).getCell(11).value = item.verbatim;
          worksheet.getRow(rowNum).getCell(12).value = item.tally1;
          worksheet.getRow(rowNum).getCell(13).value = item.tally2;
          worksheet.getRow(rowNum).commit();
          rowNum += 1;
        });
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement