Pls be gentle!. Newbe having a go. I am using node.js (for the first time)
I have a large (10,000 line) XML file that I need to rip through and generate about 900 rows across 50 columns in smartsheets. I need to retain the order that comes from the XML file (important).
I have the code working so that it reads the xml file, and can write to the rows/columns but obviously I am generating the update inputs faster than smartsheet can handle, so I tried promises. I just can't seem to crack it and would appreciate any help you can give. The system generates all of the records but chokes as it tries to write them to smasrtsheet AND the order is all stuffed up.
Happy to be told I am barking up the wrong tree if you can suggest a better way.
Thanks in advance.
Jerji.
3 blocks of code:
testing.js file (the script itself)
testing.xml (a VERY cut down version of the xml file)
testing.xsd (the xml schema file).
testing.js
// Initialize the client
var client = require('smartsheet');
var smartsheetClient = client.createClient({
accessToken: '<insert your own access token before you run>', // use your access code
logLevel: 'info'
});
var fs= require('fs'),
xml2js = require('xml2js');
const parser = new xml2js.Parser();
const xpath = require('xpath'),
dom = require('xmldom').DOMParser;
fs.readFile('testing.xml',function(err , data){
var doc = new dom().parseFromString(data.toString(), 'text/xml');
var select = xpath.useNamespaces('testing.xsd');
if(err){
//display error
}
else{
for (var i=0; i < 10 ; i++ ){
var rowcounter=i+1;
var identifier = select('//Identifier/text()', doc)[i].nodeValue;
var revision = select('//Revision/text()', doc)[i].nodeValue;
var updated = select('//Updated/text()', doc)[i].nodeValue;
var description = select('//Description/text()', doc)[i].nodeValue;
var row = [{
"toBottom": true,
"cells": [
// Note that the column Ids here are samples. Adjust as required.
{"columnId": 2461535086897028, "value": rowcounter, "strict": false},
{"columnId": 6965134714267524, "value": identifier, "strict": false},
{"columnId": 1335635180054404, "value": description, "strict": false},
{"columnId": 7457715923511172, "value": revision, "strict": false},
{"columnId": 1828216389298052, "value": updated, "strict": false},
{"columnId": 7176240946800516, "value": 'Marker', "strict": false},
]
}];
writeRow(row);
sleep();
}
}
);
// DUMMY SLEEP FUNCTION
var sleep = function () {
let now = Date.now(), end = now + 3000;
while (now < end) { now = Date.now(); }
};
// Function to write row to sheet.
function writeRow(row) {
var options = {
sheetId: <insert your own sheet ID here>, //note real sheet id needed
body: row
};
return new Promise((resolve, reject) => {
try {
let obj = smartsheetClient.sheets.addRows(options);
resolve(obj);
} catch (err) {
reject(err);
};
});
let myPromise = smartsheetClient.sheets.addRows(options);
myPromise.then(data => {
console.log("Line Written :", data);
}).catch(err => {
console.log(err);
});
}
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<!-- June 2021 -->
<DOCUMENT>
<ITEM>
<Identifier>2021-91</Identifier>
<Revision>5</Revision>
<Updated>Oct-20</Updated>
<Description>Item Description 1.</Description>
</ITEM>
<ITEM>
<Identifier>2021-97</Identifier>
<Revision>1</Revision>
<Updated>Oct-20</Updated>
<Description>Item description 2.</Description>
</ITEM>
<ITEM>
<Identifier>2020-14</Identifier>
<Revision>0</Revision>
<Updated>Oct-20</Updated>
<Description>Item description 3.</Description>
</ITEM>
<ITEM>
<Identifier>2019-44</Identifier>
<Revision>2</Revision>
<Updated>Oct-20</Updated>
<Description>Item description 4.</Description>
</ITEM>
<ITEM>
<Identifier>2021-06</Identifier>
<Revision>2</Revision>
<Updated>Oct-20</Updated>
<Description>Item description 5.</Description>
</ITEM>
<ITEM>
<Identifier>2019-13</Identifier>
<Revision>2</Revision>
<Updated>Oct-20</Updated>
<Description>Item description 6.</Description>
</ITEM>
<ITEM>
<Identifier>2020-03</Identifier>
<Revision>2</Revision>
<Updated>Oct-20</Updated>
<Description>Item description 7.</Description>
</ITEM>
<ITEM>
<Identifier>2021-19</Identifier>
<Revision>2</Revision>
<Updated>Oct-20</Updated>
<Description>Item description 8.</Description>
</ITEM>
<ITEM>
<Identifier>2019-56</Identifier>
<Revision>0</Revision>
<Updated>Oct-20</Updated>
<Description>Item description 9.</Description>
</ITEM>
<ITEM>
<Identifier>2020-15</Identifier>
<Revision>3</Revision>
<Updated>Oct-20</Updated>
<Description>Item description 10.</Description>
</ITEM>
</DOCUMENT><?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="DOCUMENT">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="ITEM">
<xs:complexType>
<xs:sequence>
<xs:element name="Identifier" type="xs:unsignedShort" />
<xs:element name="Revision" type="xs:unsignedByte" />
<xs:element name="Updated" type="xs:string" />
<xs:element name="Description" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Any advice would be most appreciated.
Promise is asynchronous API which has it's own independant context. It has 3 statuses, which are pending, resolve and reject.
When the Promise is resolved, it calls .then(cb) and in case it failed, which is reject, it calls .catch(cb) internally.
first of all, you didn't wait for Promise to be done either way that it be resolved or rejected.
You should use .then() and .catch() to wait for it be done. Maybe async/await API will be even better for doing that.
const delay = ms => new Promise(resolve => setTimeout(() => resolve(), ms)
function writeRow(row) {//Return your promise and let it be controlled outside of function
var options = {
sheetId: < insert your own sheet ID here > , //note real sheet id needed
body: row
};
return new Promise((resolve, reject) => {
try {
let obj = smartsheetClient.sheets.addRows(options);
resolve(obj);
} catch (err) {
reject(err);
};
});
}
(or you can just use traditional way to control promise with .then(), .catch())
async function runner(){
...your scripts...
for (var i = 0; i < 10; i++) {
...
await writeRow(row)
await sleep(3000)//write row and wait for 3 seconds
...
}
}
...your scripts...
}
runner()