Copy data from multiple Excel files to one worksheet in Google Sheets – ExcejJS – how to avoid overwriting data?

by
Explore The Content hide
Modified script:
Note:
Reference:

First of all – I’m a rookie, not a developer.

I’m trying to copy data from multiple excel files, that are in one folder, to one worksheet in Google Sheets. I’m using excelJS and Google Sheets API. The issue is that despite I’m using “append” not “update” method, it overwrites the data. I assume it’s because the function is async. Is that the reason?

Here are parts of the code:

client.authorize(function (err, tokens) {
    if (err) {
        console.log(err);
        return;
    } else {
        console.log('Connected');
        gsrun();
        fs.readdir(folder, (err, files) => {
            files.forEach(file => {
                gsheetsUpload(file);
            });
        });
    };
});

function gsrun() {
    const clearOptions = {
        spreadsheetId: sID,
        range: 'Sheet1!A2:AB1000',
    };

    let resRemove = gsapi.spreadsheets.values.clear(clearOptions);
};

async function gsheetsUpload(f) {
    let filename = folder + f
    let wb = new Excel.Workbook();
    let excelFile = await wb.xlsx.readFile(filename);
    let ws = excelFile.getWorksheet('Some worksheet');
    let data = ws.getSheetValues();
    let prefix = ws.getCell('D6').value;
    let dataArray = [];

    data = data.map(function (r) {
        if (r[1] == 'Some cell value') {
            dataArray.push([r[2]]);
        };
    });

    const updateOptions = {
        spreadsheetId: sID,
        range: 'Sheet1!A2',
        valueInputOption: 'USER_ENTERED',
        resource: { values: dataArray }
    };

    console.log(dataArray);
    let resUpload = await gsapi.spreadsheets.values.append(updateOptions);
};

I read about Promises and tried using them. I’ve also tried setting timeout but that also didn’t help. How can I make the script wait for each iteration to end before the next one starts?

In your showing script, how about the following modification?

Modified script:

In this modification, all values are retrieved from the XLSX files, and the retrieved values are put into the Spreadsheet. By this, Sheets API is called one time for putting the values.

client.authorize(async function (err, tokens) {
  if (err) {
    console.log(err);
    return;
  } else {
    console.log('Connected');
    await gsrun();
    fs.readdir(folder, async (err, files) => {
      let dataArray = [];
      for (let i = 0; i < files.length; i++) {
        const res = await gsheetsUpload(files[i]);
        dataArray = [...dataArray, ...res];
      }
      if (dataArray.length > 0) {
        const updateOptions = {
          spreadsheetId: sID,
          range: "Sheet1!A2",
          valueInputOption: "USER_ENTERED",
          resource: { values: dataArray },
        };
        let resUpload = await gsapi.spreadsheets.values.append(updateOptions);
      }
    });
  };
});

async function gsrun() {
  const clearOptions = {
    spreadsheetId: sID,
    range: "Sheet1!A2:AB1000",
  };
  let resRemove = await gsapi.spreadsheets.values.clear(clearOptions);
}

async function gsheetsUpload(f) {
  let filename = folder + f;
  let wb = new Excel.Workbook();
  let excelFile = await wb.xlsx.readFile(filename);
  let ws = excelFile.getWorksheet("Some worksheet");
  let data = ws.getSheetValues();
  //   let prefix = ws.getCell("D6").value; // It seems that this is not used.
  let dataArray = [];
  data.forEach(function (r) {
    if (r[1] == 'Some cell value') {
      dataArray.push([r[2]]);
    };
  });
  return dataArray;
}
  • I thought that in this modification, await gsapi.spreadsheets.values.append(updateOptions); might be able to be modified to await gsapi.spreadsheets.values.update(updateOptions);.

Note:

  • In this modification, it supposes that the values of gsapi, sID, filename, the sheet name and Some cell value of r[1] == 'Some cell value' are valid values. Please be careful about this.

Reference:

Leave a Comment