Make a copy of google sheet into google drive folder

Question

I have two Spreadsheets. The first spreadsheet contains my raw data that indicates the employee # and name of employees. The second spreadsheet is the spreadsheet I want to copy into a google drive folder. I want to update the specific fields on the 2nd spreadsheet based on the employee number and employee name from the 1st spreadsheet. Everytime it updates the cells in the second spreadsheet, it will create copy of the 2nd spreadsheet into google drive folder.

However, it keeps on setting just one value inside the replicated spreadsheets. It doesn't loop the employee names and employee numbers from the 1st spreadsheet.

My code is already replicating the 2nd spreadsheet. It's just the values aren't updating.

 function replicateCards() {
  var ss = SpreadsheetApp.openById('xxxxxxxx');
  var copyCard = SpreadsheetApp.openById('zzzzzzzzz');
  var getID = DriveApp.getFileById(copyCard.getId())
  var card = copyCard.getSheetByName("Card");
  var mastersheet = ss.getSheetByName("Mastersheet");
  var getLastRow = mastersheet.getLastRow();
  var destinationFolder = DriveApp.getFolderById('yyyyyyyyyy');
  ;
  var changeColorToGrayList = card.getRangeList(['C7', 'E7', 'G7', 'I7', 'K7', 'M7', 'O7', 'Q7',
                                                 'C9', 'E9', 'G9', 'I9', 'K9', 'M9', 'O9', 'Q9',
                                                 'C11', 'E11', 'G11', 'I11', 'K11', 'M11', 'O11', 'Q11']);
  var setValueToZero = card.getRangeList(['C8', 'E8', 'G8', 'I8', 'K8', 'M8', 'O8', 'Q8',
                                          'C10', 'E10', 'G10', 'I10', 'K10', 'M10', 'O10', 'Q10',
                                          'C12', 'E12', 'G12', 'I12', 'K12', 'M12', 'O12', 'Q12']);

  for (i = 1; i < getLastRow; i++) {

    var employeeNumber = mastersheet.getRange(i + 1, 1).getValue();
    var employeeName = mastersheet.getRange(i + 1, 2).getValue();
    card.getRange("C3").setValue(employeeName);
    card.getRange("H3").setValue(employeeNumber);
    card.setActiveRangeList(changeColorToGrayList).setBackground("gray");
    card.setActiveRangeList(setValueToZero).setValue(0);
    //    var getID = DriveApp.getFileById(card).getId(); 
    getID.makeCopy(employeeNumber + " High Flyer Card", destinationFolder);
  }
}

I expect the output of getID.makeCopy(employeeNumber + " High Flyer Card", destinationFolder); contains different employee # and employee names, not just one value inside the google folder.

Shouldn't you use: `var getID = DriveApp.getFileById(card.getId());` instead of `var getID = DriveApp.getFileById(***copy***Card.getId());`? — a-burge, Oct 15 '19 at 12:50
Hi @a-burge. Sorry. I got confused. can you elaborate? I've updated the code also for your reference. =) — architect_mosby017, Oct 16 '19 at 09:20

ziganotschka · Accepted Answer · 2019-10-16T12:40:08.907

1

Your code should work as intended, unless a bulky file makes the code overlap.

If so, implementation of flush() will make your code run sequentially, see here for a detailed explanation.

In your case, modifying the for loop to

  for (i = 1; i < getLastRow; i++) {
    var employeeNumber = mastersheet.getRange(i + 1, 1).getValue();
    var employeeName = mastersheet.getRange(i + 1, 2).getValue();
    card.getRange("C3").setValue(employeeName);
    card.getRange("H3").setValue(employeeNumber);
    card.setActiveRangeList(changeColorToGrayList).setBackground("gray");
    card.setActiveRangeList(setValueToZero).setValue(0);
    getID.makeCopy(employeeNumber + " High Flyer Card", destinationFolder);
    SpreadsheetApp.flush();  
  }

should solve the issue.

edited Oct 16 '19 at 12:40

answered Oct 15 '19 at 12:50

ziganotschka

25,866
2
16
33

my intent is to actually override the values in C3 and H3 cells then copy the spreadsheet into a google drive folder for each loop. So let's say I have 2 records from Mastersheet. I should have 2 replicated cards in my google drive folder with different employee number and employee name. Apologies. I'm still new in using Apps Script. =) – architect_mosby017 Oct 16 '19 at 03:24
Sorry, I misunderstood your intention. Could you please provide your complete code, where card, copyCard etc. is defined, so it would be easier to troubleshoot? – ziganotschka Oct 16 '19 at 07:35
Apologies also for the confusion. I've updated the code now @ziganotschka. What should happen are: 1. Make a loop that overrides the employee # and employee name. The values are from the raw data. 2. Make a copy of the card spreadsheet everytime it changes/overrides the employee # and employee name. 3. save it to a google drive folder. So if I have 2 records in my raw data, I should have 2 copies of card spreadsheet also with different employee # and name. Hope it's more clear now. Thanks for your help bro =) – architect_mosby017 Oct 16 '19 at 09:05
I ran your code and it works as intended for me. Maybe there is a problem with your spreadsheet. I can have a look, if you share. Please make sure to sanitize it first and not provide any private information. – ziganotschka Oct 16 '19 at 10:40
Here's the copy of raw data https://docs.google.com/spreadsheets/d/1AG-J6W2qW6SLHpaB_Mzwfuk1LNwkhTd-d1mpEkUxCag/edit?usp=sharing and the copy of the spreadsheet i want to replicated https://docs.google.com/spreadsheets/d/14u5Ukwl1B_Guo0V9Cs0OtYv4zzPzb7uDKulMqIbOK3k/edit?usp=sharing I keep on getting the last value only from my raw data when it replicates it to my folder. – architect_mosby017 Oct 16 '19 at 12:11
is there a way i can delay the next iteration? My initial thought here is the iteration and making a copy is overlapping. – architect_mosby017 Oct 16 '19 at 12:17
You'd need to give me access to the files. Your thought is very correct, it can happen indeed if your files are bulky. You can use `flush()` to force a sequential execution https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#flush(). Also `sleep()` is useful: https://developers.google.com/apps-script/reference/utilities/utilities#sleepmilliseconds – ziganotschka Oct 16 '19 at 12:21
I tried to but our IT restricts an access outside our network :( that's so sad. can you send here where should i put the code you mentioned? – architect_mosby017 Oct 16 '19 at 12:28
1

Hi @ziganotschka. it seems that the flush() solves the issue!!! THANK YOUUUU!! I'm testing it now if it works if to many records also! – architect_mosby017 Oct 16 '19 at 12:36

Make a copy of google sheet into google drive folder

1 Answers1