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

Question

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?

Tanaike · Accepted Answer · 2023-12-04 13:05:43Z

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:

Method: spreadsheets.values.append

Collectives™ on Stack Overflow

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

1 Answer 1

Modified script:

Note:

Reference:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

Note:

Reference:

Comments

Your Answer

Sign up or log in

Post as a guest

Related