google-apps-scriptgoogle-sheetsimportmergeimportdata

How to copy data from a tab daily to another tab and add rows


At the moment we get requests from people to send them an e-mail with a travel deal they found on our website. We do this on a daily basis. We get the export in csv format (we download it to Google Sheets via =importdata). Every day at 5 AM the data is being renewed with the people that applied the last 24hours. The older data (before 24hours) is being removed.

What I want to do is make a copy of the data in tab1 of the spreadsheet to tab2 in the spreadsheet on a daily basis. When the new export arrives in tab1 I want the sheet to add the new data to tab2 (so we have a record of all requests in one tab).

I know that I can do this with App Script but I can't figure it out. Is there someone that knows a good trick for this? Please find the example sheet below:

enter image description here

enter image description here

Link to sheet: https://docs.google.com/spreadsheets/d/1TbhcGpsba-eu6rylN_fKqKPywMT4vd92Key9IQxRW70/edit?usp=sharing


Solution

  • The explanation can be found in the code itself:

    function copyData() {
      const ss = SpreadsheetApp.getActive();
    
      // get the source and target sheets
      const ssh = ss.getSheetByName("Exit Intent Daily");
      const tsh = ss.getSheetByName("Exit Intent All");
    
      //get the data from the source sheet
      const data = ssh.getRange("A2:K"+ssh.getLastRow()).getValues();
    
      //select the required columns (the index starts from 0)
      const fdata = data.map(r=>[r[0],r[2],r[3],r[4],r[10]]);
    
      //paste the data to the target sheet starting from the last row with content
      if(fdata.length>0){
        tsh.getRange(tsh.getLastRow()+1,1,fdata.length,fdata[0].length).setValues(fdata);
      }
    
    }