I have spreadsheet1 with all the details as in this image(Spreadsheet1), there are columns with startDate and endDate with some dates. Now i have a different spreadsheet2 like in this image (spreadsheet2) with header row of all the dates in the year (from 01/01/2021 to 31/12/2021). Now startDate and endDate from spreadsheet1 should match the header in spreadsheet2 and put the values of the column Type from spreadsheet1 to the respective cells in spreadsheet2 (like it is present in spreadsheet2 image for reference). Below is the code i'm working with but i'm not reaching my goal. Please help me i'm new to coding world. Thank you.

function myFunction() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let last_row = sheet.getLastRow();
  let data = sheet.getRange("A2:E" last_row).getValues();

  let start_date = [];
  let end_date = [];
  let dates_between = [];
  let id = [];
  let name = [];
  let message = [];

  let dd = SpreadsheetApp.openById('1z5WB1sACp1zvgfyXDbAmYxklSZOMIC8kNi_3Yci-PkM');
  let dsheet = dd.getActiveSheet();
  let dlast_row = dsheet.getLastRow();
  let ddata = dsheet.getRange('C2:NC' dlast_row).getValues();
  let did = dsheet.getRange('A2:A' last_row);

  for(let i = 0; i<data.length;i  ){
    // let id = data[i][0];
    id.push(data[i][0]);
    name.push(data[i][1]);
    start_date.push(data[i][2]);
    end_date.push(data[i][3]);
    message.push(data[i][4]);
    dates_between.push(DATES_BETWEEN(start_date[i], end_date[i]));
  }

  did.setValue(id);
  
}
function DATES_BETWEEN(dateFrom, dateTo) {
  var t = dateFrom.getTime(),
      tMax = dateTo.getTime(),
      values = [];
  while (t <= tMax) {
    values.push(new Date(t));
    t  = 24000 * 3600;
  }
  return values;
}

CodePudding user response：

If you're able to put your 'types' on the row at start date and at end date you can fill the gap in-between with Array.fill() method.

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/fill

All you need is to get the row as an array and to put it back on the sheet. You don't even need to calculate dates in the gap. You just fill all these empty elements between filled start and end cells.

Here is your row/array: ['','','x','','','x','','']

Start cell is array.indexOf('x')

End cell is array.lastIndexOf('x')

To fill the gap with 'x' strings use array.fill('x', start, end)

You will get: ['','','x','x','x','x','','']

Below is my solution that doesn't use Dates. If your dates have the same format on both sheets and if your destination sheet always has the dates of the rows of your source sheet you can consider them as strings, and use them as keys of an object (a map in my case):

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var src_sheet = ss.getSheetByName('Sheet1');
  var dest_sheet = ss.getSheetByName('Sheet2');

  // get dates from first row of destination sheet
  var dates = dest_sheet.getDataRange().getDisplayValues()[0].slice(2);
  
  // get rows (without first row) from source sheet
  var rows = src_sheet.getDataRange().getDisplayValues().slice(1);

  // loop through the rows and get the table
  var table = rows.map(row => {
    
    // get variables from the row
    var [id, name, type, start, end] = row.slice(0, 5);

    // create empty Map with dates-keys (date1:'', date2:'', ...)
    var dates_map = new Map(dates.map(date => [date, '']));
  
    // assign 'type' to key['start date'] and to key['end date']
    dates_map.set(start, type).set(end, type);

    // create array (row) from values of the Map
    var row_array = Array.from(dates_map.values());

    // fill empty elements of the array between first and last 'type'
    row_array.fill(type, row_array.indexOf(type), row_array.lastIndexOf(type));

    // return row
    return [id, name, ...row_array];
  });

  // set the table on the destination sheet
  dest_sheet.getRange(2, 1, table.length, table[0].length).setValues(table);
}

The same code without comments:

function myFunction() {
  var ss         = SpreadsheetApp.getActiveSpreadsheet();
  var src_sheet  = ss.getSheetByName('Sheet1');
  var dest_sheet = ss.getSheetByName('Sheet2');
  var dates      = dest_sheet.getDataRange().getDisplayValues()[0].slice(2);
  var rows       = src_sheet.getDataRange().getDisplayValues().slice(1);

  var table = rows.map(row => {
    var [id, name, type, start, end] = row.slice(0,5);
    var dates_map = new Map(dates.map(date => [date,'']));
    dates_map.set(start, type).set(end, type);
    var row_array = Array.from(dates_map.values());
    row_array.fill(type, row_array.indexOf(type), row_array.lastIndexOf(type));
    return [id, name, ...row_array];
  });

  dest_sheet.getRange(2, 1, table.length, table[0].length).setValues(table);
}

Just in case, this:

var [id, name, type, start, end] = row.slice(0,5);

is another way to write:

var id    = row[0];
var name  = row[1];
var type  = row[2];
var start = row[3];
var end   = row[4];

Here is the link to my dummy spreadsheet.