Sending the fieds from activity logs to a google sheet

[kayla_levy123 (Kayla Levy)]

Hi, I have a google app script that returns the data from the activity logs from my board and the user and created by fields as well. I was able to insert the whole payload into a cell in the google sheet. This was done for every log, so a row is filled with cells (each is a column) each cell contains the payload. Each cell looks like this:

{created_at=16599705890539942, data={“board_id”:2984947278,“group_id”:“topics”,“is_top_group”:true,“pulse_id”:3002211794,“pulse_name”:“Kayla”,“column_id”:“contact_position9”,“column_type”:“color”,“column_title”:“Contact Position”,“value”:{“label”:{“index”:1,“text”:“Owner”,“style”:{“color”:“#00c875”,“border”:“#00B461”,“var_name”:“green-shadow”},“is_done”:true},“post_id”:null},“previous_value”:{“label”:{“index”:3,“text”:“Consultant”,“style”:{“color”:“#0086c0”,“border”:“#3DB0DF”,“var_name”:“blue-links”},“is_done”:false},“post_id”:null},“is_column_with_hide_permissions”:false}, user_id=32384900}

I know want to take this data and split it into separate fields for each value. For example I want a column “board_id” and then the value from all the logs below. I want to do this for each field in data as well as for “user_id” and “created_by”. I have written a script to do this but it doe not seem to work. It changes the first cell to “Success” which means it has read the data but on a new sheet where I am sending the data it is not there. Below is the script to do this:

function split_values(){
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName(“Google script”); //Your original Sheet Name
var sheet2 = ss.getSheetByName(“Sheet6”); //Your modified Sheet Name
var cellRange = sheet.getDataRange();
var cellValues = sheet.getDataRange().getValues(); //All values from original sheet.

var arrayItems = ;
var array_to_print = ;
cellValues.forEach(function(row, index) {
var cell = sheet.getRange(index+1, 1)
Logger.log(cell.getValue())
if(cell.getValue() != “Success”){ //Check if we have already parsed this row
cell.setValue(“Success”) //If not, parse it and set status as Success.
var split_item = row[0].split(/data: /g);
Logger.log(split_item);
for(var i=1;i<split_item.length;i++){
var item = split_item[i].split(/, ‘board_id/)[0].split(/, ‘board_id/)[0].replace(/’/g,’"')+ ‘}’;
item = JSON.parse(item);
arrayItems.push(item);
}}
});

for (var i = 0; i < arrayItems.length ; i++) {
array_to_print.push([arrayItems[i].board_id])
}

Logger.log(array_to_print)

if(array_to_print.length>0){
sheet2.getRange(sheet2.getLastRow()+1, 1,array_to_print.length,1).setValues(array_to_print);
}
}

I have spent 2 weeks trying to figure this out. Any help would be greatly appreciated.

[Iryna_Belaya]

Hello @kayla_levy123 ,
While I can’t help with the script itself, I can recommend to test as a workaround Board Email Reports that may do this job for you.
It aggregates the changes in the activity log by the column you choose and over the period you choose in the automation rule, and sends the xls-report to the people you mention in the rule (no guest access needed!).
Report types

On a board/project level:

1. New and updated tasks
2. Status updates
3. Budget, Costs, Revenue changes
4. Timeline and deadline overdue
5. Time Tracking changes

On an item/task level:

• Updates by a specific employee
• Updates mentioning a specific employee
• Updates containing attachments
• Status updates
• Last updates

An example:

The email you get will contain the xls-report and roundup of changes:

I hope you will find it helpful.

[Matt_Frost]

@kayla_levy123

Hi, I have a google app script that returns the data from the activity logs from my board and the user and created by fields as well. I was able to insert the whole payload into a cell in the google sheet. This was done for every log, so a row is filled with cells (each is a column) each cell contains the payload. Each cell looks like this:

{created_at=16599705890539942, data={“board_id”:2984947278,“group_id”:“topics”,“is_top_group”:true,“pulse_id”:3002211794,“pulse_name”:“Kayla”,“column_id”:“contact_position9”,“column_type”:“color”,“column_title”:“Contact Position”,“value”:{“label”:{“index”:1,“text”:“Owner”,“style”:{“color”:“#00c875”,“border”:“#00B461”,“var_name”:“green-shadow”},“is_done”:true},“post_id”:null},“previous_value”:{“label”:{“index”:3,“text”:“Consultant”,“style”:{“color”:“#0086c0”,“border”:“#3DB0DF”,“var_name”:“blue-links”},“is_done”:false},“post_id”:null},“is_column_with_hide_permissions”:false}, user_id=32384900}

I know want to take this data and split it into separate fields for each value. For example I want a column “board_id” and then the value from all the logs below. I want to do this for each field in data as well as for “user_id” and “created_by”. I have written a script to do this but it doe not seem to work. It changes the first cell to “Success” which means it has read the data but on a new sheet where I am sending the data it is not there. Below is the script to do this:

function split_values(){
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName(“Google script”); //Your original Sheet Name
var sheet2 = ss.getSheetByName(“Sheet6”); //Your modified Sheet Name
var cellRange = sheet.getDataRange();
var cellValues = sheet.getDataRange().getValues(); //All values from original sheet.

var arrayItems = ;
var array_to_print = ;
cellValues.forEach(function(row, index) {
var cell = sheet.getRange(index+1, 1)
Logger.log(cell.getValue())
if(cell.getValue() != “Success”){ //Check if we have already parsed this row
cell.setValue(“Success”) //If not, parse it and set status as Success.
var split_item = row[0].split(/data: /g);
Logger.log(split_item);
for(var i=1;i<split_item.length;i++){
var item = split_item[i].split(/, ‘board_id/)[0].split(/, ‘board_id/)[0].replace(/’/g,’"')+ ‘}’;
item = JSON.parse(item);
arrayItems.push(item);
}}
});

for (var i = 0; i < arrayItems.length ; i++) {
array_to_print.push([arrayItems[i].board_id])
}

Logger.log(array_to_print)

if(array_to_print.length>0){
sheet2.getRange(sheet2.getLastRow()+1, 1,array_to_print.length,1).setValues(array_to_print);
}
}

I think this might be because theres a mix of syntax and language here, so the data will need a bit of a clean up as it’s not all JSON. To make it more compatible make sure it’s JSON friendly (e.g. use : instead of =).

You can also use (try-catch) which will help work out where in your script any errors are occurring. It also won’t crash the entire script if one line doesn’t work. You basically say “try this, and if it doesn’t work go here”, essentially skipping to the next section or an error printed statement.

Try the script below, see if that works, or incorporate elements into yours.

(Full disclosure its been a minute since I’ve written code like this so apologies in advance)

function split_values() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName(“Google script”);
var targetSheet = ss.getSheetByName(“Sheet6”);
var cellValues = sourceSheet.getDataRange().getValues();
for (var i = 0; i < cellValues.length; i++) {
var cellValue = cellValues[i][0];
if (cellValue === “Success”) continue;

try {
  // STEP 1: Clean your string to valid JSON
  var jsonString = cellValue
    .replace(/=/g, ':')
    .replace(/[“”]/g, '"')
    .replace(/(\w+):/g, '"$1":') // Wrap keys with quotes
    .replace(/, user_id/g, ',"user_id"') // Fix user_id formatting
    .replace(/, created_at/g, ',"created_at"'); // Fix created_at formatting
  
  // STEP 2: Parse JSON safely
  var dataObject = JSON.parse(jsonString);

  // STEP 3: Extract data fields
  var createdAt = dataObject.created_at || "";
  var userId = dataObject.user_id || "";
  var dataFields = dataObject.data || {};

  var boardId = dataFields.board_id || "";
  var groupId = dataFields.group_id || "";
  var isTopGroup = dataFields.is_top_group || "";
  var pulseId = dataFields.pulse_id || "";
  var pulseName = dataFields.pulse_name || "";
  var columnId = dataFields.column_id || "";
  var columnType = dataFields.column_type || "";
  var columnTitle = dataFields.column_title || "";

  // For nested fields like "value" and "previous_value"
  var valueLabel = dataFields.value ? dataFields.value.label.text : "";
  var previousValueLabel = dataFields.previous_value ? dataFields.previous_value.label.text : "";

  // STEP 4: Write extracted data to the target sheet
  targetSheet.appendRow([
    createdAt,
    userId,
    boardId,
    groupId,
    isTopGroup,
    pulseId,
    pulseName,
    columnId,
    columnType,
    columnTitle,
    valueLabel,
    previousValueLabel
  ]);

  // Mark as Success so we don't repeat processing
  sourceSheet.getRange(i + 1, 1).setValue("Success");

} catch (e) {
  Logger.log("Error processing row " + (i + 1) + ": " + e.message);
  sourceSheet.getRange(i + 1, 1).setValue("Error");
}

}
}

[Matt_Frost]

I don’t know why it’s plotting the code into text and a code box, but you’ll need to use the whole code