Best Of
Re: Combine Specific Characters from Multiple Cells
@Gillian C This is perfect! Exactly what I needed, thank you!
LindsayM
Re: Combine Specific Characters from Multiple Cells
Hi @LindsayM
You should be able to do
= LEFT([First Name]@row,1) + [Last Name]@row
If you want spaces or other delimiters between the two combined values then you can do the following
= LEFT([First Name]@row,1) + " " +[Last Name]@row (which will give a value of J Doe)
= LEFT([First Name]@row,1) + "-" + [Last Name]@row (which will give a value of J-Doe)
Hope that gives you a starting point.
Gillian C
Re: Share solution? Find and replace text in a Sheet (including inside formulas)
@NeilKY when you say within a formula do you mean inside the formula definition or within the results of the formula? It is possible to do this with the API but the approach is going to be a bit different depending on what you mean here. Let's assume you mean within the formula definition as that is more likely the case,
1. Get sheet (required via bridge, option via straight api)
2. Get all columns (can get direct from api, otherwise it is part of get sheet)
3. Iterate through all columns
4. for each column create a column update json object and put them into an array of json objects for update
5. process all column updates via the column update API, now all your formulas are done
you can use 1-5 to also rename anything in a column header or description if that is text you also want to change.
Now to do cells,
1. get sheet
2. get rows
3. interate through all rows
4. iterate through all columns
5. prepare JSON update object for each row (needs id, column id, and value)
6. process all row updates via the update row API
prime_nathaniel
Re: Premium Apps turned ON by default - can you default to OFF?
Yes, thing have changed since this original post!
It's my understanding that when each premium app is being set up for an organization, they decide whether to use an "opt-in" model or "opt-out" model for licensing. Once this has been configured, it can only be changed by contacting Support (e.g. if you used to have it so all new members were default toggled on but you want to now make the default toggled off).
Cheers,
Genevieve
Genevieve P.
Re: How can I append additional text to strings pulled via formula from other cells?
You can explore across the row with something like this..=IF([Input X]@row <> "", [Name X]@row + ", ", "") +
IF([Input Y]@row <> "", [Name Y]@row + ", ", "") +
IF([Input Z]@row <> "", [Name Z]@row + ", ", "")
It may result with a single trailing comma at the end, so to remedy that you could use LEN and LEFT=IF(
LEN(
IF([Input X]@row <> "", [Name X]@row + ", ", "") +
IF([Input Y]@row <> "", [Name Y]@row + ", ", "") +
IF([Input Z]@row <> "", [Name Z]@row + ", ", "")
) > 2,
LEFT(
IF([Input X]@row <> "", [Name X]@row + ", ", "") +
IF([Input Y]@row <> "", [Name Y]@row + ", ", "") +
IF([Input Z]@row <> "", [Name Z]@row + ", ", ""),
LEN(
IF([Input X]@row <> "", [Name X]@row + ", ", "") +
IF([Input Y]@row <> "", [Name Y]@row + ", ", "") +
IF([Input Z]@row <> "", [Name Z]@row + ", ", "")
) - 2
),
""
)
=Chris Palmer
Re: API adding blank row instead of data sent in the code
*deleted
ty for clarification @Marcela!
Tim Buck II
Re: How can I append additional text to strings pulled via formula from other cells?
Thanks, Chris! I did find a solution to my original problem which was just to add a + between my cell reference and additional text.
Phil Yorkville
Re: How can I append additional text to strings pulled via formula from other cells?
Hello,
Something like this perhaps?=JOIN(COLLECT([Name Column]:[Name Column], [Input Column]:[Input Column], <> ""), ", ")
=Chris Palmer
Re: Populating a drop down from data in another sheet
You can do this with the Update Column endpoint, if you first read the list of IDs from the first sheet, you can post those into the dropdown column on the second sheet as "options".
https://smartsheet.redoc.ly/tag/columns#operation/column-updateColumn
// Specify column properties
Column columnSpecification = new Column
{
Id = 5005385858869124,
Title = "First Column",
Index = 0,
Type = ColumnType.PICKLIST,
Options = new string[] { "One", "Two"}
};
// Update column
Column updatedColumn = smartsheet.SheetResources.ColumnResources.UpdateColumn(
2252168947361668, // sheetId
columnSpecification
Brian_Richardson