Trouble pushing worksheet template: what am I doing wrong?

[patjdixon]

I am trying to follow 'Workbook Templates' example in SPy documentation to push a worksheet template into a workbook.  I believe the correct command is:

spy.push(workbook={new workbook ID that I am pushing to}, worksheet={template worksheet ID that I want to insert into the new workbook})

I find the push gives me a link to the correct workbook that I want to push to, but no worksheet is inserted.  I know that the template worksheet is the correct ID.  If I replace the worksheet ID with a string for a worksheet name, it will insert a blank worksheet with that name.  

See attached files.  What am I doing wrong?

 

CreateInstancesFromTemplates.ipynb

[Mark Derbecker]

Pat if you're only pushing a workbook (and not accompanying that workbook push with calculations as well), you'll use `spy.workbooks.push()` instead of `spy.push()`.

Take a look at the "Using Pre-existing Signals/Conditions/Scalars etc" section of the Workbook Templates.ipynb documentation file:

[patjdixon]

I am trying to push a worksheet, not workbook.  I have a workbook 'Pulmac TestInstanceWorkbook' and want to push the template worksheet 'Signal List' into it.

[Mark Derbecker]

I think this should work:

# Pull the workbook
workbook = spy.workbooks.pull(workbook_id)

# Add the template worksheet
if template_worksheet.name in workbook.worksheets:
    workbook.worksheets[template_worksheet.name] = template_worksheet
else:
    workbook.worksheets.append(template_worksheet)

# Push back up
spy.workbooks.push(workbook)

 

[patjdixon]

On the line:

if template_worksheet.name in workbook.worksheets:

I get:

AttributeError: 'WorkbookList' object has no attribute 'worksheets'

[Mark Derbecker]

Sorry, try this:

# Pull the workbook
workbooks = spy.workbooks.pull(workbook_id)
workbook = workbooks[0]

# Add the template worksheet
if template_worksheet.name in workbook.worksheets:
    workbook.worksheets[template_worksheet.name] = template_worksheet
else:
    workbook.worksheets.append(template_worksheet)

# Push back up
spy.workbooks.push(workbook)

 

[patjdixon]

Yudaman!  Thanks Mark.

[patjdixon]

I found a problem.  Append does not append the worksheets in the workbook.  The template worksheet ID gets pushed and just replaces the worksheet that is there.  When you change the worksheet name, the ID is retained so you do not add another worksheet.  It just renames the worksheet already there.  I need a way to append a workbook with instances of the template worksheet.  Ideas?

[patjdixon]

To be clear, if I run this 5 times with and rename template_worksheet.name each time, I will end up with 1 worksheet that has the name of the last template_worksheet.name.  What I want is 5 worksheets with the names I assign for each worksheet.

[Mark Derbecker]

It's a bit hidden in the documentation, but in this scenario where you're wanting to add several worksheets that use the same template, you'll need to use the copy() function (see below). You must supply a label argument. You'll do this before you assign a name and before you append to the worksheets list.

[patjdixon]

I have it working, but I am getting an error.  It does not prevent execution, but I want to clean it up.  The code I have that is working is:

This does insert new worksheets, but I have errors that seem to indicate push is trying to push the template workbook, which I do not want to do.  I only want to push new worksheets to the existing workbook I have:

WorksheetAppend_testcode.ipynb

[Mark Derbecker]

Pat, in the ipynb file you attached, it's showing that the error is 

AttributeError: 'Analysis' object has no attribute 'parameters'

Can you attach a notebook with the dependency error showing (that you took a screenshot of above), and with the stack trace expanded?

Also, for your template worksheet, do you have a Journal with links to worksteps in it? I think that may be causing a problem. (It shouldn't cause a problem... but I need to see what the Journal looks like.)

 

 

Edited July 3, 2023 by Mark Derbecker

[patjdixon]

I don't understand how the wrong code got uploaded.  Here it is again, and it should show the same result as the image.  There is no stack trace available.

I do have links in the template:

WorksheetAppend_testcode-2.ipynb

[Mark Derbecker]

OK-- so my guess is that the Journal links don't work properly, is that true?

(That's what the error message boils down to.)

I think everything else about the worksheet(s) should be correct... am I right?

[patjdixon]

I think you are right.  Everything seems to work.  I do want the links in the template, but I need to figure out how to get the instance to have those figures (Trend, XY Plot, Table) configured so I don't have to manually recreate them.  I think the Mustache templating system will help.

Actually, looking at the "Workbook Templates" help, it looks like Mustache would only allow variables that are text or signals, not worksteps that would open preset Trend, XY Plot, Table, etc.  Therefore, I think I just have to live with the error messages; I don't see another way.

[Mark Derbecker]

Well, actually, that all SHOULD work the way you intend it -- where you have a Journal that opens those preset visualizations.

But it's not working, and I think it's a bug. Can you execute the following lines at the bottom of your notebook and send me the resulting zip files?

spy.workbooks.save(spy.workbooks.load('./PJD_WorksheetTemplate'), 'ForMark-Templates.zip')
spy.workbooks.save(WorkbookInstance_List, 'ForMark-PulledWorkbook.zip')

(I agree with your comment -- the Mustache stuff won't help here.)

Edited July 4, 2023 by Mark Derbecker

[patjdixon]

Mark, yudaman!  Thanks, zip files are attached.  Happy 4th!

ForMark-PulledWorkbook.zip ForMark-Templates.zip

[Mark Derbecker]

Pat can you tell me what version of SPy you're on? You can do

print(spy.__version__)

 

[patjdixon]

188.0

[Mark Derbecker]

Pat, I believe I've found the problem, and it looks like SPy is incorrectly throwing an error. Actually, the operation is completely successful, and the journal links should work. Can you confirm that? I'll fix the error-throwing problem.

[patjdixon]

I can confirm everything works