Making Changes to Google Docs Table

[kelo...@sewanee.edu]

Hi Everyone,

The purpose of my project is to create a . pdf company directory with a specific format.  I have a csv file that is being dropped into GoogleDrive. I am importing the data from that csv file into a GoogleSheet. I then have a script pulling the data from the GoogleSheet into a table in GoogleDocs. In the table, I have a few criteria: 1. I want to hide the borders of the table, 2. the values in columns 1 and 2 should only appear once in each column (e.g. if I have 20 people in Athletics, the word Athletics in column 1 should only appear once such that all of the employees in the department appear to be nested under Athletics. If I have 5 people in Athletics (Column 1) Administration(Column 2),  the terms Athletics and Administration should only appear once in each respective column),  and 3. if the cells in columns 2, 3, or 4 of a row are empty (there is no value), I want to change the bottom border to 1pt dotted. 

I am encountering two issues. First, my script for recognizing unique values seems to only work for the first page or so. It then hides all data from column 1 for the remaining rows. I am not sure why it is failing. Second, I cannot get the setBorder function to work. properly. I receive TypeError: cell.setBorder is not a function

Any help that you can provide would be greatly appreciated. Of course, if there is a more efficient way to achieve my goal, I welcome your suggestions. Here is my code in it's entirety: 

function importDataFromSheetToDoc() {

var sheet = SpreadsheetApp.openById("XX");

 

var doc = DocumentApp.openById('XX');

 

var sheetData = sheet.getSheets()[0].getDataRange().getValues();

 

var body = doc.getBody();

 

var table = body.appendTable([]);

 

 able.setBorderWidth(0);

 

  // Initialize arrays to keep track of unique values in columns 1 and 2

  var uniqueColumn1Values = [];

  var uniqueColumn2Values = [];

 

  // Set the batch size for processing

  var batchSize = 100;

 

  // Loop through each row in the sheet data

  for (var i = 0; i < sheetData.length; i++) {

    var rowData = sheetData[i];

   

    // Check if column 1 value is unique

    if (uniqueColumn1Values.indexOf(rowData[0]) === -1) {

      uniqueColumn1Values.push(rowData[0]);

    }

   

    // Check if column 2 value is unique

    if (uniqueColumn2Values.indexOf(rowData[1]) === -1) {

      uniqueColumn2Values.push(rowData[1]);

    }

   

    // Add a new row to the table and include all columns

    var newRow = table.appendTableRow();

    for (var j = 0; j < rowData.length; j++) {

      newRow.appendTableCell(rowData[j]);

    }

   

    // Check if any cell in columns 1 or 2 is empty and set the background color to white

    if (rowData[0] === '' || rowData[1] === '') {

      for (var k = 0; k < 2; k++) {

        if (rowData[k] === '') {

          newRow.getCell(k).setBackgroundColor('#ffffff'); // White color

        }

      }

    }

   

    // If a duplicate value is found in columns 1 or 2, clear the cell's content

    if (uniqueColumn1Values.indexOf(rowData[0]) !== i) {

      newRow.getCell(0).setText('');

    }

    if (uniqueColumn2Values.indexOf(rowData[1]) !== i) {

      newRow.getCell(1).setText('');

    }

  if (rowData[1] === '' || rowData[2] === ''|| rowData[3] === '') {

      // Set the bottom border of the cell to 1pt with dots

      var cellIndex = rowData[1] === '' ? 1 : 3;

      var cell = lastRow.getCell(cellIndex);

      cell.setBorder(null,null,true,null,null,null,"black",SpreadsheetApp.BorderStyle.DOTTED);

        // Check if we have processed a batch and sleep briefly to avoid script execution time limits

    if ((i + 1) % batchSize === 0) {

      Utilities.sleep(500); // Sleep for 500 milliseconds

    }

  }

}

}

Thank you!

Kerline

[Keith Andersen]

May I ask how many people are in this company directory?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c88c9394-4cf2-4a5e-ae0d-3e3360e4e6f2n%40googlegroups.com.

[Kerline Lorantin]

As of today, I have 964 lines. I've been using a partial directory with 198 lines for testing purposes.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/GI8yTrlZifQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4wPb93cC7Os2edYLxmFsHg1mKL%3Da7LBUvOYrmMyH_5Z4g%40mail.gmail.com.

--

Kerline Lorantin, PMP

Associate Director, Enterprise Applications

Strategic Digital Infrastructure

The University of the South

Sewanee, TN 37383

Email: kerline....@sewanee.edu

Office: 931.598.1826

[Keith Andersen]

Would you consider instead of a.. border changing the color of the cells that are empty to a very light gray? I'm just thinking that doing so might be easier to code.

In any event, I'll get working on a solution.

How many classifications do you have i.e. athletics, admin etc.

Also can you give me the row headers.

Tks

Keith 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJtVCtUGfzYhD2q8rQgzNU54E6aC%2BrAkVzLZk_Ao3wDB1c5CfA%40mail.gmail.com.

[Keith Andersen]

A mock sheet would be most helpful.

[Donald Tripdarlinq]

I see a few issues in your code that need to be addressed. Let's tackle them one by one.

1. Typo in Setting Table Border: You have a typo when setting the table border. You wrote able instead of table. Change able.setBorderWidth(0); to table.setBorderWidth(0);.

2. Checking for Unique Values: The logic for checking unique values in columns 1 and 2 is causing problems. You should use a separate loop to build the unique values arrays before adding rows to the table. Here's how you can do it:

3. Data Analysts, Donald genes from Nigeria

[Kerline Lorantin]

@Keith

Thank you, Keith. I really appreciate your help. I am not tied to the border style. It was more of an attempt to make it easier for the end user to see. So if there is data in the first cell and the fifth cell, but cells two, three and four are empty, I thought that the dotted line (since I cannot add leaders or dotted tab stops) would be helpful.

There are 144 different classifications in the first column. 

There are 158 different classifications in the second column.

I do not have any row headers.

Here is a copy of the Google sheet: https://docs.google.com/spreadsheets/d/1mos0Bbz_OMp1E32__3bUZ91U8Zrncb4vHJW7Wg1UqxI/edit#gid=0

Here is a copy of the Google doc: https://docs.google.com/document/d/17hjqvfKo4ghOX-UDdx9FHNieNjlO9K6krnL1iXjUrKg/edit

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4wuFC1aXRquyVG70bErybWL1uvoJ8Y3jvkJZo5cxDb2qw%40mail.gmail.com.

[Kerline Lorantin]

Thank you for your time, Donald. It seems that part of your response is missing. Was there something else that you were going to share under checking for unique values?

- Kerline

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/30d22645-6a8b-4e54-b236-394d99492b52n%40googlegroups.com.

[Keith Andersen]

Kerline,

I have begun a script to address these issues and will be working on it this afternoon. I hope to have something for you tonight.

Cheers,

Keith 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJtVCtXaxKCYNFcXY0yUPDG9zVjdsMcH1GccYqohVoMoYG76qA%40mail.gmail.com.

[Kerline Lorantin]

Thank you very much for your time and your efforts, Keith. I really appreciate it. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4zLE6yap55_LVKm%3DTqQTm_JmQxVJf6OJgjjUFgzPeOssw%40mail.gmail.com.

[Donald Tripdarlinq]

SURE, first off all, were you able to resolve it?

[Keith Andersen]

i believe ive accomplished your goals excel for the dotted bottom border. That one eludes me as yet.

Take a gander and let me know what needs to change.

https://docs.google.com/spreadsheets/d/1tC16rKCMduCqfiDAI4UriRDU6WQhj2cVI-iuwgDh3fU/edit?usp=sharing

Cheers

Keith

[kelo...@sewanee.edu]

@Keith Thank you very much! I haven't had an opportunity to look at your script yet. I plan to do it tomorrow. What do you mean by the following, " alt very least col 1 should be sorted ascending to work properly"? Also, is the thinking that I should convert to pdf from GoogleSheets and omit the GoogleDocs part?

@Donald still working on it

[Keith Andersen]

I just did a video and came up with a second more flexible formatting method.

Here is the video with a link to the new sheet in the description.

https://www.youtube.com/watch?v=_9CruyhaKu8

When you import the initial data, if column 1 is not sorted, you will not get a cascading effect of similar titles under each other.

No,  I wouldn't go right to pdf. to properly format totally with coding - you would need a ton more coding to format and make a pdf. It's possible, but the second method I exampled may be easier and give more artistic control.

Email or check back in here if you have questions. If you need to discuss over phone, private email me and I'll send my number.

Cheers

Keith

heres the sheet for convenience.

https://docs.google.com/spreadsheets/d/1L4ItQfTcFacqfcsnm3Ip1HQGGiLx-lYgYXxosY-Jjnk/edit?usp=sharing

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9a7bf604-35e4-4056-b2cc-c6883d8a66f3n%40googlegroups.com.

[kelo...@sewanee.edu]

Keith, this is just above and beyond. Thank you! I've learned a lot from this interaction. I will let you know if I have any follow-up questions. 

[Keith Andersen]

You're very welcome. My pleasure. Don't hesitate to reach out if you need something changed or clarified.

God bless

Keith 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4e606e58-bc4c-4b6c-8ccb-344a1e200c7bn%40googlegroups.com.