Script to replace content and formulas in many other spreadsheets

Question

I have a script that allows me to import data from a multitude of "child" spreadsheets to a "parent" spreadsheet. Each child is composed of many tabs, but all the data that I import is unified thanks to queries in a single sheet of the child ("datasheet").

Problem is: if in the future I want to modify something (for example import more data from the childs), i would then have to go and edit manually every single child datasheet.

I'd rather have a script that would do the following:

In my "Parent", i would create a "datasheet" which would be the template datasheet.
The script would copy the "content" (and the formulas) of the parent datasheet.
It would paste this content+formulas in all the spreadsheets specified (in my Parent, i already have a list of URLs - one for each child).

I don't want to "copy" the parent datasheet, i'd rather replace content. Is this even possible?

Thank you in advance for your help!

Hi there @Kev! I can't fully understand your issue. I understand that you want to copy the Parent sheet (both content and formulas) into a list of sheets, but in your last paragraph you said that you don't want to copy that sheet. Could you please clarificate it for me? Also, please share your code so we all can take a look at it. — Jacques-Guzel Heron
Hi Jacques. Thank you for your comment. What my last sentence meant was that I don't want a script that duplicates the Parent datasheet into another spreadsheet (because it would create a new sheet each time). I want the script to replace the content of an already existing sheet of the Child Spreadsheet. Unfortunately i don't have a code to share cause i'm not even sure it is possible (i know it's possible to create a copy of a sheet into another spreadsheet, but not if it can replace the content, formulas included). — Kev
Hey @Kev! It is possible to replace the content of an already existing sheet instead of creating a new one each interaction. I now understand your question better. Do you want the script to copy the values and formulas of all the cells of the parent sheet? If that is not the case, please share an example spreadsheet with dummy data so I can help you better. — Jacques-Guzel Heron
Hi @Jacques-GuzelHeron Thank you again for your help! Yes, all the values/formulas of the parent sheet. It should be an exact copy of the parent sheet. — Kev

Jacques-Guzel Heron Jacques-Guzel Heron · Accepted Answer · 2020-02-11T08:20:52

Based on our conversation on comments, I comprehend that you want to copy all data from the Parent sheet into the specified sheets. I'll assume that the list of sheets is located in the upper left corner of the Parent sheet. If that isn't the case, please forgive me and indicate to me where the sheet list is located. This is the code that fulfills your request:

function sheetPopulator(spreadsheetID) {
  var masterSheet = SpreadsheetApp.openById(spreadsheetID).getSheetByName(
    "Parent");
  var specifiedSheets = masterSheet.getRange(2, 1, masterSheet.getLastRow(), 1)
    .getValues();
  var lastRowMasterSheet = masterSheet.getLastRow();
  var lastColumnMasterSheet = masterSheet.getLastColumn();
  var allData = masterSheet.getRange(1, 1, masterSheet.getLastRow(), masterSheet
    .getLastColumn()).getValues();

  for (var i = 0; i < specifiedSheets.length; i++) {
    if (SpreadsheetApp.openById(spreadsheetID).getSheetByName(specifiedSheets[
        i]) != null) {
      SpreadsheetApp.openById(spreadsheetID).getSheetByName(specifiedSheets[i])
        .getRange(1, 1, lastRowMasterSheet, lastColumnMasterSheet).setValues(
          allData);
    }
  }
}

That function will first gather the list of specified sheets and after that will read all the data from the Parent sheet. After that, the code will iterate over every element of the list and, if that element coincides with a sheet name, it will copy all the previously read data into that sheet.

Please, take this as one of many possible solutions to your issue. Don't hesitate to ask me for clarifications or further help.

UPDATE

I apologize if the previous script doesn't fulfill your requests. After reading your new comments I studied your spreadsheets and designed a new code. I have tested this new script on copies of your spreadsheets and it works flawlessly. This is the code in question:

function sheetPopulatorII() {
  var parentSheetID = "{PARENT SHEET ID}";
  var childrenListSheet = SpreadsheetApp.openById(parentSheetID).getSheetByName(
    "Childs");
  var childrenList = childrenListSheet.getRange(2, 1, childrenListSheet
    .getLastRow() - 1, 1).getValues();

  for (var i = 0; i < childrenList.length; i++) {
    childrenList[i][0] = childrenList[i][0].toString().substring(39, 83);
  }

  for (var i = 0; i < childrenList.length; i++) {
    var children = SpreadsheetApp.openById(childrenList[i][0]);

    children.getSheets()[0].setName("OLD SHEET");
    SpreadsheetApp.openById(parentSheetID).getSheetByName("Master").copyTo(
      children).setName("Data");
    children.deleteSheet(children.getSheetByName("OLD SHEET"));
  }
}

I am going to explain this code step by step. First, it opens the children list sheet (inside the Parent Sheet) using .openById() for opening the Parent spreadsheet, .getSheetByName() to open the involved sheet, .getRange() for selecting the list, .getLastRow() to know how long the list is (so you can add new URLs on the future using the same code) and .getValues() to gather the data.

Afterwards the code will iterate the list to convert the URLs into IDs with .substring() to cut away the non-ID parts. Next, it will iterate the list again but this time it will rename the old sheet with a temporal name using .setName(), copy the Parent sheet with .copyTo() and delete the renamed sheet with .deleteSheet(). This approach copy both: values and formulas. Please, contact me again if you need further help developing the code or understanding it.

Script to replace content and formulas in many other spreadsheets

1 Answers

UPDATE