-1

I have a google spreadsheet that includes a couple of sheets that are dynamically maintained from a variety of sources. Call them Dynamic_data1 and Dynamic_data2.

For each of these dynamic sheets there is a companion static sheet. Let's call them Static_data1 and Static_data2.

I have a periodic need do the following for these two pairs of sheets, which I have been doing manually:

  1. Delete the entire contents of Static_data1
  2. Copy the entire contents of Dynamic_data1
  3. "paste special/paste values only" the values from Dynamic_data1 into Static_data1

Then repeat the process for Dynamic_data2/Static_data2

It's always the whole sheet, regardless of the number of rows or columns present.

Doing this manually works fine, but I find I have the need to automate it. I've done a little scripting, not much, so I am no expert.

I can't help but think this should be a simple job for a script. However, my inept googling has not located something I can adapt. I'd be grateful for advice.

0

Try something like this:

function copyAll() {
  const ss=SpreadsheetApp.getActive();//Spreadsheet with spreadsheet id's
  const sh=ss.getSheetByName('FromToList');//sheet with spreadsheet id's
  const idA=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues().map((r)=>{
    return {from:r[0],to:r[1]};//FromToList is a sheet with from id's in columnA and to id's in columnB
  });//tested down to here the rest is up to you.  I recommend not unleashing this on real spreadsheets until after you have thoroughly tested it.
  idA.forEach(obj=>{
    let fss=SpreadsheetApp.openById(obj.from);//from Spreadsheet
    let fshts=fss.getSheets();//from sheets
    let tss=SpreadsheetApp.openById(obj.to);//to Spreadsheet
    let tshts=tss.getSheets();//to sheets
    fshts.forEach(fsh=>{
      fsh.copyTo(tss);//copy all from sheets to to spreadsheet
    });
    tshts.forEach(tsh=>{
      tss.deleteSheet(tsh);//delete all original to sheets in to spreadsheet
    });
  });
}

Note: function has not be tested

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.