Objectif: when importing new values i need to keep tracks of old values in a new spreadsheet by adding the snapshot with the date and time of the export.

I have an array (colomn A:C) below to export in a other spreadsheet (will stay the same):

in the scenario for the exported spreadsheet below, I would like a script doing this similar information exported based on the date of the export :

1 is the previous export 2 is the new export that goes in the next column after the previous export with the now date. 3 the next exported values 4 the values exported from column C depending on when the export have been done.

The model file is here

Thank you for your help

CodePudding user response：

Solution:

• Get the list of source cities and of previously exported cities, using getValues.
• Iterate through the source cities, finding the row index to which is corresponds in the target sheet (appending the city if it doesn't already exist in the target sheet).
• Export the data via setValues.

Code sample:

const FIRST_ROW = 2;

function exportData() {
  const ss = SpreadsheetApp.getActive();
  const source = ss.getSheetByName("data");
  const target = ss.getSheetByName("exported");
  const sourceValues = source.getRange("A2:C"   source.getLastRow()).getValues();
  const currentExportedCities = target.getRange("A2:B"   target.getLastRow()).getValues();
  let exportedPopulation = new Array(currentExportedCities.length).fill("");
  for (let i = 0; i < sourceValues.length; i  ) {
    const [control, city, population] = sourceValues[i];
    let cityIndex = currentExportedCities.map(c => c[1]).indexOf(city);
    if (cityIndex < 0) {
      currentExportedCities.push([control,city]);
      cityIndex = currentExportedCities.length-1;
    }
    exportedPopulation[cityIndex] = population;
  };
  target.getRange(FIRST_ROW, 1, currentExportedCities.length, currentExportedCities[0].length).setValues(currentExportedCities);
  exportedPopulation.unshift(new Date());
  target.getRange(1, target.getLastColumn()   1, exportedPopulation.length).setValues(exportedPopulation.map(p => [p]));
}

CodePudding user response：

Something that i did not mention is that I need to capture column A as well that will have data for each week that will be added during the year. Therefore, we will have week 02 in column A with same town or additional town in column B I tried to add the column on yuor script

const sourceValues = source.getRange("A2:C"   source.getLastRow()).getValues();

const currentExportedCities = target.getRange("A2:B"   target.getLastRow()).getValues().flat();

but not sure how to manage for adding the week in first_row and town in seconde row:

target.getRange(FIRST_ROW, CITY_COL, currentExportedCities.length).setValues(currentExportedCities.map(c => [c]));

thanks for your help