I have this situation:

• a form response destination tab called 'GIORNALIERA', in spreadsheet file 1, with the original data, that i get from submissions on Google Forms. This data will change, with new rows added through new form response submissions every day. From time to time, rows are also manually deleted in this tab.

• a tab called 'Foglio13' in spreadsheet file 2, in which i need to append the data from the original sheet, and keep that data even if it gets deleted from the source

• another tab in spreadsheet file 2, in which i need to append all the rows if the first column in the source contains a specific name, and as above, keeps that data even if it gets deleted from the source. The output, for example, for Luke Skywalker would be this:

This is the current code that i'm using, but it has one issue. If i run the function again it appends all the data, but i want to append only new data that hasn't been appended before

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("PANORAMICA");
  var pasteSheet = ss.getSheetByName("Foglio13");

  var rows = copySheet.getDataRange().getValues(); // Gets the rows with data

  rows.map(row => pasteSheet.appendRow(row)); // Appends the rows to the second sheet
}

CodePudding user response：

To copy form responses from the form responses tab to another tab as they are submitted, use an installable trigger.

/**
* Installable trigger that runs each time a form response is submitted.
*
* Create an installable "on form submit" trigger to run this function:
* https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually
*
* @param {Object} e The "on form submit" event object.
*/
function appendDataOnFormSubmit(e) {
  'use strict';
  if (!e) {
    throw new Error(
      'Please do not run the appendDataOnFormSubmit(e) function in the script editor window. '
        'It runs automatically when a form response is submitted.'
    );
  }
  let archiveSs = SpreadsheetApp.openById('..put target spreadsheet ID here...');
  let archiveSheet = archiveSs.getSheetByName('Foglio13');
  if (!archiveSheet) {
    archiveSheet = archiveSs.insertSheet(appendRowsToArchiveSheetSettings.targetSheet);
    const formResponsesSheet = e.source.getSheetByName('GIORNALIERA');
    const headers = formResponsesSheet.getRange('A1:1').getValues().flat();
    archiveSheet.appendRow(headers);
  }
  const newRowData = e.range.getValues().flat();
  archiveSheet.appendRow(newRowData);
}

To create additional tabs for each employee that show only "their" subset of the archived data, use =query() formulas, like this:

=query(Foglio13!A1:Z, "where B = 'Luke Skywalker' ", 1)

Alternatively, use filter views to show one employee at a time directly in the archive sheet.

CodePudding user response：

If you need to copy all data to another sheet, instead of copying the data, you can just copy the sheet itself.

The following functions will copy the source sheet to the target spreadsheet, and rename that sheet as your target sheet name, if a sheet with shuch name already exists, it will be deleted.

// if the source sheet and the target sheet is in the different spreadsheet:
function copySheet() {
  const ss = SpreadsheetApp.openById("Source SpreadSheet ID");
  const source = ss.getSheetByName("Source Sheet Name");
  const target = ss.getSheetByName("Target Sheet Name");
  if(!!target) ss.deleteSheet(target);
  source.copyTo(ss).setName("Target Sheet Name");
}

// if the source sheet and the target sheet is in the same spreadsheet:
function importSheet() {
  const sss = SpreadsheetApp.openById("Source SpreadSheet ID");
  const tss = SpreadsheetApp.openById("Target SpreadSheet ID");
  const source = sss.getSheetByName("Source Sheet Name");
  const target = tss.getSheetByName("Target Sheet Name");
  if(!!target) tss.deleteSheet(target);
  source.copyTo(tss).setName("Target Sheet Name");
}