How to add a new row into Google Spreadsheet and extend cells' formula

[Chan Yu]

Hello,

I wish someone could help me with this question as title.

I have a Google Spreadsheet with six columns like the screenshot1 shows. The columns with green header will be entered from RMP during processes, while the grey ones have formulas, which depend on the cell content in the same line. For example, the value of E3 is
=IF(EQ(C3,"Submitted"),D3,0)
and F4 is
=IF(EQ(C4,"Approved"),D4,0)

Meanwhile, I have everything built in RMP to read/write to this spreadsheet. The process will insert columns A, B, C, and D. At the same time, The E and F should be filled with correct formula.

In this case, if I simply use "add a row" connector to add at the bottom of all records, the new row will be insert after the row with formulas showing in the screenshot2.

If I insert the whole row with formulas, how could I got the cell content like C3 like the screenshot3 showing? Also, the formula could not be changed since it has effects on other worksheets.

Thanks so much for your help.

Regards,
Chan

[Chan]

[Bidisha Das]

Hi Chan,

There is no specific api to apply formula in a Google worksheet cell.Hence, you can't use the code as you mentioned in screenshot 3 because this code is used to insert values to the respective worksheet columns in a spreadsheet which is nothing but adding rows to the spreadsheet.You can't use this to set formula in a specific cell in the worksheet.

You can do it via google apps-script. 

Please refer to : http://stackoverflow.com/questions/12036726/how-do-i-add-formulas-to-google-spreadsheet-using-google-apps-script

and https://developers.google.com/apps-script/reference/spreadsheet/range#setFormula(String)

Else,you can manually set the formula in the respective cell in the worksheet as you are doing currently.

To retrieve a cell-based feed,you can use Provider URL: https://spreadsheets.google.com/ 

& Connector URL: feeds/cells/${spreadsheet_id}/${worksheet_id}/private/full with Result format:XML

It will return the content of all the cells of a given worksheet.

You can also refer to: https://developers.google.com/google-apps/spreadsheets/data#retrieve_a_cell-based_feed

To retrieve a specific row or column,you can refer to:  https://developers.google.com/googleapps/spreadsheets/data#fetch_specific_rows_or_columns

Thanks & Regards

Bidisha

[Chan]

Hey Bidisha,

Thanks for your reply.
For the formula thing, I just hard code them into the process.....

Just encounter another question here.
I have a array in my WI, which content a date picker. And the date will be passed into Google Spreadsheet.

However, the format is time stamp and I only want the year.

I got a build in function for freemarker to convert timestamp to date which is ?number?number_to_date. But the available version of RMP is lower than it.


Is there any way to get the year only?

Thank you so much for your help.

Regards,
Chan

[Chan]

Hey,

I figured out.

I created a new invisible column which execute the JS to convert that timestamp to year only.

Thank you anyway~

Regards,

Chan