Looking to enhance my current workaround for utilizing Google Spreadsheet's ImportRange feature

Question

Looking to enhance my current workaround for utilizing Google Spreadsheet's ImportRange feature

I recently joined this forum and have just started using Google Scripts. I don't have any prior experience with JavaScript or programming languages, but had to learn them when I decided to use Google Apps as the main platform for my small business.

My issue lies in the limitation of the ImportRange function in Google Sheets to 50 on each spreadsheet. I've set up a model where each customer has their own spreadsheet with personal data, deadlines, etc., stored in their individual folder on Google Drive.

In addition, I've created a spreadsheet called "Organizer" which serves two functions -

1) automatically generates correspondence using the autoCrat script, 2) displays deadlines for each customer and sorts them by priority.

Therefore, I need to share/import/copy data from all customer spreadsheets into the "Organizer". Since there are more than 50 customer spreadsheets, I had to abandon the ImportRange function. Instead, I'm using a simple script to directly copy files from every spreadsheet:

function ImportDataRange() {

// row number.1
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Database");
var range = sheet.getRange(2, 1)
var id = range.getValue()
var ssraw = SpreadsheetApp.openById(id);
var sheetraw = ssraw.getSheetByName("Raw");
var range = sheetraw.getRange("A2:AB2");
var data = range.getValues();

sheet.getRange("B2:AC2").setValues(data)

// row number.2
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Database");
var range = sheet.getRange(3, 1)
var id = range.getValue()
var ssraw = SpreadsheetApp.openById(id);
var sheetraw = ssraw.getSheetByName("Raw");
var range = sheetraw.getRange("A2:AB2");
var data = range.getValues();

sheet.getRange("B3:AC3").setValues(data)
}

The script works well, but the issue arises when I try to add a new customer spreadsheet to the "Organizer" using this method. I have to manually add a new copy of the entire code for each new row and also adjust the output range of imported data and the location of the source file ID in the "Organizer".

Does anyone know of a workaround that would make it easier/automatic to add new rows/customer data?

Thank you for your assistance!

javascript google-apps-script

Answer 1

Answer №1

If you're looking to automate importing data from multiple sheets, a loop can be very helpful. While I haven't tested the code provided below, it seems like it should work based on your requirements.

function ImportOtherSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Databaze");

  // Retrieve IDs from the first column
  var sheetIds = sheet.getRange(2, 1, sheet.getLastRow() - 1);

  // Iterate over each ID in the column
  for (var i = 0; i < sheetIds.length; i++) {
    var id = sheetIds[i]; // Get the ID from the array

    // Access the new sheet, range, and values
    var ssraw = SpreadsheetApp.openById(id);
    var sheetraw = ssraw.getSheetByName("Raw");
    var range = sheetraw.getRange("A2:AB2");
    var data = range.getValues();

    // Write the values to the local range
    sheet.getRange(i + 1, 2, 1, data[0].length).setValues(data)
  }
}

As you delve deeper into Google Apps Script (GAS) and JavaScript, make sure to utilize resources like MDN and the GAS Documentation for further learning.

Answer 2

If you're looking to automate importing data from multiple sheets, a loop can be very helpful. While I haven't tested the code provided below, it seems like it should work based on your requirements.

function ImportOtherSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Databaze");

  // Retrieve IDs from the first column
  var sheetIds = sheet.getRange(2, 1, sheet.getLastRow() - 1);

  // Iterate over each ID in the column
  for (var i = 0; i < sheetIds.length; i++) {
    var id = sheetIds[i]; // Get the ID from the array

    // Access the new sheet, range, and values
    var ssraw = SpreadsheetApp.openById(id);
    var sheetraw = ssraw.getSheetByName("Raw");
    var range = sheetraw.getRange("A2:AB2");
    var data = range.getValues();

    // Write the values to the local range
    sheet.getRange(i + 1, 2, 1, data[0].length).setValues(data)
  }
}

As you delve deeper into Google Apps Script (GAS) and JavaScript, make sure to utilize resources like MDN and the GAS Documentation for further learning.

Answer 3

Answer №2

After studying fooby´s code and spending a considerable amount of time experimenting, I finally managed to create a working code that suits my needs. For those who are curious, here is the code:

function DisplayDataFromOtherSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Database");

var ids = sheet.getRange(2, 1, sheet.getLastRow() - 1).getValues();

for (var i = 0; i < ids.length; i++) {
var id = ids[i];

var ssraw = SpreadsheetApp.openById(id);
var sheetraw = ssraw.getSheetByName("Raw");
var range = sheetraw.getRange("A2:AB2");
var data = range.getValues();

sheet.getRange(i + 2, 2, 1, data[0].length).setValues(data)

    }
}

A special thanks to fooby for their assistance - I couldn't have done it without you!

Answer 4

After studying fooby´s code and spending a considerable amount of time experimenting, I finally managed to create a working code that suits my needs. For those who are curious, here is the code:

function DisplayDataFromOtherSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Database");

var ids = sheet.getRange(2, 1, sheet.getLastRow() - 1).getValues();

for (var i = 0; i < ids.length; i++) {
var id = ids[i];

var ssraw = SpreadsheetApp.openById(id);
var sheetraw = ssraw.getSheetByName("Raw");
var range = sheetraw.getRange("A2:AB2");
var data = range.getValues();

sheet.getRange(i + 2, 2, 1, data[0].length).setValues(data)

    }
}

A special thanks to fooby for their assistance - I couldn't have done it without you!

Looking to enhance my current workaround for utilizing Google Spreadsheet's ImportRange feature

Answer №1

Answer №2

Similar questions

Troubleshooting the Issue of Table Append not Functioning in Live Environment

Implementing Conditional Display of Span Tags in React Based on Timer Duration

Events in d3.js are properly registered, however they are not being triggered as

What are the appropriate levels of access that an operating system should provide for web-based scripting?

Interact with a webpage element using Selenium

Priority is given to strings over numbers

Simulated function for handling express functions

Transmitting the User's Geographic Location and IP Address Securely in a Hidden Input Field

Issue: The data type 'void' cannot be assigned to the data type 'ReactNode'

Error: The index "id" is not defined

Upgrade from using fetch to utilize await in order to achieve the same outcome

I am looking to develop a unique event that can be triggered by any component and listened to by any other component within my Angular 7 application

Is there a way to determine the quantity of lines within a div using a Vue3 watcher?

Show spinner until the web page finishes loading completely

Updating backgroundPosition for dual background images within an HTML element using Javascript

It appears that when filtering data in Angular from Web Api calls, there is a significant amount of data cleaning required

What is the best way to iterate over an array of objects?

Ways to implement functional component in ReactJs

Is the for loop in Node.js completed when making a MySQL call?

Creating Eye-Catching Images by Incorporating Image Overlays Using Just One Image