0

I am experimenting with converting my project from a ScriptDB storage to a Spreadsheet storage. I have problems all the time with ScriptDB, and this previous answer had some great data on spreadsheet's surprising relative speed.

My problem is that my objects can have a variety of number of attributes and I am not sure that it is possible to efficiently write this kind of data in bulk.

I might have a quick test array like:

  var arrData = [["sku:6100","price:16.00","Brand:Widget","Stock:2"],
                 ["sku:6102","price:13.00","Brand:Widget","Stock:2","Somethingelse:3"]]

Writing it with:

      var data_write = sheet.getRange(1, 1, arrData.length, 5).setValues(prod_data);

Will give me a complaint about the "5" length value saying it should be 4. If I use 4 it says it should be 5. Is there any way to force setValues() to write an array like this?

If not, the other two options I can see would be 1) Iterating through my array and padding it out with empty cell values to even out each row's length, or 2) Writing each object one line at a time.

Community
  • 1
  • 1
Nelluk
  • 1,171
  • 2
  • 12
  • 23

1 Answers1

1

Assuming that will only have to move the data from ScriptDB to your spreadsheet once, performance isn't a huge concern. You could loop over each row prod_data is an array you've created from the data in arrData (option 2 in your question):

for (var row=0; row < prod_data.length; row++) {
  var data_write = sheet.getRange(1, 1, 1, prod_data[row].length).setValues(prod_data[row]);
}

HOWEVER - you may find that the output data does not consistently line up with column names, because the order of object properties is not guaranteed. If that is a problem, an alternative solution (like your option 1) is to take advantage of the ObjService library and its objectToArray() method. By doing so, every row will be the same length, and all properties will end up aligned in the row arrays according to the order of the headers.

var headers = ["sku","price","Brand","Stock","Somethingelse"...];
var prod_data = [];
prod_data.push(headers);
for (var row=0; row < arrData.length; row++) {
  // get a 2D array from this object
  var rowData = objectToArray(headers,arrData[row]);
  prod_data.push(rowData[0]);
}
var data_write = sheet.getRange(1, 1, rowData.length, rowData[0].length).setValues(prod_data);

WRT the performance comparison of ScriptDB vs Spreadsheets, you should consider what operations you are doing. ScriptDB is designed for fast access to keyed data - it's ability to respond to queries that search for matches was not part of the analysis in that previous answer. I'm just saying this so you're aware of it, and I encourage you to do some measurements yourself in your own application to decide whether a move out of ScriptDB is warranted.

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Thanks -- this may be a faulty idea, but I was thinking I would load the spreadsheet array, convert it to a JSON keyed by one of the column values, do my work on it, re-convert to an array, and re-save to the spreadsheet. Not ideal at all, but ScriptDB just seems so problematic. I've also been thinking about playing with BigQuery and putting everything into a more traditional table and querying with SQL, but this doesn't seem to be the use case that BigQuery was designed for. – Nelluk Jun 06 '13 at 14:26