0

I'm parsing an Excel file and it's coming back as an array object. I want to add each object value into the correct SharePoint list column.

I'm using POST to upload to info and the data is going through. SharePoint recognizes that two items (the two rows from the test.xlsx file) have been uploaded, but when I refresh the SP list both items are blank.

enter image description here

It looks as if each array object is getting added to the column, but it's getting rejected by SharePoint (having too many values, or something). Any thoughts? How can I make sure that the Holiday Title goes in the Holidays column, the Holiday Location goes in the Location column, etc.?


JS:

 reader.onload = function(e) { 
    let data = e.target.result;
    let workbook = XLSX.read(data, { type: 'binary' });

    workbook.SheetNames.forEach((sheetName) => {
        let XL_row_object = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);

        console.log("xl: ", XL_row_object) // You can see this at the bottom of the post

        if (XL_row_object) { 
            $.each(XL_row_object, function(value) {
                // this.value = $(value)

                let getTitle = XL_row_object.map(x => x.Title)
                let getOffice = XL_row_object.map(x => x.Office)
                let getStartTime = XL_row_object.map(x => x["Start Time"])
                let getEndTime = XL_row_object.map(x => x["End Time"])

                addItemToSPList(getTitle, getOffice, getStartTime, getEndTime)
            })

        } else {
            console.log('Parsed JSON does not appear to have necessary values')
        }

    })

} // reader.onload

// some other code 

}

function addItemToSPList(value) {
    let holidaysColumns = {
        "__metadata":{"type": "SP.Data.holidaysListItem"},
        "Title": value.getTitle,
        "Location": value.office, // did this instead of getOffice to see if anything changed
// FYI, the column in SP is "Location"
        "Start Time": value.getStartTime,
        "End Time": value.getEndTime
    };

    let listName = "holidays";
    return fetch(
// etc

Console snippet:

xl:(2) [{…}, {…}]
0: {Title: "Festivus", Office: "London", Start Time: 43895, End Time: 43895, __rowNum__: 1}
1: {Title: "Founder's Week", Office: "London", Start Time: 43897, End Time: 43900, __rowNum__: 2}
length: 2
__proto__: Array(0)
0

Try using your code like:

function addItemToSPList(getTitle, getOffice, getStartTime, getEndTime) {
    let holidaysColumns = {
        "__metadata": {"type": "SP.Data.holidaysListItem"},
        "Title": getTitle,
        "Location": getOffice, // FYI, the column in SP is "Location"
        "Start Time": getStartTime,
        "End Time": getEndTime
    };

    let listName = "holidays";
    return fetch(
// etc
| improve this answer | |
  • Hi @Bodrov, does this answers your question? have you got it working or you still facing this issue? – Ganesh Sanap Mar 23 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.