CODEWITHSUNDEEP
javascriptloopsgoogle-apps-script
arn
arn

Reputation: 1

Google script loop check for duplicate before writing data

I have a script which reads data from a site, stores the data in an array variable and then writes the data to a google sheet.

Per item id (JSON format), the data which is read is of the form:

[timestamp number text1 text2]

and these details are duplicated across different ids in a for loop.

What i'm left with on the sheet is per row (one item in each cell):

timestamp(id1) number1(id1) text1(id1) text2(id1) timestamp(id2) number1(id2) text1(id2) text2(id2) timestamp(id3) number1(id3)...etc

each row will contain only a single value for timestamp, however the timestamp variable is written multiple times. Is it possible to adapt my script to check column A of the bottom row on my sheet and only write the new row if the timestamp in the current bottom row is different to the timestamp in the new row about to be written.

for loop iterates through json file and stores data in "values" variable.

{
 {.....
  let values = [];
  values.push(timestamp, number1, text1, text2); //final line of for loop
 }
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test");
  var range = ss.getRange(3, 1, 1, values.length);  
  if (range.isBlank()) {  
    range.setValues([values]);
  } else {
    ss.appendRow(values);
  }
}

2 Requests:

a) I would like the timestamp variable to only be written once, in column A.

b) I would like the script to check the last written row to ensure that the timestamp value printed in column A is different to the value about to be written in the new row. If it is the same, do not write to the row.

Thanks

Upvotes: 0

Views: 576

Answers (1)

Neven Subotic
Neven Subotic

Reputation: 1429

So for Request A: You need to change the array you are passing to the setValues()method. If you already know which columns these are, then you can modify the array by replacing the existing value with an empty string.

const outputRow = [ … ]; // This is where your current output is set
const emptyTheseColumns = [3, 6, 9]; // columns C, F, I

const cleanedOutputRow = outputRow.map( (item, index) => {
  const column = index + 1; // columns start at 1, index at 0

  // return empty string for stated columns 
  if( emptyTheseColumns.indexOf( column ) != -1){
    return ""
  }
  // otherwise return the current value
  return item
});

// then use the new array in setValues( cleanedOutputRow )

Upvotes: 1

Related Questions