1

I currently have a project tracker in a Google sheet which records a project name, status character column and three progress columns as below:

enter image description here

I would like to record a daily log of each progress relative to the project so that I have a table recording the following headings automatically: Date, Project 1 Progress 1, Project 1 Progress 2, Project 1 Progress 3, Project 2 Progress 1, Project 2 Progress 2, etc...

I have been able to use the following script to copy one columns daily data to another column, but don't know how to transpose this:

  function recordHistory() {var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var source = sheet.getRange("C2:C8");
  var values = source.getValues();
  values = [[new Date()]].concat(values);    // prepending the date to values
  sheet.insertColumnAfter(7);                // inserting AFTER column G
  SpreadsheetApp.flush();         
  sheet.getRange("G1:G8").setValues(values)

Which I modified from this post: (Automatically record daily values in a new column).

Ideally, I would like to be able to visualize the progress in a graph as well as get some basic stats around how fast we are completing given projects so I can better predict future projects time to completion. I am honestly at a loss and have only just begun working with macros in Google Sheets, so any help you can offer is greatly appreciated, as I'm trying to do this without using R or similar software.

0

1 Answer 1

0

don't know how to transpose this

You are already recording the progress daily using a script function. To make the function copy data into a blank column instead of overwriting previously recorded data, replace .insertColumnAfter(7) with .insertColumnAfter(6). To run the script once a day, use an installable trigger.

To transpose the data into a new sheet, Insert > Sheet and put this formula in cell G1 of the new sheet:

=query( transpose(Sheet1!F1:K), "where Col1 is not null offset 1", 0 )

...where F is one column before the first column with recorded data and K is the current last column with recorded data. The formula will automatically adjust and expand its range as new columns are inserted daily.

Then Insert > Chart whose data range points to the transposed data on the new sheet. You can place the chart in the project data sheet if you want.

2
  • Thank you for your speedy help @doubleunary! If I wanted to transpose in the same sheet is there a way to do it so that my current script generates rows instead of columns?
    – Jarrod C
    Jan 24, 2023 at 8:48
  • That can be done, but I am afraid you would be in trouble with that in the longer run. Use the solution above to minimize hassle down the road. Note that you can place the chart in the project data sheet even when the chart's data range is in the transposed record sheet. Or you can create a chart directly from the record data without transposing. Jan 24, 2023 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.