Skip to main content
A
Anonymous
October 25, 2011
Answered

Can you add a formula when using spreadsheetAddRows()?

  • October 25, 2011
  • 1 reply
  • 992 views

I've created a spreadsheet object with two sheets. I've added content to each sheet using spreadsheetAddRows(), but is it possible to inject a hyperlink formula for each record returned by the query I've passed to spreadsheetAddRows()? 

Sheet one contains a list of "topic" numbers. My goal is to make these numbers link to coresponding "details" on the second sheet. Thanks for any pointers you folks can offer. Here's my code so far:

          //     Set path for file
          theDir = GetDirectoryFromPath(GetCurrentTemplatePath());
          theFile = theDir & "RPC_Reports\#ReportName.ReportFileName#.xlsx";
          
          //     Create an empty spreadsheet object that will contain two sheets
          sheet = spreadsheetNew("Topic Ranking", "true");
          
          
          /////// Sheet One ///////
                              
          //     Populate first sheet with query data
          spreadsheetAddRows(sheet, qrytopicList, 3);
          
          
          //     Formulate hyperlinks to sheet two
          for(i=1; i <= qrytopicList.recordCount; i++) {
              
              spreadsheetSetCellFormula(sheet, 'HYPERLINK("##Topic Details!A1", "#qrytopicList.topicID#")', #i#,1);
             }

                    
          /////// Sheet Two ///////
          
          //     Creat a second sheet for this object
          spreadsheetCreateSheet(sheet, "Details");
          
          //     Set sheet two (Topic Details) as active
          spreadsheetSetActiveSheet(sheet, "Details");
                                        
          //     Populate second sheet with query data
          spreadsheetAddRows(sheet, qrytopicDetails);
                              
          //     Set active sheet to the first sheet before writting to disk
          spreadsheetSetActiveSheet(sheet, "Topic Ranking");
          
          //     Write spreadsheet to disk
          spreadsheetWrite(sheet, theFile, true);
     
This topic has been closed for replies.
Correct answer -__cfSearching__-

is it possible to inject a hyperlink formula for each record returned by the query

Yes, if you know which row numbers(s) they are pointing to in the second sheet.

>  spreadsheetCreateSheet(sheet, "Details");

spreadsheetSetCellFormula(sheet, 'HYPERLINK("##Topic Details!A1", "#qrytopicList.topicID#")', #i#,1);

Just be sure your query and cell references are correct

a) The sheet name in the hyperlink does not match the actual name in your code: 

b) IIRC, the sheet name must be enclosed in single quotes when it contains spaces

       ie =HYPERLINK("#'Topic Details'!A1","Text The

Link")

c) If you are using a from/to loop, do not forget the query row number. Otherwise, you are using the first row over and over.

1 reply

-
-__cfSearching__-Correct answer
Inspiring
October 25, 2011

is it possible to inject a hyperlink formula for each record returned by the query

Yes, if you know which row numbers(s) they are pointing to in the second sheet.

>  spreadsheetCreateSheet(sheet, "Details");

spreadsheetSetCellFormula(sheet, 'HYPERLINK("##Topic Details!A1", "#qrytopicList.topicID#")', #i#,1);

Just be sure your query and cell references are correct

a) The sheet name in the hyperlink does not match the actual name in your code: 

b) IIRC, the sheet name must be enclosed in single quotes when it contains spaces

       ie =HYPERLINK("#'Topic Details'!A1","Text The

Link")

c) If you are using a from/to loop, do not forget the query row number. Otherwise, you are using the first row over and over.

A
Anonymous
October 26, 2011

cfSearching:

a) Yeah, I had changed the name from "Topic Details" to "Details" to get rid of the space and forgot to update it here.

b) I stuck with the name "Details", but thanks for pointing out how I could include a space in the sheet name if I wanted to.

c) This was the key to the issue I was having, I had forgotten to include the query row number. This caused every row in the spreadsheet to have the same topic ID. Now I'm using:

// Set row variable because the first two rows contain a title and column headers.

for(i=1; i <= qrytopicList.recordCount; i++) {

row = #i# + 2;

spreadsheetSetCellFormula(sheet, 'HYPERLINK("##Details!A#row#", "#qrytopicList.topicID#")', #row#,1);

}

This creates a column in sheet one that links to "details" on sheet two.

Thanks for your help!

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices