Update google “master” spreadsheet when other sheets are updated

1
votes

I have multiple Google spreadsheets with booking data such as booking number, client name, email, booking date etc. The order of these columns is not the same in all sheets.

I would like to update all data from all my "source" sheets in one "master" spreadsheet. Meaning as soon as a new row is added or an existing row is updated, the data will be synced to the master spreadsheet

What would be the best way to achieve that? Javascript or is there some existing Google Sheets addon?

Example sheet 1: Fast boat bookings

Example sheet 2: Airport transfer bookings

Master sheet

Thanks so much to everyone looking into this!

Most people recommend to use "importrange", but I don't think this works for my use case.

I am also aware that it could be achieved by Zapier, but it would become to costly to pay for so many zaps. I believe there is another solution.

I do not have any code yet to start with :-/

I expect the data in the master sheet to be sorted by submission date and time like this:

2
google-apps-scriptgoogle-sheets
How are your rows in the source sheets being added/updated? Are they being filled in manually cell by cell or populated by a form/script? – ziganotschka
Welcome. Have you tried to use IMPORTRANGE? Why do you think that it doesn't work work for your use case? Have you already read the "Using arrays in Google Sheets" on the Google Docs Editors help center? – Rubén
@Christian Eiermann:If your rows are populated automatically, addition of new contents will not fire a trigger to run the script automatically. You will need a workaround including IMPORTRANGE as suggested by Ruben, since IMPORTRANGE will also trigger for automatic updates – ziganotschka
It's not allowed to offer payments / hiring posts / comments but you could look at your favorite community members profiles to see if they included contact details or instructions to contact / hire them. Regarding allowing to edit spreadsheets shared on this site usually it's not a good idea as the spreadsheet could be changed in such way that make them irrelevant for the question beside they could be abused / trolled. – Rubén
If you desist to help help from this community, please delete your post, but if you would like to continue, I suggest you to edit the question to share a brief description of your search/research efforts and to change the sharing settings of your spreadsheets from edit to view. – Rubén

2 Answers

0
votes

The general procedure for using formulas is

  1. Use IMPORTRANGE to get the data from the source spreadsheets into the master spreadsheet
  2. Use array notation to put the imported data together

NOTES:

If you are new to using IMPORTRANGE, arrays in Google Sheets and complex formulas, use one sheet for each IMPORTRANGE, and delete the unused columns to save cells because the Google Sheets 5 million cell limit

If you prefer to use scripts, you should get the spreadsheets keys, or URLs then you could use SpreadsheetApp.openById(id) or SpreadsheetApp.openByUrl(url) to open the spreadsheets. Then you could use getValues() / setValues() to read / write the values from source spreadsheets to the master spreadsheet.

References

Related

0
votes

As a workaround for Apps Script triggers not being fired by automatic sheet updates, you need to use IMPORTRANGE to import your data into a dummy sheet. IMPORTRANGE will detect also through automatic sheet update and simultaneously it is able to fire an onEdit trigger.

Do the following:

  1. Delete all empty rows in MASTER spreadsheet

  2. Create a dummy spreadsheet and import into it the contents of MASTER spreadsheetwith a formula: enter image description here

  3. From the dummy sheet, open the script editor and insert the following code:

function changed(e) {
  var masterSheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  if(PropertiesService.getScriptProperties().getKeys().length==0){
     PropertiesService.getScriptProperties().setProperty('startRow', 5);
  }
  var startRow=PropertiesService.getScriptProperties().getProperty('startRow');
  var lastRow=masterSheet.getLastRow();
  var numRows=lastRow-startRow+1;
  var startCol=1;
  var numCols=6;
  var values=masterSheet.getRange(startRow,startCol,numRows,numCols).getValues();

  var FBsheet=SpreadsheetApp.openById('1SzBx5Q9rrlcLGSqD8y5eFE5TX304LfZ7D9mxxrHhfKw').getSheetByName('Sheet1');
  var ATsheet=SpreadsheetApp.openById('1IRD8wT5Kmx7h_xP807f4ibWRn8g98RjA-dJXxADXAl0').getSheetByName('Sheet1');

  FBsheet.getRange(FBsheet.getLastRow()+1,startCol, numRows, numCols).setValues(values);
  var ATlastRow=ATsheet.getLastRow();
  for(var i=0;i<numRows;i++){
    Logger.log(values[i][1]);
    ATsheet.getRange(ATlastRow+1+i,1, 1, 1).setValue(values[i][1]);
    ATsheet.getRange(ATlastRow+1+i,2, 1, 1).setValue(values[i][0]);
    ATsheet.getRange(ATlastRow+1+i,3, 1, 1).setValue(values[i][3]);
    ATsheet.getRange(ATlastRow+1+i,4, 1, 1).setValue(values[i][2]);
    ATsheet.getRange(ATlastRow+1+i,5, 1, 1).setValue(values[i][5]);
    ATsheet.getRange(ATlastRow+1+i,6, 1, 1).setValue(values[i][4]);
  }  
  PropertiesService.getScriptProperties().setProperty('startRow',  lastRow+1);
}
  1. Attach to the script an onEdit trigger
  2. Run the script once manually, it is normal that it will throw you an error
  3. Now the script will run automatically each time data update takes place in MASTER spreadsheet.

Make sure you change all spreadsheetIDs and sheet names with your values.