How to fill value into the next column?

Help Activities
, ,
1

Hi, I have an activity to fill the value in the next column daily as seen on the red box below :

image

So my robot will generate data daily. Currently the headers are made, but the problem is right now how to fill the data to the right side? Because currently my data is appending to bottom (rows) or it’s overwriting. How to write it in columns?

2

Hey!

Are we trying to update the data in the columns?

If yes,

Try like this:

Create one integer variable as counter and initialize with 1

Example:-

Assign Counter = 1

Now inside the for each row use write cell activity and in the range pass the expression like this

Range:- "K2:K"+(Counter+1).ToString

Next to this use one more assign to increment the counter like this

counter = counter+1

Regards,
NaNi

3

Seems like this is for K column only, what about the column L and M?

And also how’s the condition if the next day it’s running? The data is growing on the excel file… It will look like this :

image
image911×500 26.1 KB

4

Hey!

In this case you can check the current date like this…

CurrentRow(Now.ToString("dd-MMM-yy")).ToString

You have to mention the range to write the data to that column…

Regards,
NaNi

5

Sorry I’m explaining it in the wrong way. I mean the data is currently like this :

image
image1006×491 22.8 KB

So the date and the sub header are not part of the template, the robot will generate it. Let’s say I’m running today, the data will be like this :

image

The problem is right now how to pass the value to the columns? Because it will just continue filling in rows

6

Hello @Rhys18 , How do you get all data of GPS, Strange data, and Last location data?

7

Using HTTP Request Activity (API) JSon and I convert it to data table

8

By using this data table you write data into above mentioned daily data table right?

9

Yes daily data pulling… From monday-friday

10

Read the Excel file after the update Current date
In writing cell activity you need a Column index name.
GPS Column= UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(DTReadFullRange.Columns.Count-2)
Strange Column= UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(DTReadFullRange.Columns.Count-1)
Last Location= UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(DTReadFullRange.Columns.Count)

For above 30 July You can get K, L, M
Row index You have to give from 3 and increment it

11

Okay so from my understanding :

  1. This is expression to see if there’s value on it right? Can it be done on the last Column instead? (Last Location) so it doesn’t have to check the GPS and Strange column?

  2. It didn’t answer my main question, so let me give you explanation in picture :

image
image1620×663 66.9 KB

if there’s anything unclear let me know :slight_smile:

12

Before You Write data, read it and pass the dt value for find last column and append the value

13

So to append the value do I need append range activity?

14

If You copy from Jason converted Excel to daily data excel without ay changes follow this
Read data of converted Excel.
Read Daily data excel after entering the current data.
Use Write range and enter Cell value- UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(DTReadFullRange.Columns.Count-2)+“3”

15

Ahh so just use write cell only and the data table will be written as expected?

16

If you need to copy all data without editing and enter it into the Daily Excel file, use write range and enter the above conditions I mentioned

17

I don’t understand copy all data. What do you mean? The data is updated daily from the JSon and it might be not same

18

HI not sure if this would be of much help but noticed that the excel columns are always named like
A,B…Z followed by AA, AB …AZ, BA…BZ. Your data moves three columns everyday to the right .So maybe if you know the last column updated ,for next day you can can write cell by 3 columns.
07/29/22 last column is say AB
07/30/22 AC AD AE will be written.
07/31/22 AF AG AH will be written.
Sorry if i may have totally missed the mark :smile:

19

@Rhys18 Just Use these conditions for excel activities to Get the last column index position and frame activities as much as you need.

21

It work thanks ! And just now I tested and I found it copy/paste the previous range. So I do need to make template for it right? Can it be checked first with if condition?

for example :

if condition is true then just copy paste
else write…

I guess it only work with template right?